Jak efektywnie przekształcać dane w Power Query: podstawy pivotowania i unpivotowania

W pracy z danymi w Excelu często korzystamy z tabel przestawnych, które oferują szybkie podsumowanie danych poprzez ich agregację, co jest bardzo pomocne w analizach. Z kolei Power Query oferuje funkcję zbliżoną do tabel przestawnych, umożliwiającą tworzenie nagłówków kolumn z wartości określonej kolumny oraz podsumowanie danych na podstawie innych kolumn. Funkcja ta, nazwana "Pivot Column", jest przydatna do tworzenia prostych podsumowań, jednak nie oferuje wszystkich możliwości tabel przestawnych. Głównym celem Power Query jest przekształcanie danych z różnych źródeł w taki sposób, by były one gotowe do dalszego przetwarzania i analizy.

W praktyce często spotykamy się z sytuacjami, gdy dane są przygotowane w sposób wygodny do ręcznego wprowadzenia, ale nie nadają się do analizy przez narzędzia takie jak Excel czy bazy danych. Tego rodzaju dane wymagają przekształcenia, by stały się bardziej strukturalne i przystępne do analizy. Takie dane mogą przypominać raporty przypominające tabelę przestawną, w której podsumowania są rozmieszczone w formie wielu kolumn, a nagłówki są rozmieszczone w sposób mniej uporządkowany.

Aby lepiej zrozumieć zasady funkcji unpivot w Power Query, warto zapoznać się z przykładem danych przedstawionych w pliku Excel "Introduction to pivot and unpivot.xlsx". Kluczowym założeniem przy takich transformacjach jest zasada, że jeden typ danych powinien znajdować się w jednej kolumnie danych. Na przykład wartości podsumowujące przychody w różnych miesiącach, z wyjątkiem wartości sumujących, powinny być umieszczone w jednej kolumnie "Revenue". Następnie te dane należy połączyć z odpowiednimi nagłówkami wierszy i kolumn, a także z metadanymi raportu, które powinny być uwzględnione w jednej zorganizowanej tabeli.

W Power Query ważne jest również, by wszystkie wartości z nagłówków kolumn, nagłówków wierszy i metadanych raportu znalazły się w odpowiednich kolumnach. Warto pamiętać, że wszelkie nadmiarowe dane, takie jak sumy, powinny zostać usunięte, by uniknąć wprowadzania zbędnych informacji do struktury danych. Celem jest stworzenie jednorodnej tabeli, która umożliwi łatwiejsze i szybsze przetwarzanie danych w dalszych etapach analizy.

Pierwsze przykłady przekształcania danych zaczynają się od tzw. unpivotowania kolumn. Z danych przygotowanych w pliku Excel "FirstReport" widać, że dane zostały wprowadzone do arkusza w sposób, który nie jest zgodny z zasadami struktury danych. Przykładowo, w komórce A1 umieszczono informację o kraju, dla którego przygotowano raport, jednak brakuje danych powtarzających się dla sprzedawców w kolumnie "Salesman". Ponadto kolumna z sumą ogólną nie jest potrzebna w tym przypadku.

Ważnym elementem w przygotowywaniu danych do dalszych transformacji jest usunięcie pierwszych dwóch wierszy, które zawierają jedynie informacje o kraju, dla którego raport został przygotowany. Tego typu dane będą dodane później, ale na etapie unpivotowania należy je usunąć, by nie wprowadzać ich do finalnej struktury danych. Tego rodzaju operacje w Power Query umożliwiają szybkie i sprawne przekształcanie danych w sposób, który pozwala na ich łatwiejsze analizowanie i wykorzystywanie w dalszych etapach pracy.

Kluczowym elementem w procesie unpivotowania danych w Power Query jest zapewnienie, by dane były odpowiednio uporządkowane, bez zbędnych sum i powtórzeń, oraz by wartości z nagłówków kolumn, wierszy i metadanych zostały przypisane do odpowiednich kolumn w przekształconej tabeli. Działania te są niezwykle istotne w kontekście późniejszych analiz, gdyż niewłaściwie przekształcone dane mogą prowadzić do błędnych wyników analitycznych.

Podstawowe zasady dotyczące pivotowania i unpivotowania danych stanowią fundament efektywnej pracy z Power Query. Dzięki tym technikom użytkownicy są w stanie reorganizować dane w sposób, który ułatwia ich dalszą obróbkę, a także analizę. Umiejętność skutecznego przekształcania danych na potrzeby raportowania oraz analizy stanowi nieocenioną wartość w pracy z dużymi zbiorami danych.

Jak przekształcić dane w Power Query: Procesy transpozycji, scalania i podziału

Proces zaczyna się od transpozycji danych, czyli zmiany miejscami wierszy i kolumn. To narzędzie pozwala na przekształcenie danych z formatu, w którym informacje o miesiącach są zawarte w jednej kolumnie, w format, w którym każdy miesiąc staje się osobną kolumną, a wartości dotyczące godzin i kosztów są przypisane do tych miesięcy. Po transpozycji tabela zawiera 7 kolumn i 8 wierszy, jednak należy pamiętać, że transpozycja nie zachowuje oryginalnych nazw nagłówków kolumn, które zostaną zastąpione domyślnymi nazwami.

W tej sytuacji idealnym rozwiązaniem jest użycie opcji "Fill" w Power Query, która pozwala na wypełnienie pustych wartości (null) w pierwszej kolumnie. Puste komórki pozostaną niezmienione, ponieważ nie mają wartości do zastąpienia. Po wypełnieniu pustych wartości, warto połączyć pierwsze dwie kolumny, aby uzyskać spójną nazwę w jednej kolumnie. Należy pamiętać, że podczas scalania wartości z null, wynik zawiera jedynie tekstową część, ponieważ null nie wpływa na wynik połączenia.

Po scaleniu kolumn i przekształceniu danych w tabelę, można ponownie zastosować funkcję transpozycji, aby przywrócić dane do pierwotnego układu. Warto przy tym zauważyć, że Power Query automatycznie przypisuje typy danych do kolumn, co ułatwia dalszą obróbkę. Jeżeli dane w tabeli zawierają wartości null, można je łatwo zastąpić, na przykład, zerami. Następnie, używając opcji "Unpivot Other Columns", należy przekroczyć granice kolumn i przejść do etapu, w którym wartości w kolumnie "Attribute" są scalane w jedną jednostkę bez separatora.

Często w takich przypadkach, jak w tym przykładzie, kiedy nazwy miesięcy są skrócone do trzech liter, potrzebne będzie podzielenie danych na kilka części. W tym celu stosuje się funkcję "Split Column", wybierając opcję "By Number of Characters". Dzięki temu można rozdzielić wartość w kolumnie "Attribute" na dwie części, rozdzielając je po trzech pierwszych znakach, co pozwala na stworzenie nowych kolumn dla miesięcy. Można również używać innych funkcji, takich jak Text.Start, Text.End, lub Text.Middle, które pozwalają na bardziej dynamiczne dzielenie tekstu, nawet w bardziej złożonych przypadkach.

Jednak kluczowym aspektem jest także możliwość automatycznego dzielenia kolumny przy użyciu opcji "By Lowercase to Uppercase", w której Power Query analizuje przejście między małymi a dużymi literami, co pozwala na automatyczne tworzenie nowych kolumn. Ten proces jest pomocny, gdy mamy do czynienia z tekstem, w którym małe i wielkie litery rozdzielają różne części danych. Dzięki temu, dane w kolumnach mogą zostać poprawnie podzielone na mniejsze jednostki, co ułatwia dalszą analizę.

Po wykonaniu tych operacji można wykonać kolejną transformację danych, korzystając z polecenia "Pivot Column". Ta funkcja pozwala na przekształcenie danych z formatu wierszy w format kolumn, dzięki czemu możliwe staje się uzyskanie ostatecznej tabeli, w której wartości są uporządkowane w odpowiednich kolumnach. Po zakończeniu transformacji, dane mogą zostać załadowane do Excela i wykorzystane w analizach.

Warto również pamiętać, że Power Query umożliwia zarządzanie dużymi zbiorami danych w sposób prosty i intuicyjny. Korzystając z narzędzi do transformacji danych, można znacząco uprościć procesy obróbki danych, eliminując wiele ręcznych operacji, które mogłyby być czasochłonne i podatne na błędy. Przekształcanie powtarzających się wierszy w kolumny jest szczególnie przydatne, gdy mamy do czynienia z dużymi zestawami danych, które wymagają odpowiedniego podziału i strukturalizacji.

Jak poprawnie tworzyć kolumny warunkowe i łączyć testy logiczne w Power Query?

Podczas pracy z Power Query często zachodzi potrzeba dodania nowych kolumn na podstawie złożonych warunków. Przykładem może być obliczanie nadgodzin lub przyznawanie premii pracownikom na podstawie określonych kryteriów sprzedażowych. W takich przypadkach standardowe narzędzia interfejsu użytkownika, takie jak okno „Dodaj kolumnę warunkową”, mogą okazać się niewystarczające, gdyż nie obsługują formuł wykraczających poza proste, liniowe warunki. Dlatego konieczne jest korzystanie z polecenia „Kolumna niestandardowa” i ręczne pisanie formuł w języku M.

W przypadku obliczania nadgodzin warto zwrócić uwagę na poprawne stosowanie typu danych duration. Przykładowa formuła sprawdza, czy czas pracy przekracza 8 godzin, a następnie wylicza wartość nadliczbową jako różnicę między rzeczywistym czasem pracy a 8 godzinami. Należy przy tym unikać zwracania wartości 0 jako liczby, gdyż powoduje to pojawianie się pustych komórek w Excelu. Zamiast tego stosuje się #duration(0, 0, 0, 0), czyli zero w formacie czasu trwania, co gwarantuje poprawne wyświetlanie wartości. Przykład takiej formuły to:

if [Work Time] > #duration(0, 8, 0, 0) then [Work Time] - #duration(0, 8, 0, 0) else #duration(0, 0, 0, 0)

Alternatywnie, gdy dane dotyczą liczby godzin jako wartości liczbowej, formuła staje się prostsza, np.:

if [Total Hours] > 8 then [Total Hours] - 8 else 0

Należy jednak pamiętać o odpowiednim przypisaniu typu danych (np. liczbowego), aby wynik był właściwie interpretowany.

W zakresie testów logicznych Power Query pozwala na łączenie warunków za pomocą operatorów and i or. Operator or zwraca true, gdy choć jeden z warunków jest spełniony, natomiast operator and wymaga spełnienia wszystkich warunków. To rozróżnienie jest kluczowe przy definiowaniu reguł przyznawania premii lub innych decyzji biznesowych. Przykład formuły z operatorem or dla premii może wyglądać tak:

if [Sales Quantity] > 60 or [Sales Value] > 8000 then "Bonus" else null

Z kolei dla operatora and:

if [Sales Quantity] > 50 and [Sales Value] > 7000 then "Bonus" else ""

Ważne jest, aby odpowiednio zdefiniować wartość zwracaną w przypadku niespełnienia warunków — null lub pusty tekst, co przekłada się na puste komórki w Excelu.

Tworzenie bardziej złożonych warunków, na przykład wielopoziomowego oceniania lub zagnieżdżonych instrukcji if, wymaga precyzyjnego zrozumienia kolejności i składni wyrażeń. W takich przypadkach tabela referencyjna z progami ocen może służyć jako podstawa do tworzenia warunków sprawdzających, czy wynik ucznia mieści się w określonym przedziale. Stosowanie operatorów porównania, takich jak „większy lub równy”, umożliwia precyzyjne przypisywanie kategorii na podstawie wartości liczbowych.

Przy tworzeniu formuł w Power Query ważne jest nie tylko poprawne zastosowanie składni, ale także zrozumienie sposobu działania systemu — na przykład, że w oknie „Dodaj kolumnę warunkową” testy logiczne są wykonywane sekwencyjnie i nie pozwalają na łączenie warunków logicznych w jednej formule. Dlatego manualne pisanie formuł w „Kolumnie niestandardowej” staje się nieodzowne przy bardziej zaawansowanych potrzebach.

Zrozumienie logiki działania operatorów and i or oraz prawidłowa konstrukcja wyrażeń warunkowych pozwala na elastyczne modelowanie złożonych reguł i warunków biznesowych bez konieczności tworzenia wielu osobnych kolumn czy skomplikowanych makr. Poprawne wykorzystanie formuł w Power Query zwiększa efektywność pracy, automatyzuje procesy analityczne i minimalizuje ryzyko błędów.