Power Query – tutorial cz. 1

Zapraszamy również do zapoznania się z serią Poznaj Power BI gdzie omawiane są również transformacje Power Query – serię znajdziesz tutaj.

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ę jednak do codziennej pracy z danymi, do gromadzenia danych czy przygotowywania ich do odkrywania 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 jak pobieranie danych z dowolnej strony czy przygotowywanie oraz czyszczenie danych. Zaprezentowany zostanie zatem cały 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. Plik instalacyjny można znaleźć tutaj: http://www.microsoft.com/pl-pl/download/details.aspx?id=39379 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. Dodatek wygląda tak jak na zdjęciu poniżej. 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ć!

    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 pod adresem: http://en.wikipedia.org/wiki/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.

Excel dokona analizy strukturę 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 wcześniej wymienione tabele.

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

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, które koniec końców tworzą tak naprawdę proces ETL, który może być wielokrotnie odtwarzany. Można 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.

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

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…”.

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”.

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”.

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”.

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.

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”.

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.

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.

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.

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.

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 jednak pobrana nowa tabela z zupełnie innej strony: http://www.geonames.org/countries/ 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.

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.

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:

To co jest również plusem to jak wspomniano wyżej tak przygotowany proces może być wykorzystywany powtórnie, a wystarczy tylko odświeżyć połączenie w Excelu. Daje to możliwość posiadania zawsze aktualnych informacji z wielu różnych miejsc.