Przekształcenie skonsolidowanych danych z Power Query

Rewelacyjne narzędzie typu ETL, jakim jest Power Query, stworzone zostało w celu ułatwienia i zautomatyzowania procesu pobierania i przekształcania danych. Zdaniem wielu analityków, w tym także moim, Power Query to najbardziej rewolucyjna funkcjonalność, jaka została zaimplementowana do Excela po wprowadzeniu tabel przestawnych. Z jego wykorzystaniem zaoszczędziłem godziny poświęcone m. in. na codzienne pobieranie danych z różnych systemów, łącznie, przekształcenie oraz przeklejanie ich do moich plików, przeciąganie formuł itp. Tworząc zapytanie, czyli z ang. query, jesteśmy w stanie przygotować odświeżalną tabelę połączoną bezpośrednio ze źródłem i w pełni dostosowaną do naszych potrzeb. Takie zapytanie tworzymy raz, a później tylko je odświeżamy w celu zaczytania aktualnych danych. W dzisiejszym wpisie przedstawię konkretny przykład na przekształcenie danych w Power Query.

O Power Query można by jeszcze długo pisać. Na szczęście przyswojenie umiejętności jego obsługi jest bardzo intuicyjne i nie zajmuje wiele czasu. Myślę, że już po pierwszym dniu można zacząć wydajnie z nim pracować. Na łamach mojego bloga na pewno jeszcze sporo na ten temat przeczytacie.

Przekształcenie skonsolidowanych danych z Power Query

Często nasze dane źródłowe mają formę pogrupowanego konspektu (jak na wycinku niżej). Zdarza się, że otrzymujemy je w takiej postaci od innych użytkowników bądź generujemy je bezpośrednio z różnego rodzaju systemów. Niestety dalsza obróbka oraz ich analiza staje się bardzo trudna. Nie możemy chociażby użyć ich jako źródła dla tabeli przestawnej bo ich układ nie jest bazodanowy.  W opisywanym przykładzie chcąc sprawdzić łączną ilość dla kilku wybranych kontrahentów lub cech dostawy musimy żmudnie wybierać interesujące nas dane oraz przeklejać je do osobnego arkusza. Takie rozwiązanie nie dość, że jest czasochłonne, to dodatkowo obarczone dużym ryzykiem popełnienia błędu.

W celu przekształcenia danych do pożądanej i elastycznej formy musimy załadować je do edytora Power Query. Aby uniknąć ryzyka niezaczytania wszystkich danych wstawiamy całe nasze zestawienie w jedną tabelę Excela. Wybieramy dowolną komórkę znajdująca się w obrębie naszego zestawienia i z karty „Wstawianie” wybieramy „Tabela”.

Excel samodzielnie rozpozna zakres naszych danych oraz ich nagłówki. Domyślnie sformatowany obiekt tabeli wygląda podobnie jak poniżej.

Załadowanie danych do edytora Power Query

W kolejnym kroku z karty „Dane” wybieramy opcję „Z tabeli / zakresu” i ładujemy nasze dane do edytora Power Query. Jeśli pracujemy na Excelu 2010 lub 2013 to analogiczną opcje odnajdujemy w karcie „Power Query”.

W edytorze przechodzimy do karty „Dodaj kolumnę” i wybieramy „Kolumna warunkowa”. Uzupełniamy pojawiające się okno. Dzięki temu wyciągniemy informację o nazwach naszych kontrahentów.

Powstałą nową kolumnę przenosimy na początek naszej tabeli, a następnie wybieramy przekształcenie „Wypełnij w dół”. Dzięki temu każdy wiersz będzie miał przypisanego właściwego dla siebie kontrahenta.

W kolejnym kroku odfiltrowujemy wszystkie wiersze z wartościami „null”. Najlepiej zrobić to jednorazowo z kolumny „Data_zamknięcia_dostawy”.

Następnie z pierwszej kolumny wyciągamy nazwę naszego kontrahenta. Wybieramy opcję „Wyodrębnij”, „Tekst między ogranicznikami”. W tym konkretnym przypadku nazwy są otoczone z przodu ogranicznikiem „: ” oraz z tyłu „ (”. W innych przypadkach możemy elastycznie wykorzystywać pozostałe opcje. Po zatwierdzeniu pokażą się nam same nazwy kontrahentów.

To w zasadzie koniec naszego przekształcenia. Teraz wybieramy „Zamknij i załaduj”. Domyślnie ładujemy nasze przekształcone zestawienie do tabeli w nowym arkuszu.

W efekcie otrzymujmy osobną tabelę wynikową. Teraz nasze dane mają układ bazodanowy i możemy podpiąć je po tabelę przestawną. Przekształcenie naszych danych w Power Query zakończyło się powodzeniem. Dzięki temu zyskujemy możliwość ich analizy i wyciągania odpowiednich wniosków.

Aktualizacja danych źródłowych i odświeżenie tabeli wynikowej

Nieocenioną zaletą utworzonego połączenia jest możliwość aktualizowania naszych danych źródłowych i osiągania oczekiwanego wyniku bez konieczności przechodzenia od nowa wszystkich powyższych kroków. Załóżmy, że dochodzą nam nowe dane bądź całkowicie zmienia się układ kontrahentów w nowym otrzymanym raporcie. Wystarczy, że podkleimy aktualne dane w miejsce starego raportu i odświeżymy tabelę wynikową.