Business Intelligence – Power Query
– wiedza w pigułce od JCommerce

Poradniki | 28.03.2014 | Czas czytania: 9 minut

Microsoft Power Query. Nieduży, ale pełny możliwości dodatek do pracy z danymi w Excelu. Pobierz dane z Facebooka, SharePointa czy Wikipedii, zintegruj i przygotuj do wizualizacji w ciągu kilku minut za pomocą paru kliknięć. Bez programowania i kombinowania – zobacz jak działa Microsoft Power Query.

Microsoft Power Query (PQ) to dodatek do arkusza kalkulacyjnego Microsoft Excel, który został zaprojektowany przez firmę Microsoft do wspierania rozwiązań klasy Self-Service Business Intelligence. Równie przydatny może okazać się również do codziennej pracy z danymi, do ich gromadzenia oraz czerpania z nich wiedzy. Pozwala pobierać dane z wielu różnych miejsc; począwszy od relacyjnych baz danych, poprzez dane pochodzące z SharePointa i systemu operacyjnego aż po dowolne dane z dowolnej strony internetowej. Dodatkowo umożliwia wstępną obróbkę danych oraz przygotowanie ich do dalszej analizy lub wizualizacji. Wszystko to można zrobić za pomocą specjalnego języka „M”, lub za pomocą kilku ruchów myszy.

W poniższym poście zostanie omówiony właśnie Microsoft Power Query. Zaprezentowane zostaną kluczowe funkcjonalności, takie jak pobieranie danych z dowolnej strony czy przygotowywanie oraz czyszczenie danych. Przedstawiony będzie proces pracy z danymi: od ich gromadzenia, aż po ich wizualizację. Do prezentacji tych możliwości zostaną wykorzystane dane o medalach olimpijskich pochodzące z Wikipedii, ale nie tylko.

Przed rozpoczęciem pracy należy oczywiście pobrać oraz zainstalować ww. dodatek. Należy pamiętać, że dodatek ten współpracuje wyłącznie z Microsoft Office 2013 Professional Plus, Office 365 ProPlus i Excel 2013 Standalone.

Po instalacji dodatek jest gotowy do użycia z poziomu nowej zakładki w menu Excela. Co prawda celem tego postu nie jest omówienie wszystkich możliwości oraz zakładek, ale na pewno warto wspomnieć o jednej, szczególnej. Mowa tutaj o funkcji „Online search”, która pozwala wyszukiwać dane za pomocą języka naturalnego – zupełnie tak, jak w popularnych wyszukiwarkach. Zdecydowanie warto przetestować!

Excel zakladka

Dodatek Power Query w zakładce Microsoft Excel

Dzisiaj natomiast zostanie zaprezentowana druga z opcji dodatku, czyli pobieranie danych ze stron internetowych: zakładka „From Web”. Interesujące dane znajdują się na Wikipedii, gdy wpiszemy „All-time Olympic Games medal table”. Po wejściu na stronę można zauważyć, że zostały one jednak podzielone na dwie różne tabele: pierwsza zawiera listę państw, które zdobyły już medale na Igrzyskach, a druga zawiera państwa, które brały udział, ale nie zdobyły jeszcze żadnego medalu. Pozostałe tabele zostaną celowo pominięte w tej prezentacji. Aby pobrać te dane wystarczy kliknąć na ikonę, wkleić adres internetowy strony oraz zatwierdzić wybór.

from web jcommerce


Excel dokona analizy struktury strony i wyszuka wszystkie dostępne, ustrukturalizowane porcje danych, które następnie zaprezentuje w formie listy wyników wyszukiwania. Jak widać na poniższym rysunku, system znalazł między innymi wcześniej wymienione tabele.

Navigator JCommerce

Aby pobrać te dane wystarczy kliknąć przycisk „Load” na każdym źródle i po chwili dane zostaną załadowane do arkusza.

zrodlo danych 1 JCommerce

zrodlo danych 2 JCommerce

To, co jest interesujące i świadczy o potencjale tego dodatku to fakt, że Excel pamięta wszystkie połączenia do źródeł. Oprócz połączeń rejestrowane są również wszystkie operacje oraz zmiany w strukturze oraz samych danych, tworzących tak naprawdę proces ETL, który może być wielokrotnie odtwarzany. Można również dodać, że wszystkie operacje użytkownika są tłumaczone do języka pośredniego „M” i mogą być również modyfikowane za pomocą zmiany w „kodzie”. Jak już wspomniano, zgromadzone dane można poddać obróbce. Aby rozpocząć proces czyszczenia oraz przygotowywania danych należy kliknąć „Edit” na wybranym połączeniu do źródła danych.

work queries widok JCommerce

Zostanie wówczas otwarte nowe menu, które zawiera listę wszystkich możliwych transformacji.

menu transformacji JCommerce

Jednym z największych problemów w tej tabeli jest brak atomowości w poszczególnych atrybutach. Pierwsza kolumna: „Team (ISO code)” zawiera tak naprawdę dwie informacje: nazwę państwa oraz „IOC code”. Pierwszą transformacją będzie właśnie podział tych informacji na dwa, oddzielne atrybuty. W tym celu wystarczy wybrać opcję „Split column”. Warto dodać, że najpopularniejsze transformacje oraz opcje dostępne są również pod prawym przyciskiem myszy. Jak wspomniano należy wybrać „Split column”, a następnie „By Delimeter…”.

balanced scorecard

Teraz należy określić warunki podziału. W tym przykładzie należy podzielić kolumnę względem pierwszego lewostronnego nawiasu znajdującego się po lewej stornie, aby to zrobić należy wybrać „Custom” i wskazać na „(” oraz wybrać opcję „At the left-most delimeter”).

split a column JCommerce

Dane zostały podzielone na dwie oddzielne kolumny. Druga z nich zawiera nadal brudne dane i trzeba z niej usunąć niepotrzebny nawias. W tym celu należy powtórzyć wcześniejszą operację zmieniając znak nawiasu oraz wybierając opcję: „At the most right delimeter”.

Query editor kokpit JCommerce


Aby upewnić się, że kolumna IOC code nie zawiera dodatkowo niepotrzebnych spacji można użyć polecania „Trim” znajdującego się w zakładce „Transform”. Po wykonaniu tych czynności w tabeli została kolumna, która po podziale nie zawiera żadnej informacji i jest niepotrzebna. Do usunięcia atrybutu z tabeli służy opcja „Remove”.

polecenia trimm JCommerce

Aby zakończyć przygotowywanie danych w pierwszej tabeli należy jeszcze odpowiednio nazwać kolejne atrybuty oraz ustawić odpowiednie typy danych. Można to zrobić tak jak zaprezentowano na obrazku poniżej.

rename jquery

Pierwsza tabela jest gotowa. Każda kolumna zawiera unikalne informacje, zawiera czyste dane oraz posiada właściwy typ i jednoznaczną nazwę. Aby zapisać zmiany i przetworzyć dane należy wybrać “Apply & Close”.

apply&close jquery JCommerce

W tym momencie skrypt, który powstał z zapisu poszczególnych kroków i przetłumaczeniu ich na język „M” jest wykonywany, a dane w nowej formie trafiają do arkusza.

tabela JCommerce

Dane potrzebne do analizy znajdują się jednak w dwóch różnych tabelach. Aby połączyć dane (rekordy) z dwóch tabel, tabele te powinny mieć taką samą strukturę ( w przypadku różnic nieznalezione kolumny zostaną wypełnione NULLami). Przed połączeniem należy zatem w przypadku drugiej tabeli wykonać analogiczne kroki co w pierwszej.

tabela JQuery JCommerce

Teraz, w dwóch różnych arkuszach znajdują się dwie tabele o podobnej strukturze, ale zawierające różne informacje. W celu połączenia ich ze sobą należy wybrać „Append” z menu Power Query. W oknie konfiguracyjnym należy określić, które tabele mają zostać połączone i zatwierdzić przyciskiem „OK”. Excel automatycznie wykryje podobieństwo i złączy dane. Jak już wspomniano w przypadku braku kolumny w dołączanej tabeli Excel wypełni ta kolumny NULLami. Tak też stało się w tym przypadku. Druga tabela prezentowała dane o państwach, które nie zdobyły jeszcze żadnego medalu i nie zawierała z oczywistych względów atrybutów opisujących ilość zdobytych medali. W tym demo wartości NULL można jednak bez problemu zamienić na 0, ponieważ NULLe faktycznie oznaczają, że dane państwo nie zdobyło medalu.

balanced scorecard

Wartości można z łatwością zastąpić innymi wybierając opcję “Replace Values…” oraz wskazując odpowiednie warunki zamiany. Tutaj wartość „null” będzie zamieniona na 0. Po sprawdzeniu typów danych nową tabelę można nazwać i zapisać zmiany.

JQuery settings JCommerce

Poprzednia operacja złączenia łączyła rekordy (odpowiednik union w języku sql). Inną bardzo ważną czynnością podczas pracy z danymi oraz ich przygotowywaniem jest operacja łączenia tabel, dołączająca do jednej tabeli informacje z drugiej według określonego klucza (odpowiedniej join w języku sql). Oczywiście Power Query umożliwia takie złączenia, co zostanie zaprezentowane poniżej. Na samym początku zostanie pobrana nowa tabela z zupełnie innej strony – GEONAMES. Strona ta zawiera listę państw z informacjami na temat ich powierzchni oraz ludności. Przed łączeniem należy oczywiście pobrać i przygotować dane: analogicznie jak w przypadku wcześniejszych tabel. Kiedy dane w drugiej tabeli są gotowe wystarczy kliknąć ikonę „Merge”, wybrać tabele, które mają zostań złączone oraz wybrać atrybuty, które będą pełniły rolę klucza. Po dokonaniu wyboru Excel poinformuje ile rekordów zostało odnalezionych. Power Query pozwala również wybrać metodę złączenia. Można pobrać wyłącznie pasujące rekordy (odpowiednik inner join w sql) lub wszystkie (odpowiednij left join w sql). W przypadku drugiej opcji kiedy Excel nie znajdzie odpowiednika dla danego klucza wartości wypełni NULLami – zupełnie tak jak w przypadku SQLa. W przypadku tej prezentacji zostaną zwrócone wszystkie rekordy, a kluczem będą nazwy państw.

kokpit Query JCommerce

Po złączeniu Power Query wyświetli edytor nowoutworzonej tabeli, która oprócz atrybutów z pierwszej (głównej) tabeli będzie zawierała jeden dodatkowy o typie „table”. Aby dokończyć proces złączenia należy kliknąć na małą ikonę obok nazwy kolumny oraz wybrać atrybuty z drugiej kolumny, które powinny zostać dołączone do tych z pierwszej tabeli.

new column JQuery JCommerce

Aby zakończyć proces przygotowania danych należy jeszcze raz upewnić się i sprawdzić czystość danych oraz dokonać ostatnich poprawek. Wykonano następujące czynności:

– zduplikowano kolumnę („Duplicate column”) ze skrótami kontynentów oraz w jej kopii skróty zamieniono na pełne nazwy kontynentów

– Zamieniono brakujące wartości na „N/A” oraz 0

– W kolumnach określających wielkość kraju oraz populację zamieniono najpierw „,” na pusty ciąg znaków, a następnie „.” na „,”, aby zmienić typ danych tych kolumn na numeryczne

– Ustawiono odpowiednie typy danych

– ustawiono odpowiednie nazwy dla atrybutów

To wszystko! Z pomocą Power Query i kilku prostych operacji przygotowany został proces pobierania oraz integracji danych z dwóch zupełnie różnych stron internetowych bez linijki kodu i programowania. Plusem PQ jest fakt, że tak przygotowane dane z pomocą PQ są automatycznie dodawane do modelu danych i z łatwością mogą być wykorzystywane w Power View, ale również prezentowane oraz wizualizowane w arkuszu. Tak, jak tutaj:

prezentacja przykladowa JCommerce

Plusem jest także możliwość powtórnego wykorzystania przygotowanego procesu, do czego wystarczy tylko odświeżyć połączenie w Excelu. Daje to możliwość posiadania zawsze aktualnych informacji z wielu różnych miejsc.

Power Query to bardzo ciekawe i potężne narzędzie, które sprawia, ze praca z danymi jest jeszcze prostsza i przyjemniejsza. Bez specjalistycznej wiedzy i umiejętności programistycznych pozwala na pobieranie, integrowanie oraz przygotowywanie różnych danych z wielu źródeł, które następnie mogą być w różny sposób analizowane czy wizualizowane. Mimo, że PQ póki co na pewno nie jest w stanie zastąpić pełnowymiarowego projektu ETL, z całą pewnością dostarcza wielu nowych możliwości oraz jest przydatny w rozwiązaniach typu self-service Business Intelligence. Może także sprawić wiele frajdy analitykom oraz pasjonatom.

Spodobał Ci się artykuł? Masz już jakieś pomysły jak wykorzystać Power Query w projekcie czy też do zabawy z danymi? Podziel się ze swoimi przemyśleniami w komentarzach i bądź gotowy na więcej ciekawostek, postów i artykułów o Business Intelligence, odkrywaniu wiedzy i nie tylko.

Autorem jest jednen z największych pasjonatów Business Intelligence w JCommerce.

Więcej ciekawych artykułów znajduje się na prywatnym blogu Sławka.

Zapraszamy!

Autorem wpisu jest

Sławomir Drzymała, JCommerce SA

Business Intelligence Specialist

Konsultant Business Intelligence, który na co dzień projektuje i implementuje rozwiązania BI w Microsoft SQL Server, korzystając zarówno z klasycznych narzędzi, jak i funkcjonalności Power BI. Posiada doświadczenie w pracy z Oracle, SAP, IBM, QlikView oraz Pervasive.

Komentarze

  • Aktualnie brak komentarzy.

Skontaktuj się z nami