GWARANTOWANE TERMINY
Jesteś tutaj: strona głównastrefa wiedzyDlaczego Power BI Excel (Power Query, PowerPivot, PowerView i PowerMap)?
MENU

Dlaczego Power BI Excel (Power Query, PowerPivot, PowerView i PowerMap)?


Kategoria: Analiza danych, Prezentacja danych
11Aug

Z tego artykułu dowiesz się:

  • Co to jest Excel Bi geneza powstania - zalety 
  • Power Query - zastosowanie do wczytywania danych z pliku TXT
  • Porównanie zastosowania VBA I Power Query
  • Co to jest Power Pivot - zastosowanie i przykład zastosowania
  • Co to jest Power Map- przykład zastosowania w analizie danych powierzchniowych

Co to jest Power BI Excel (Power Query, PowerPivot, PowerView i PowerMap)?

Excel, który był rewolucyjnym pomysłem i rozwiązaniem do wykonywania obliczeń, powoli zaczyna odsuwać się w cień ustępując miejsca innym rozwiązaniom szczególnie w analizach biznesowych. Chciałbym w tym miejscu uspokoić wszystkich zaniepokojonych czy warto w takim razie uczyć się Excela. Powiem, że warto bo jeszcze przez lata będzie to podstawowe narzędzie do dokonywania kalkulacji a niniejszy artykuł dotyczy zakresu tematycznego związanego z analizami biznesowymi. Chciałby jednak pokazać, że w przypadku bardzo dużej ilości danych oraz potrzebie dokonywania na nich wielu obliczeń Excel przestaje sobie z nimi radzić. Obliczenia są bardzo czasochłonne a przy każdej aktualizacji danych wymagane jest powtórzenie dużej ilości skomplikowanych korków co zwiększa szansę na popełnianie błędu. 

Aby rozwiązać powyższe niedoskonałości pewną nadzieję dawał język programowania VBA, który pozwalał automatyzować pracę i dokonywać obliczenia, ale pomimo olbrzymich możliwości był dla użytkowników za trudny. Szczególnie dla tych którzy nie posiadali wykształcenia w jakimś stopniu związanego z informatyką a analizy biznesowe w pracy są zagadnieniem pobocznym.

Innym problemem, który się pojawił były same dane. Excel za pomocą tabeli przestawnej bardzo dobrze sobie radził z analizą danych ale zapisanych tylko w jednej tabeli. Gdy jednak mamy informacje przechowywane w wielu tabelach analiza w Excelu bywa w wielu przypadkach niemożliwa. Przy niewielkich projektach, nawet gdyby połączyć wszystkie informacje z wielu tabel i umieścić je w jednej dużej tabeli ich ilość powodowałaby bardzo powolną pracą komputera. Problematyczne i obarczone dużym ryzykiem zrobienia błędu byłoby również wprowadzanie nowych danych i ich aktualizacja.

Sporym problemem jest również ograniczenie ilości wierszu w arkuszu Excela. Wynosi on niewiele ponad 1 milion wierszy i coraz częściej ta granica jest barierą dla zbieranych danych. Przy narastającej ewidencji sprzedaży, produkcji lub pomiarów osiągniecie granicy miliona wierszy jest tylko kwestią czasu. Od kilku lat coraz częściej można spotkać użytkowników, dla których arkusz Excela jest „za mały” a w tracie prowadzonych szkoleń widziałem już realną potrzeb na zarządzanie 9,5 milionami danymi, które z racji ograniczeń Excela były przechowywane w kilkunastu arkuszach!

Rozwiązaniem byłoby zbudowanie projektu w bazie danych np. z Access-ie. Niestety praca z bazami danych wymaga przede wszystkim nauki nowego programu, zbudowania projektu a przy bardziej zaawansowanych analizach poznanie SQL-a. Wielu użytkowników zajmujących się „przy okazji” analityką nie zdecydowało się przez zmianę środowiska i mimo niedogodności zarządzali danymi dalej w Excelu.

Trzecim wyzwaniem jest wizualizacja, która pozwalałaby pokazać dane z kilku perspektyw tak, aby w czytelny sposób móc zapoznać się z danymi, zrozumieć trendy i wyniki a w konsekwencji podjąć właściwą decyzję biznesową. Wykresy przestawne połączone z fragmentatorami i osiami czasu są ciekawym i funkcjonalnym rozwiązaniem, ale wymagają sporego nakładu pracy i docelowo nie sprostały wyzwaniom przy coraz większej potrzebie wizualizacji coraz większe ilości danych.

I te trzy zagadnienia: automatyzacja w VBA, obsługa baz danych wraz z SQL i na koniec wizualizacja wchodziły w zakres analityki biznesowej zwanej BI i były z racji skomplikowania dostępne dla niewielkiej grupy specjalistów.

Przełomowym okazał się rok 2013, gdy pojawiły się rozwiązania pozwalające osobom nie będącymi programistami, bazodanowcami i grafikami dokonywać analiz BI w Excelu przy wykorzystaniu dodatkowych narzędzi zawartych w samym programie. Sama wersja Excela 2013 była dość nietypowa z punktu widzenia korzystania z BI ponieważ najpierw został opublikowany sam Excel a dopiero później zostały opublikowane rozszerzenia odpowiedzialne za analizy biznesowe. W późniejszych wersjach Excela elementy BI są już częściami składowymi programu.

Do automatyzacji czynności został napisany program Power Query, jako alternatywę dla analiz bazodanowych został opracowany Power Pivot a do wizualizacji powstały 2 programy PowerView i PowerMap. I te trzy filary:

 

  • Pobieranie i poprawa danych ( Power Query)
  • Analiza danych w tabelach (PowerPivot)
  • Wizualizacja (PowerView i PowerMap

 

są fundamentem analiz biznesowych BI wykonywanych w Excelu.

Rozwiązania analityki biznesowej BI w środowisku Excela są bardzo ciekawą i relatywnie prostą propozycją dla użytkowników, którzy nie posiadają wiedzy pozwalającej na programowanie w VBA, nie mają żadnego doświadczenia w pracy z bazami danych a z grafiką mieli do czynienia tylko na poziomie pojedynczych wykresów.

Każde zaprezentowane we wcześniejszych artykułach rozwiązanie może być używany niezależnie od innego choć najlepsze rezultaty uzyskuje się tworząc projekty, które wykorzystują wszystkie moduły dostępne w BI. 

W chwili obecnej głównym kierunkiem rozwoju jest praca w zintegrowanym środowisku BI zwanym PowerBI. Są tam wszystkie wymienione powyżej moduły, choć największa zmiana zaszła w części odpowiedzialnej za wizualizacje.

Power Query - co to jest 

Pierwszym filarem w analityce biznesowej BI jest PowerQuery.  Jest to rozszerzenie (podaplikacja) działająca z poziomu Excela lub PowerBI  wykorzystywana głównie do:

  • pobierania 
  • przekształcania danych

Jeżeli dane nie zawierają literówek i innych błędów wymagających poprawy pojedynczych wartości w komórkach Power Query z powodzeniem w pełni zastępuje Excela, ponieważ ma w sobie dostępne narzędzia do obróbki tekstów, czasu,  do wykonywania obliczeń, do zarządzania funkcjami logicznymi (odpowiednik funkcji Jeżeli), dzielenia tekstu na kolumny, łączenia tabel (odpowiednik Wyszukaj.Pionowo) itp. Ta cala funkcjonalność nie wymaga od użytkownika znajomości funkcji i umiejętności ich wpisywania ponieważ interface Power Query pozwala na wykonywanie powyższych czynności poprzez wybór ikon i korzystania z pól dialogowych. Dla trudniejszych zadań użytkownik ma do dyspozycji język programowania M. Wynik wczytanych i poprawionych danych w Power Query może być zapisany w arkuszu Excela lub wpisany do modelu danych w celu analiz w Power Pivot lub wizualizacji.

Zobacz film

 

Pobieranie danych z pliku TXT do analizy za pomocą VBA I Power Query

W ramach przykładu chciałbym zautomatyzować pobieranie danych z pliku TXT i porównać rozwiązania dostępne w VBA i Power Query. Nie będę porównywał możliwości otwierania pliku TXT w samym Excelu ponieważ wymagałoby to od użytkownika za każdym razem przejście przez  kreatora importu tekstu i z automatyzacją niewiele miałoby to wspólnego.

W przypadku VBA użytkownik musi napisać program jak np. zamieszczony poniżej. Wcześniej jednak powinien sprawdzić jaki symbol odpowiada za podział kolumn i umieścić go w kodzie. W poniższym przykładzie jest to spacja. Wczytanie i aktualizacja danych z pliku TXT polega na wielokrotnym uruchomieniu programu.

Sub import_TXT()

Dim plik As String, i As Integer, ile As Integer, wiersz As Integer, dane As String

Set ark = ThisWorkbook.Worksheets(1)

plik = "C:wynikiDane.txt"

Open plik For Input As #1

wiersz = 1

 

Do Until EOF(1)

Line Input #1, dane

wyraz = Split(dane, " ")

ile = UBound(wyraz)

For i = 0 To ile

ark.Cells(wiersz, i + 1).Value = wyraz(i)

Next i

Wie

rsz = wiersz + 1

Loop

 

Close #1

End Sub

 

W przypadku PowerQuery użytkownik musi wskazać plik przeznaczony do wczytania. Power Query samodzielnie rozpoznaje separatory kolumn i stronę kodową pliku i prezentuje podgląd danych. Jeżeli podgląd podziału na kolumny jest prawidłowy od użytkownika wymagane jest tylko wybranie polecenia ZAŁADUJ

Aktualizacja danych zawartych w pliku TXT polega na wybraniu polecenia Odśwież wszystko wyróżnionego czerwonym konturem na rysunku poniżej. W przeciwieństwie do zamieszczonego powyżej kodu VBA (który odpowiada tylko za wczytanie pliku TXT), załadowane dane poprzez Power Query są domyślnie umieszczane w nowym arkuszu i sformatowane (nadane są kolory, krawędzie, dopasowane do treści są szerokości kolumn). Aby uzyskać ten sam efekt w VBA należałoby dodatkowo dopisać linijki kodu, które będę realizować wymienione powyżej czynności.

Przekształcanie danych w Power Query

Wczytane dane rzadko kiedy nadają się od razu do dalszej analizy. Często separatorem części całkowitych i dziesiętnych jest kropka zamiast przecinka, w jednej kolumnie znajduje się zbyt dużo danych i trzeba je rozdzielić na kilka kolumn, lub odwrotnie z kilku kolumn stworzyć jedną. W ramach przykładu chciałbym pokazać jak w Power Query połączyć w jedna kolumnę imię i nazwisko występujące w 2 kolumnach.

W tym celu trzeba zaznaczyć, kolumny w takiej kolejności w jakiej ma nastąpić połączenie i z pod prawego klawisza wybrać polecenie Scal kolumny. W  pojawiającym się oknie należy wybrać jaki znak ma rozdzielać informacje z dwóch kolumn i nadać nazwę opisującą wynik scalenia. Po zatwierdzeniu, kolumny z imieniem i nazwiskiem zostają zastąpione wynikiem ich połączenia. 


Przeczytaj również: Power BI wizualizacja danych na wykresie.

Wszystkie wykonywane czynności w Power Query automatycznie są zapisywane w postaci listy jako wykaz zastosowanych kroków, dlatego użytkownik w każdym momencie może, podejrzeć dane z dowolnego etapu obliczeń i poprawek, wycofać się w przypadku popełnienia błędu oraz zastosować „nauczone” czynności dla nowych plików i połączeń. Power Query posiada również mechanizmy i gotowe rozwiązania, których jest brak w Excelu jak np. porządkujące układ danych w kolumnach umożliwiający wykonanie analizy w tabeli przestawnej.

Power Pivot - Co to jest

Power Pivot a dokładnie praca w oparciu o model danych jest drugim filarem w BI. Rozwiązanie to jest przełożeniem na poziom Excela rozwiązań dotychczas dostępnych w bazach danych, ponieważ podstawą jest praca na relacyjnie połączonych ze sobą tabelach i wykonywanie na tego typu danych analiz. Język Dax umożliwia z danych porozrzucanych po tabelach uzyskać informację i wykonywać analizy a sam Power Pivot odpowiada za przedstawianie danych w interface tabeli przestawnej. Ponieważ staram się porównać przełomowość i relatywną prostotę rozwiązań BI w kontekście dotychczasowych metod analizy danych, porównam rozwiązania w podstawowym narzędziu do analiz w Excelu czyli Tabeli przestawnej i w PowerPivot.

 

Zobacz przykład na filmie 

 

Przykład zastosowania Power Pivot

W przedstawionym przykładzie posiadamy 2 tabele z wykazem sklepów położonych w różnych miejscowościach i osiągniętych w poszczególnych sklepach wynikach sprzedażowych. 

Tabela 1 Wykaz sklepów

Tabela 2 Wyniki sprzedażowe – fragment danych z ponad 6000 wierszy

Chciałbym wykonać analizę pokazującą wyniki sprzedaży przypisane do poszczególnych dyrektorów. 

Ponieważ tabela przestawna nie potrafi raportować danych z wielu źródeł najpierw trzeba połączyć dane i zapisać je w jednej tabeli. Ponieważ docelowo analiza może dotyczyć nie tylko nazwisk dyrektorów ale i powiązanych kosztów, zatrudnienia itp. należałoby połączyć wszystkie kolumny z dwóch tabel. Efekt połącznia przedstawiony jest poniżej. Ponieważ Excel do analiz wymaga aby każdy wiersz przechowywał komplet informacji, do każdego wiersza z wynikami sprzedaży należałoby przepisać (i przy okazji powielić) tą samą informację o sklepie.

 

Ilustracja powyżej pokazuje tylko fragment danych, ale nie trudno zauważyć, że wykaz tylko 12 sklepów trzeba przekopiować na ponad 6000 razy, bo tyle zawiera tabela z wynikami. Takie rozwiązanie mocno obciąża komputer, utrudnia wprowadzanie nowych danych a zaprezentowany przykład jest bardzo prosty. 

Gdyby do analizy próbować dołączyć trzecią tabelę (aby np. móc analizować wyniki konkretnych sprzedawców w konkretnych sklepach), to zbudowanie takiej tabeli do analiz byłoby już bardzo trudne i czasochłonne. Przy większej ilości tabel (np. z informacją o producencie, konkretnym towarze) dokonywanie analiz w Excelu przestałoby być możliwe. Zbudowana w oparciu o klasycznie przygotowane dane tabela przestawna i raport sprzedaży jest przedstawiony obok.

Power Pivot korzysta z rozwiązań stosowanych w bazach danych. Korzystając ze wspólnej informacji w obydwu tabelach (pole filia), łączy je ze sobą relacjami. Relacje pomiędzy tabelami tworzy się metodą przeciągnij i upuść. 

Linia łącząca obydwie tabele ma na swoich krańcach znak 1 oraz * co oznacza, że tabele połączone są relacją jeden do wielu. Czyli informacja o jednym sklepie może być wielokrotnie łączona z informacją o sprzedaży a wiele dokonanych sprzedaży można być przyporządkowane do jednego sklepu.  

W przeciwieństwie do wcześniejszego rozwiązania informacja o każdym ze sklepów przechowywana jest tylko raz w tabeli Sklepy, ale poprzez relację można wielokrotnie pobrać ją od strony tabeli Sprzedaż do wykonania analizy.  

 

Power Pivot do analiz korzysta z relacyjnie połączonych tabel, a użytkownik tak jak w zwykłej tabeli przestawnej ma za zadanie wybrać pola (kolumny), które mają być użyte w analizie. Osoby pracujące dotychczas z tabelami przestawnymi bez problemu zaczną pracować w Power Pivot ponieważ interface jest identyczny, choć dokonywanie obliczeń i analiz nie odbywa się od strony Excela ale w dodatkowym oknie aplikacyjnym. Sam interface z tabeli przestawnej służy tylko do prezentacji wyników.

Power Map - Co to jest, przykład i zastosowanie

Możliwości graficznej prezentacji danych są w ramach rozwiązań BI realizowane za pomocą dwóch programów. Oprócz Power View jest jeszcze rozszerzenie Power Map, które koncentruje się na prezentacji danych na mapie. Oznacza to, że dane użyte do analizy muszą mieć informację o lokalizacji. Jednakże użytkownicy nie muszą być specjalistami potrafiącymi poruszać się w geodezyjnych układach współrzędnych, bo wystarczy tylko informacja o państwie, mieście, województwie, powiecie, kodzie pocztowym itp. a Power Map samodzielnie identyfikuje miejsce i wyświetla je na mapie wraz z dodatkowymi informacjami np. wynikami sprzedaży. Korzystanie z dodatku wymaga połączenia z internetem ponieważ mapa jest dynamicznie pobierana z usługi Bing Maps. 

Power Map jest dodatkiem, choć w przeciwieństwie do Power View od razu jest dostępny w Excelu na karcie Wstawianie. Ponieważ jest częścią BI dane wykorzystywane do prezentacji są przechowywane w modelu danych czyli inaczej mówiąc wyniki obliczeń w Power Pivocie mogą być prezentowane za pomocą tego dodatku.

Użytkownicy pracujący w Power View lub w samym Excelu (wykres typu Kartogram) również mogą tworzyć raporty w oparciu o położenie danych w terenie, jednakże dostępne tam mechanizmy są dość mocno uproszczone i mało funkcjonalne. Mechanizmy dostępne w Power Map posiadają natomiast dwa niespotykane w pakiecie office rozwiązania a mianowicie: 

  • Warstwy – czyli niezależne płaszczyzny projektowe w ramach jednego projektu. Na warstwach można umieszczać różne informacje i poprzez włączanie i wyłączanie ich, budować raporty z dowolną treścią. Warstwy dobrze znane są użytkownikom CAD-a (AutoCad, MicroStation), grafiki (Photoshop, Ilustrator, CorelDraw) itp.
  • Wykresy trójwymiarowe. Grafika w office jest dwuwymiarowa a dostępne możliwości np. przy wykresach 3D są w rzeczywistości odpowiednim sposobem patrzenia na dwuwymiarowe dane. W Power Map wykresy są w pełni trójwymiarowe.

 

Zobacz film zastosowanie Power Map w praktyce 

 

Jeszcze jednym ciekawym rozwiązaniem są możliwości związane z animacją. W pakiecie office do tego typu zadań przeznaczony jest PowerPoint. Animacje dostępne są również w dodatku Power Map. Ponad danymi wyświetlanymi na mapie mogę się przemieszczać, zbliżać, obracać, a słupki z danymi mogą mi „rosnąć” pokazując np. przebieg w czasie. Animacje mogą być wyświetlane na bieżąco w dodatku ale można również tworzyć z nich filmy, podkładając do nich ścieżkę dźwiękową.

W ramach porównania możliwości Excela i dodatku Power Map zaprezentuje przedstawienie na mapie danych z poniższej tabelki. Tabelka zawiera podstawowe informacje pobranie z Wikipedii o województwach w Polsce. 

Województwo

Miasto wojewódzkie

Powierzchnia

Ludność

dolnośląskie

Wrocław

19 947

2 901 003

kujawsko-pomorskie

Bydgoszcz

17 971

2 079 917

lubelskie

Lublin

25 122

2 121 613

lubuskie

Gorzów Wielkopolski

13 988

1 015 440

łódzkie

Łódź

18 219

2 470 610

małopolskie

Kraków

15 183

3 395 663

mazowieckie

Warszawa

35 558

5 391 813

opolskie

Opole

9 412

988 014

podkarpackie

Rzeszów

17 846

2 128 747

podlaskie

Białystok

20 187

1 182 677

pomorskie

Gdańsk

18 310

2 328 214

śląskie

Katowice

12 333

4 540 140

świętokrzyskie

Kielce

11 711

1 244 383

warmińsko-mazurskie

Olsztyn

24 173

1 431 299

wielkopolskie

Poznań

29 827

3 490 597

zachodniopomorskie

Szczecin

22 892

1 703 009

Przy generowaniu w Excelu wykresu typu kartogram, potrzebujemy posiadać dane powierzchniowe ( państwo, województwo lub powiat) i tak jak przy wykresach mamy kilka gotowych propozycji wyświetlania danych, z których jedno prezentuje poniżej. 

Wykres typu kartogram w oparciu o nazwę identyfikuje i wyświetla kształt (w powyższym przykładzie województw) i poza etykietami (powierzchnią) nie pozwala na dołączenie do wykresów kolejnych informacji. Wykres typu kartogram nie pozwala również na lokalizację obiektów punktowych np. miast. W przeciwieństwie do Power Map nie identyfikuje gmin, kodów pocztowych itp.

 

Zupełnie inaczej przedstawiają się możliwości Power Map. Uruchomienie dodatku z danymi z tabelki zamieszczonej wcześniej, automatycznie identyfikuje lokalizacje występujących tam miast i województw a od użytkownika należy określenie, które informacje powiązane z wybraną lokalizacją chciałby pokazać na mapie.

W ramach przykładu wyświetlone zostały dwie grupy danych, które zostały umieszczone na dwóch warstwach.

  • Do informacji o województwach została dołączona informacja o ich powierzani i zaprezentowana jako wykres powierzchniowy w odcieniach koloru zielonego
  • Do informacji o położeniu miast zostały dołączone informacje o liczbie ludności w poszczególnych województwach. Dane zostały przedstawione na wykresie kolumnowym w kolorze niebieskiemu.

Szczegółowe informacje o poszczególnych elementach wykresów wyświetlają się w dymku po najechaniu na nie wskaźnikiem myszki. Na rysunku poniżej wyświetlone zostały dane dla wyników (słupka) opisującego Rzeszów.  Domyślnym obszarem pracy jest kula ziemska, ale po lokalizacji nazw, obszar wyświetlania pokazuje ten obszar globu dla którego posiadamy dane. Pracując z kulą ziemską użytkownik ma możliwość zbliżania i oddalenia się od jej powierzchni, obrotu jak i zmiany kąta patrzenia. Przykładowy sposób prezentacji  wyżej wymienionych danych przedstawiony jest poniżej. Ilość treści (szczegółów) na mapie jak i wielkość wykresów dynamicznie dostosowują się do odległości z jaką patrzymy na powierzchnie co ułatwia prezentacje danych.

Ciekawą funkcja programu jest możliwość zdefiniowania animacji np. obrotu ziemi i dodatkowo stworzenia z tego filmu. W ramach powyższego przykładu chciałbym „przelecieć” ponad powierzchnią Polski zaczynając od obszaru Francji a kończąc przy granicy z Rosją. Generowany film został wzbogacony o ścieżkę dźwiękową i zapisany w jakości full HD.

Power Map jest bardzo funkcjonalnym dodatkiem, choć sprawdzającym się tylko dla danych w których analizy są powiązane z lokalizacją na mapie. Tak jak pozostałe aplikacje z grupy BI, jego wizualizacje są przechowywane w pliku Excela.

Autorem artykułu jest Tomasz Bagiński, trener Cognity.

Zachęcamy do zapoznania się ze szkoleniami oferowanymi przez Cognity z zakresu Power BI:

Więcej artykułów na temat Power BI:

Wybrani Klienci:

Ten serwis używa plików cookies. Brak zmiany ustawień przeglądarki oznacza zgodę na ich używanie.
Więcej informacji o plikach cookies znajdziesz tutaj
Kliknij ZGADZAM SIĘ, aby ta informacja nie wyświetlała się ponownie
Cognity.pl