Power BI – ładowanie struktur typu matrix

Tabele przestawne w Excelu są jednym z najukochańszych narzędzi analityków. Dane z tych właśnie struktur często kopiowane są z takich struktur i tworzą postać matrixa, który z kolei często jest dla nas źródłem. Jak zatem pobrać dane ułożone w ten charakterystyczny sposób do Power BI? Postaram się wyjaśnić ten wcale nietrudny proces w ramach niniejszego wpisu do lektury którego serdecznie zapraszam.

Na samym początku powiedzmy sobie czym dokładnie jest matrix i o czym tak naprawdę mówię. Struktura standardowej tabeli jest raczej każdemu znana i wygląda następująco:

W przypadku matrixa mówimy o nieco odwróconej logice gdyż część danych znajduje się w kolumnach tak jak w standardowej tabeli, część z kolei znajduje się w wierszach. Konkretne wartości są wyliczone na przecięciach obu wymiarów:

Można powiedzieć, że powyższy zestaw jest efektem działania funkcji Pivot w TSQL 🙂 Skoro mamy już taką strukturę to jak załadować ją do Power BI? To bardzo proste, wystarczy pamiętać o kilku zasadach o których za chwilę sobie powiemy.

Nasz zestaw testowy będzie opierał się o plik Excel zawierający plany sprzedażowe w kilku wersjach dla poszczególnych produktów.
W nagłówkach znajduje się miesiąc oraz wersja planu, a w wierszach identyfikatory produktów, na przecięciu obu wymiarów mamy oczywiście wartość planu:

Załadujmy sobie ten plik używając standardowego konektora Excel od razu przechodząc do edytora Power Query:

W edytorze nasz zbiór danych przedstawia się następująco:

Wygląda trochę nieciekawie prawda? Jak w ogóle do czegoś takiego się dobrać? Nasz cel numer jeden na ten moment to doprowadzić do stanu, gdzie w poszczególnych kolumnach nie będziemy mieli generycznych nazw jak “Column1..ColumnN” tylko właściwe dane. Pierwszym kandydatem na wypełnienie nagłówków danymi jest pierwszy wiersz, przenieśmy go zatem wyżej używając transformacji Use First Row as Hsaders znajdującej się na zakładce Transform:

Sytuacja jest nieco lepsza jednakże nadal mamy generyczne nazwy…:

Gdy spojrzymy co by się stało gdybyśmy przenieśli po raz kolejny pierwszy wiersz do nagłówka to dostrzeżemy, że nie wiele by to dało ponieważ również tam znajdują się puste wartości. Czy zatem stoimy na przegranej pozycji? Oczywiście, że nie! Jeśli nie ma już jak zapełnić luk wartościami z pierwszego wiersza spójrzmy na pierwszą kolumnę w zestawie. W naszym przykładzie znajdują się tam identyfikatory produktów, ponadto możemy dostrzec, że w tej kolumnie nie ma żadnych luk ani wartości pustych, które by nam przeszkadzały. W takim razie przenieśmy dane z pierwszej kolumny do nagłówka. Pierwszym krokiem aby osiągnąć ten cel będzie wykonanie transpozycji – transformacja  ta również w tym przypadku jest dla nas dostępna z poziomu interfejsu graficznego:

Po jej wykonaniu efekt wygląda całkiem dobrze, pierwszy cel prawie osiągnięty. Teraz trzeba przenieść pierwszy wiersz do nagłówka:

Wygląda to już nieco lepiej. W dalszej kolejności musimy wykonać zestaw takich operacjei aby pierwsza kolumna, która zawiera wersje planu nie zawierała wartości pustych (null). Wartości puste w tej konkretnej kolumnie wzięły się stąd, że w Excelu mieliśmy scalone komórki i Power BI wartość z tych komórek umieścił w pierwszym wierszu, a pozostałe wypełnił właśnie wartościami pustymi. W tym konkretnym przypadku algorytm wygląda tak, że jeśli komórka jest pusta to należy wziąć ostatnią znaną wartość i powtórzyć tę czynność dla całego zbioru. Zadanie to nikomu nie powinno przysporzyć problemów ze względu na fakt, że możemy do tego celu wykorzystać transformację Fill dostępną na zakładce Transform:

Jak możecie zauważyć na powyższym zrzucie ekranowym transformacja ta może poruszać się zarówno do dołu jak i do góry, co daje nam pewną elastyczność i możemy dostosować nasz algorytm do konkretnego przypadku. W przedstawionym przykładzie poruszamy się w dół. Po wykonaniu tej transformacji nasza kolumna wygląda już w porządku (przy okazji zmieniłem nazwę tej kolumny na Version, a kolumny obok na Month) :

Pierwsze dwie kolumny wyglądają naprawdę dobrze, a wszystkie pozostałe dane znajdujące się w kolumnach to identyfikatory produktów, które raczej wolelibyśmy mieć w wierszach zamiast w kolumnach. Kiedy zdajemy sobie sprawę, że coś jest w kolumnach zamiast w wierszach to na myśl powinna nam przyjść operacja unpivot, którą właśnie w tym miejscu użyjemy (przy okazji wykonałem kilka kosmetycznych poprawek nazw kolumn na zbiorze):

Nasze dane wyglądają już całkiem przyzwoicie! Mamy upragnioną formę tabelaryczną! Nie zawsze jednak nasza tabela wygląda tak jak w powyższym przykładzie i w wierszach nie mamy tylko jednego atrybutu, a wiele. Wtedy też metoda używająca transpozycji nam się nie powiedzie prawda? Całkowita racja! Ale i na to znajdziemy sposób – zasymulowałem taką sytuację i do wierszy dodałem nowy atrybut o nazwie ProductGroupKey:

Sposobów przekształcenia czegoś takiego jest co najmniej kilka, ja postaram się przedstawić najprostszy z nich, który tak naprawdę doda tylko dwa dodatkowe kroki do techniki przedstawionej wyżej. Pierwszym ze wspomnianych kroków jest konkatenacja ProductGroupKey oraz ProductKey (jako separator należy wybrać taki ciąg, który nie będzie występował w żadnej z łączonych kolumn). Po wykonaniu tej operacji reszta wygląda dokładnie tak samo jak powyżej. Wykonane przeze mnie transformacje możecie zobaczyć poniżej:

Nasz bieżący stan wygląda następująco:

Pozostaje nam rozdzielić pole na odpowiadające ProductKey i ProductKeyGroup dokonamy tego za pomocą transformacji Split Column i jej opcji By Delimiter:

Konfiguracja jest bardzo prosta i intuicyjna – wystarczy wybrać separator według którego chcemy dokonać podziału oraz czy podział ma nastąpić wraz z każdym jego wystąpieniem czy pierwszym wystąpieniem z lewej/prawej:

Po rozdzieleniu trzeba nadać odpowiednie nazwy i właściwie to wszystko. Jeśli chodzi o aspekty wydajnościowe to warto pamiętać o tym, że struktury typu matrix zazwyczaj pochodzą z Excela lub sieci Web i raczej nie powinniśmy mieć tam do czynienia z ogromnymi wolumenami i Power BI poradzi sobie z całym procesem stosunkowo dobrze. Trzeba również pamiętać, że operacje takie jak np. transpozycja nie są przerzucane na źródło takie jak np. baza danych w ramach mechanizmu Query Folding (o którym opowiemy sobie w ramach innego wpisu) i będą wykonywane lokalnie przez Power BI więc powinniśmy ich unikać, ale na sensownych rozmiarowo danych nie powinny one stanowić problemu.

Podsumowując możemy powiedzieć, że ładowanie struktur tego typu nie powinno sprawić nam problemów. Power BI/ Power Query jest świetnym narzędziem do obrabiania niekoniecznie uporządkowanych danych i w tzw. data munging sprawdza się naprawdę dobrze.  Wspominałem już, że jest bardzo wiele sposobów do osiągnięcia tego samego i zdaję sobie z tego doskonale sprawę, chciałem jednak pokazać Wam bardzo prosty sposób ładowania matriksów i chyba mi się udało. Zauważcie również, że nie dotknęliśmy kodu w M co też jest w moim mniemaniu dosyć dużą zaletą tego podejścia.