Power BI – transpose, fill, unpivot czyli czyli ładowanie danych ze struktury typu matrix

Pora zgłębić kolejne możliwości transformacji dostępnych w Power BI. Najlepiej będzie przedstawić ich działanie na “życiowym” przykładzie dlatego też przedstawię jeden z najpopularniejszych problemów z jakimi trzeba się zmierzyć w codziennej pracy. Bardzo często dane które musimy załadować do narzędzia analitycznego nie mają do końca przyjaznej formy tabelarycznej tylko np. matrixa, tabeli przestawnej czy innej podobnej struktury. Wtedy też musimy użyć dostępnych transformacji aby przekształcić te dane do formy tabelarycznej – zdatnej do używania w modelu. Dziś pokażemy jak to zrobić w Power BI – zapraszam do lektury.

Przyjrzyjmy się poniższej strukturze zapisanej w programie Excel. Zapis taki jest bardzo popularny ze względu na swoją przejrzystość i łatwość wpisywania konkretnych wartości.

Nie jest to jednak przyjazna forma dla żadnego narzędzia BI – jeśli to możliwe powinniśmy ładować dane w postacie tabeli. Zdaję sobie jednak sprawę, że nie zawsze mamy wybór – tak więc załadujmy powyższy zestaw do narzędzia. Standardowo w pierwszym kroku wybieramy połączenie do programu Excel i wskazujemy zakres w jakim znajdują się nasze dane. Oczywiście dane w tej formie nie są dla nas interesujące i musimy je przekształcić dlatego też klikamy Edit.

Mając teraz taki zestaw danych możemy go przetransponować czyli zamienić wiersze z kolumnami. Uczynimy to używając dostępnej transformacji o samo opisującej nazwie Transponse znajdującej się na wstążce w zakładce Transform.

Jak można zauważyć na powyższym zrzucie ekranowym dane są już w trochę lepszej formie niż poprzednio. Widać jednak, że mimo wszystko musimy dokonać kolejnych transformacji – pierwszą z nich jaką się zajmiemy jest kolumna z rokiem. Istnieje cały szereg pustych wartości – wiemy, że wartości NULL występujące po konkretnym roku są tak naprawdę równe temu roku – dlatego też musimy znaleźć sposób na uzupełnienie tych luk. Jak pewnie się domyślacie istnieje wbudowana transformacja, która takową operację wykona za nas – znajduje się on znów na zakładce Transform i nosi nazwę Fill. Aby jej użyć musimy zaznaczyć kolumnę, którą chcemy uzupełnić i wybrać Fill – następnie z dostępnych opcji należy wybrać Down. Transformacja ta weźmie wartość na jaką natrafi i uzupełnia nią wszystkie wartości puste posuwając się ku dołowi. W momencie gdy transformacja natrafi na wartość niepustą to zaczyna znów nadpisywać puste pola tym razem ostatnią wartością na jaką natrafiła i tak aż do końca zestawu danych. Drugą dostępną opcją jest Up, który wykonuje analogiczną czynność zaczynając od dołu i kierując się ku górze.

Pierwszy problem z głowy – teraz musimy “przekręcić” produkty tak aby zamiast w kolumnach znalazły się w wierszach. Zanim to zrobimy najlepiej ustawić produkty w takiej formie jako nagłówki kolumn wybierając opcję Use First Row As Headers (jest ona dostępna zarówno ze wstążki jak i z menu kontekstowego). Warto tutaj odnotować fakt, iż można również tutaj zrobić rzecz odwrotną z wykorzystaniem opcji Use Headers as First Row tj. zamienić nagłówki na wiersz – może to być przydatne wtedy gdy nasze dane są pozbawione nagłówków, a program mylnie potraktował pierwszy napotkany wiersz jako właśnie nagłówek.

Ostatnią rzeczą jaka nam została polega na zamienieniu kolumn z produktami na wiersze – aby tego dokonać można użyć opcji Unpivot Columns. Tak więc zaznaczamy kolumny z produktami i wybieramy Unpivot columns.

Nasz zestaw jest prawie gotowy – teraz wystarczy nadać nazwy kolumną poprzez dwukrotne kliknięcie na nagłówek i dane są gotowe do użycia na raporcie. Jak widać tego typu problem z danymi źródłowymi jest dosyć łatwy do rozwiązania w kilku transformacjach.

Tego typu transformacje bardzo łatwo zaimplementować w tworzonym rozwiązaniu. Analogicznie można je stworzyć używając Microsoft Excel wraz z dodatkiem Power Query. Moim zdaniem aby nabrać płynności w użytkowaniu narzędzia trzeba je wykorzystywać w praktyce no i przede wszystkim znać jego możliwości – dlatego też w kolejnych częściach serii postaram się dalej zgłębiać transformacje, by następnie przejść już do bardziej zaawansowanych możliwości. Mam nadzieję, że powyższy opis okazał się dla Was przydatny – zachęcam również do regularnego przeglądania nowych artykułów w ramach serii Poznaj Power BI.