Power Query – magia dla każdego

W świecie Excela powstanie dodatku Power Query szybko stało się jednym z najważniejszych wydarzeń od czasu wprowadzenia tabel przestawnych. Power Query jest najważniejszą częścią grupy dodatków do Excela, do której należą jeszcze Power Pivot, Power View i Power Map. Jest również kluczowym elementem usługi Power BI dostępnej w Office 365. Jego poznanie i używanie pozwalają bardzo szybko osiągnąć spektakularne efekty wcześniej dostępne tylko dla najbardziej zaawansowanych użytkowników. Bardzo szybki rozwój i wbudowanie Power Query do Excela otworzyło ogrom nowych możliwości analizy danych dla wszystkich użytkowników.

Etapy przygotowania danych do analizy.

Bardzo często albo wręcz zawsze przygotowanie zbioru danych do późniejszej analizy obejmuje 4 czynności. Są to:

• przekształcanie danych w taki sposób, aby jak najłatwiej można je było później analizować;
• dodawanie do już gotowego zbioru danych, kolejnych zbiorów, często w innym formacie, tak aby wynikowy zbiór obejmował więcej szczegółów;
• łączenie ze sobą danych, które mają taką samą strukturę, ale podzielone są na wiele mniejszych zbiorów;
• wzbogacanie istniejącego zbioru danych szczegółami, których brakuje w zbiorach wejściowych, a ułatwiają czy czasami wręcz pozwalają dokonać analizy.

Bez względu na przeznaczenie danych – czy jest to prosty raport czy bardzo rozbudowana aplikacja BI, również bez względu na użyte narzędzia – VBA, SQL czy inne języki – przygotowanie danych do analizy jest bardzo ważne (czasem decydujące) dla powodzenia przedsięwzięcia.

Dane wejściowe rzadko mają dokładnie taką postać, jaka jest optymalna do dalszej analizy. Najczęściej są zawarte w plikach o różnych formatach, w bazach danych, lokalizacjach (czasami bardzo odległych). Najcięższą częścią analizowania danych jest ich oczyszczenie z informacji niepotrzebnych, błędnych, przefiltrowanie zgodnie z wymaganiami i nadanie im postaci najlepszej do zastosowania narządzi analitycznych Excela, bo ten program jest najczęściej używany do tego celu. Doprowadzenie danych do postaci uporządkowanej tabeli w Excelu często jest najmniej wdzięczną i najbardziej pracochłonną czynnością. Dodatkowo każda drobna zmiana w strukturze danych wejściowych (na którą najczęściej nie mamy wpływu) może wymusić powtórzenie całego mozolnego procesu od początku.

„Stare metody” obróbki danych.

Do momentu powstania Power Query, w zależności od doświadczenia i umiejętności posługiwania się, do przygotowania danych używało się:

• wbudowanych funkcji Excela, które stosuje większość użytkowników tego programu; bardzo dużo zależy od doświadczenia użytkownika, jego osobistych przyzwyczajeń i wygody, z drugiej strony rozbudowane funkcje stają się skomplikowane i trudne do zrozumienia; najczęściej używanymi do tego celu funkcjami są: WYSZUKAJ.PIONOWO(), INDEKS(), PODAJ.POZYCJĘ(), PRZESUNIĘCIE(), LEWY(), DŁ(), USUŃ.ZBĘDNE.ODSTĘPY(), OCZYŚĆ().
• VBA – czyli Visual Basic for Applications, wbudowany język programowania, który pozwala zautomatyzować wszystkie czynności wykonywane w Excelu (i oczywiście w pozostałych aplikacjach pakietu Office), a wśród nich możliwe jest również zaprogramowanie wszystkich czynności niezbędnych do przekształcenia i przygotowania danych do analizy; osiągnięcie sprawności w posługiwaniu się VBA wymaga niestety czasu i cierpliwości;
• SQL – znany bardziej jako język programowania wśród specjalistów od baz danych; stworzony specjalnie m.in. do sortowania, grupowania i przekształcania danych w bazach danych; chyba najtrudniejszy do nauczenia i do osiągniecia zaawansowanego poziomu użytecznego do przygotowywania zbiorów danych.

W dalszym ciągu oczywiście stosuje się te rozwiązania, ale coraz częściej okazuje się, że brakuje czasu, aby nauczyć się i nabrać wprawy w ich stosowaniu. Czasami już działające rozwiązania są tak skomplikowane, że ich naprawa, modyfikacja są trudne lub niemożliwe do wykonania z bardzo prozaicznego powodu. W mniejszych organizacjach takie skomplikowane rozwiązania są tworzone i na bieżąco rozwijane przez bardzo nieliczną grupę specjalistów, a często wręcz pojedyncze osoby. Po ich odejściu nie ma już nikogo kto byłby w stanie zapanować nad całością, naprawić czy zmodyfikować coś, co do tej pory działało dobrze.

Z drugiej strony konieczność szybkiego przygotowania i analizy danych pojawia się w miejscach, gdzie do tej pory nikt tego robił. Trudno wymagać więc, żeby wszyscy obarczeni tego typu zadaniami zaczęli się uczyć zaawansowanego używania Excela, VBA czy SQL. Ilość czasu potrzebna na tradycyjne, mniej lub bardziej zautomatyzowane przygotowanie danych, pomnożona przez ilość osób w organizacji pozwala dostrzec jak pracochłonne i kosztowne jest to działanie. A co, jeśli odniesiemy to całej gospodarki choćby jednego kraju?

Do gry wchodzi Power Query.

Rozwiązanie, które jest dosyć łatwe do nauczenia się w stopniu pozwalającym wykonywać bardzo wiele skomplikowanych operacji. Raz stworzone rozwiązanie może być ponownie stosowane tyle razy, ile potrzeba, nawet w przypadku drobnych zmian w danych wejściowych, odpowiednio przygotowane pozwala ominąć ewentualne błędy lub wskazać dokładne miejsce, które wymaga zmiany. Rozwijanie lub rozbudowa rozwiązania jest ułatwiona, ponieważ każda czynność wykonywana na danych jest rejestrowana jako osobny krok całego procesu (swego rodzaju program czy recepta). Lista czynności może być łatwo potem modyfikowana czy uaktualniana. W porównaniu do funkcji Excela, VBA czy SQL – Power Query nawet dla całkiem początkujących użytkowników jest łatwiejsze do nauczenia się i pozwala szybciej osiągnąć zadowalające rezultaty.

Bardzo skomplikowane rozwiązania stworzone przy pomocy Power Query mogą być później modyfikowane nawet przez bardzo początkujących użytkowników – nie ma w ten sposób potrzeby, aby z powrotem angażować bardzo zaawansowanych użytkowników do wykonywania drobnych zmian, których można się bardzo szybko nauczyć się samodzielnie.

Jeśli chodzi o szybkie połączenie się ze źródłem danych, proste uporządkowanie, a później zbudowanie prostego wykresu czy raportu Power Query jest bezkonkurencyjne. Osoby, które dopiero zaczynają pracę z danymi na większą skalę często nie mają czasu ani ochoty, aby cierpliwie uczyć się formuł Excela, programowania w VBA czy SQL. W Power Query gotowe rozwiązanie można szybko wyklikać w programie wyglądającym podobnie do Excela. Można nawet nie znać ani rozumieć do końca użytych reguł, ale stworzyć działające prawidłowo rozwiązanie.

Mimo, że wielu zadań nie można teraz i nie będzie można w przyszłości wykonać w Power Query tak jak w VBA czy SQL, to większość szybkich (często jednorazowych) analiz będzie wykonywanych właśnie w Power Query. Mamy więc kolejne genialne wręcz narzędzie, które ułatwia analizowanie danych, wyciąganie wniosków i podejmowanie decyzji w świecie, który wręcz jest napędzany gigantyczną ilością danych. Ci, którzy potrafią szybko z tych danych skorzystać zdobywają przewagę nad swoimi konkurentami. Powstają również całe nowe branże, które zajmują się gromadzeniem, przetwarzaniem i udostępnianiem danych.

Jak wygląda praca z Power Query?

Power Query jest traktowane jako narzędzie ETL. Ten skrót pochodzi od angielskich słów: extract (czyli wydobycie, wyciągnięcie, ekstrakcja), transform (czyli transformacja, przekształcenie) i load (czyli wczytanie, załadowanie).

• Etap pierwszy, czyli extract:
  • Power Query pozwala wyciągnąć dane m.in. z plików tekstowych, CSV, plików Excela, baz danych, stron internetowych. Dzięki ciągle dodawanym łącznikom, dane można czerpać z coraz to większej ilości usług jak serwery Exchange, Salesforce i wielu, wielu innych. Wiele produktów czy usług specjalnie umożliwia dostęp do gromadzonych danych, tak aby użytkownik mógł we własnym zakresie tworzyć takie analizy jakie są mu najbardziej potrzebne.
• Etap drugi, czyli transform:
  • Power Query pozwala przede wszystkim oczyścić dane z niepotrzebnych czy uszkodzonych fragmentów. Pozwala zmieniać format danych tak aby najlepiej odpowiadały potrzebom, np. daty, dane walutowe czy dane w formacie zależnym od lokalizacji. Dane uzyskane z wielu źródeł możemy łączyć w poziomie, dodając więcej szczegółów do już istniejących zapisów. Możemy je również łączyć w pionie zwiększając np. zakres czasowy obejmowany przez dane. Możemy w końcu grupować dane względem wybranych kryteriów. Dodatkowo zanim jeszcze dane zostaną wczytane np. do tabeli Excela, możemy wykonywać na nich obliczenia, których wyniki umieścimy w stworzonej kolumnie, możemy dynamicznie tworzyć całe, nowe tabele danych, których zawartość będzie zawsze się aktualizowała razem ze zmianami w źródle danych. Przykładowo stosunkowo łatwo można stworzyć dynamiczną tabelę zawierającą wszystkie daty z analizowanego zakresu, która później pozwoli powiązać wszystkie obliczenia z jedną wspólną osią czasu, dzięki czemu łatwiej jest dostrzec zależności od czasu i między stworzonymi wartościami.
• Etap trzeci, czyli load:
  • w uproszczeniu Power Query pozwala wczytać dane do 4 miejsc:
    • do tabeli Excela,
    • do modelu danych Power Pivot,
    • do usługi Power BI,
    • do połączenia.

Pierwsze 3 miejsca są trochę do siebie podobne. Dane są wczytywane, a dalej dostępnymi narzędziami Excela, Power Pivot czy Power BI są znów przetwarzane czy wprost tworzone są z nich wykresy, tabele itp.

Ostatnie, 4 miejsce jest o tyle ciekawe, że jest to tylko połączenie, albo inaczej zapytanie do źródła danych, które może być z kolei źródłem kolejnego zapytania czy zapytań. Nie zawiera danych więc plik z tym połączeniem nie jest duży, ale umożliwia dostęp do olbrzymiej ilości danych, które są przygotowane dokładnie w taki sposób jaki jest nam potrzebny. Dzięki temu możemy przekroczyć np. ograniczenie ilości wierszy w tabeli Excela. Może analizować dane, których fizycznie nie ma w pliku. Możemy na ich podstawie budować wszelkiego rodzaju wizualizacje, obliczenia. Możemy je udostępniać, bez obawy, że przekażemy całość danych w niepowołane ręce.

Najważniejszą rzeczą jest jednak zdawanie sobie sprawy, że Power Query pozwala na dowolnie częste odświeżanie wcześniej stworzonego zestawu danych. Cały proces, składający się z pojedynczych operacji mamy zapisany w postaci pewnego rodzaju program, który możemy dowolnie modyfikować, ale tworzymy go tylko raz. Później, kiedy dane w źródle zmieniają się, przybywa ich – jedyna rzecz, którą musimy zrobić to odświeżyć dane i nasze wykresy, zestawienia i obliczenia są aktualne. To samo w przypadku, gdy dane się całkowicie zmieniają, ale ich struktura już nie.

Prawdziwa magia i potęga Power Query.

Wyobraźmy sobie taką sytuację.

Raz w tygodniu mamy do wykonania analizę. Co tydzień otrzymujemy nowy plik z danymi, które najpierw musimy przetworzyć, aby później na ich podstawie wykonać np. raport, zestawienie. Powiedzmy, że przygotowanie danych za każdym razem od nowa zajmuje nam 30 minut, a sam raport robimy bardzo szybko podmieniając tylko dane wejściowe.

Teraz taki sam zestaw danych przetwarzamy przy pomocy Power Query. Niech praca zajmie nam 15 minut. Zysk niewielki po raz pierwszy, ale już zaoszczędziliśmy około 15 minut. Cała zabawa zaczyna się kolejnym razem, po tygodniu. Otrzymujemy nowy zestaw danych, który zapisujemy w miejscu starego. Jedyna rzecz, którą musimy jeszcze zrobić to odświeżyć dane w Power Query, np. Excelu. Praca wykonana, niemal natychmiast. I tak każdego tygodnia zyskujemy około 30 minut. Pomnożone przez wszystkie tygodnie w roku i inne podobne zadania daje nam skalę możliwości Power Query. Jeśli struktura danych czy raport zmienią się – wystarczy jeden, pierwszy raz zmodyfikować działanie w Power Query i znów możemy spokojnie czekać na kolejny zestaw danych.

Na zakończenie.

Pozostaje jeszcze kwestia aktualizacji Power Query. Zmiany są wprowadzane w cyklu miesięcznym, więc rozwój narzędzia jest błyskawiczny. Wciąż dodawane są nowe funkcje, wizualizacje, łączniki danych. Już istniejące są często optymalizowane, ulepszane. Tak samo szybko poprawiane są również ewentualne błędy. Społeczność użytkowników Power Query powiększa się równie szybko, a ilość wszelkiego rodzaju poradników, prezentacji jest chyba niemożliwa do ogarnięcia.

Na pewno dla własnej wygody, szacunku dla własnego czasu a czasem po prostu ciekawości warto próbować używać Power Query i znajdować nowe zastosowania dla tego magicznego narzędzia.