Jak przekształcać i porządkować dane z podwójnymi nagłówkami oraz wartościami null w Power Query?

Proces przekształcania danych w Power Query, szczególnie gdy mamy do czynienia z podwójnymi nagłówkami lub wartościami null, wymaga precyzyjnego podejścia, które pozwoli uzyskać przejrzystą i użyteczną strukturę danych do dalszej analizy. Na przykładzie danych zawierających kolumny z godzinami i kosztami z podziałem na miesiące, można przedstawić, jak zastępować wartości null, rozdzielać kolumny oraz wykorzystywać polecenia Unpivot i Pivot, by osiągnąć właściwy kształt tabeli.

Przy przetwarzaniu danych pierwszym krokiem jest zamiana wartości null na inne, np. zero lub pusty tekst. W Power Query jest to realizowane za pomocą polecenia Replace Values. Warto przy tym pamiętać, że zastąpienie null pustym ciągiem znaków powoduje zmianę typu danych z liczbowego (Whole Number) na typ ogólny (any), co może wpłynąć na wydajność zapytania, szczególnie w dużych zbiorach danych. W takich przypadkach warto wykonać testy i obserwować, czy ta zmiana nie spowalnia działania narzędzia.

Następnie, by ułatwić dalsze operacje, warto zamienić typ danych kolumny Value z powrotem na Whole Number, co spowoduje przywrócenie pustych wartości do null. Po tej operacji można przystąpić do rozdzielenia kolumny Attribute na dwie osobne kolumny za pomocą polecenia Split Column by Delimiter, z wykorzystaniem spacji jako separatora. Dzięki temu rozdzielimy informację o miesiącu od typu danych (np. godziny, koszty).

Kolejnym etapem jest wykorzystanie kolumny z drugą częścią podzielonego nagłówka (Attribute.2) do zbudowania właściwych nagłówków danych. Za pomocą polecenia Pivot Column tworzymy nowe kolumny z unikalnych wartości w tej kolumnie, wypełniając je danymi z kolumny Value. Ostatecznie uzyskujemy uporządkowaną tabelę, która jest gotowa do dalszej analizy lub eksportu do Excela.

Ważnym aspektem jest także rozpoznanie, że układ z podwójnymi nagłówkami często jest wygodny do wprowadzania danych, ale nie nadaje się bezpośrednio do analizy. W Power Query należy dane takie przekształcić do formy tabeli, gdzie każdy nagłówek znajduje się w osobnej kolumnie, a wartości są odpowiednio przypisane. Ponadto wizualne wyśrodkowanie nagłówków w Excelu nie oznacza, że wartość jest wpisana w każdej komórce – puste komórki traktowane są jako null, co trzeba brać pod uwagę podczas importu i dalszej obróbki danych.

Konwersja zakresu danych do tabeli Excela bez nagłówków wymaga odznaczenia opcji „My table has headers”, aby Excel automatycznie przypisał domyślne nazwy kolumn. Po załadowaniu danych do Power Query dobrze jest usunąć automatycznie dodany krok Changed Type, który może niepoprawnie interpretować mieszane typy danych w kolumnach.

Warto również podkreślić, że mieszanie typów danych (np. tekstu i liczb w jednej kolumnie) powoduje automatyczne przypisanie typu ogólnego przez Power Query, co może wpłynąć na późniejsze transformacje i wydajność. Dlatego konieczna jest świadoma kontrola typów danych i ich konwersja w miarę potrzeb.

Znajomość tych mechanizmów jest kluczowa dla efektywnego wykorzystania Power Query w pracy z bardziej złożonymi zestawami danych, zwłaszcza gdy dane są przechowywane w nieoptymalnej formie, wymagają uporządkowania i dostosowania do analizy.

Jak wykorzystać parametry w Power Query do dynamicznego filtrowania danych?

Po wprowadzeniu wszystkich wartości, z listy Domyślnych Wartości wybierz Europę, a z listy Bieżących Wartości Azję, jak pokazano na rysunku 8.16. Potwierdzenie okna „Zarządzanie Parametrami” poprzez kliknięcie przycisku OK przenosi nas z powrotem do okna „Filtruj Wiersze”, gdzie ikona obok pola wyboru wartości zmienia się z ABC na ikonę przypisaną do parametru. Od tego momentu, zamiast listy wartości kolumny, można wybrać parametry. Na chwilę obecną dostępny jest tylko jeden parametr, jak pokazano na rysunku 8.17.

Po potwierdzeniu filtra przyciskiem OK, w pasku formuły pojawi się nowy krok z kodem M:
= Table.SelectRows(#"Removed Columns", each [Continent] = Continent).
Ten kod przypisuje filtr, który wskazuje, że wartości w kolumnie „Continent” (identyfikowane przez nawiasy kwadratowe) muszą być równe wartości zapisanej w parametrze „Continent”. Ponieważ bieżąca wartość parametru „Continent” została ustawiona na Azję, zapytanie „LargestCities” zwróci jedynie miasta z Azji.

Aby zmienić bieżącą wartość parametru, należy wykonać następujące kroki:

1. Rozwiń panel zapytań po lewej stronie edytora.

2. Kliknij nazwę pożądanego parametru (bieżąca wartość jest wyświetlana w nawiasach obok nazwy). Obszar wyników zapytania wyświetli pole „Bieżąca Wartość”, w którym można zmienić przypisaną wartość, jak pokazano na rysunku 8.18.

Chociaż parametr „Continent” ma zdefiniowaną listę wartości, możliwe jest również przypisanie innej wartości, wpisując ją ręcznie. Poniżej pola „Bieżąca Wartość” znajduje się przycisk „Zarządzaj Parametrami”, który otwiera okno „Zarządzanie Parametrami”. To okno można również otworzyć, klikając lub rozwijając polecenie „Zarządzaj Parametrami” na karcie „Strona Główna”. Dodatkową opcją dostępną po rozwinięciu polecenia „Zarządzaj Parametrami”, poza tworzeniem nowego parametru, jest możliwość edytowania wszystkich parametrów w bieżącym pliku, jak pokazano na rysunku 8.19.

Wybierając tę opcję, otwieramy okno „Edytuj Parametry”, w którym wyświetlana jest lista wszystkich parametrów i ich przypisanych wartości, umożliwiająca ich edytowanie. Ponieważ w tym przykładzie stworzono tylko jeden parametr, w oknie widoczny jest tylko ten parametr. Zmieniamy jego wartość na „Afryka”, jak pokazano na rysunku 8.20, aby zaobserwować zmiany w wynikach filtrowania.

Następnie wartość w polu „Sugestie Wartości” w oknie „Zarządzanie Parametrami” zostanie zmieniona na „Zapytanie”. Zmiana wartości w tym polu na „Zapytanie” nie pozwala jednak na wybór spośród obecnie istniejących zapytań, które można przypisać do parametru, ponieważ parametry wymagają zapytań zwracających listy. Choć zapytanie „Continent” zostało już zaimportowane do edytora i mogłoby być uznane przez użytkownika za listę, Power Query traktuje je jako tabelę, jak wskazuje ikona obok nazwy zapytania na rysunku 8.18. W związku z tym należy przejść do tego zapytania, a następnie na karcie „Transformuj” kliknąć polecenie „Konwertuj na listę”, jak pokazano na rysunku 8.21.

Po tej operacji wybrana kolumna danych zostaje zamieniona na listę, jak wcześniej omawiano w rozdziale, gdy dane były wyodrębniane z tabeli (rys. 8.5). Istotnym szczegółem, na który warto zwrócić uwagę, jest zmiana typu danych zapytania oraz przypisanej ikony, jak pokazano na rysunku 8.22. Teraz to zapytanie może być wybrane jako źródło danych dla parametru „Continent”, jak pokazano na rysunku 8.23. Potwierdzamy tę zmianę klikając OK.

W przeciwieństwie do opcji „Lista wartości”, opcja „Zapytanie” usuwa możliwość wyboru wartości parametru z listy w panelu zapytań (rys. 8.18). Niemniej jednak lista ta pozostaje dostępna w oknie „Edytuj Parametry” (rys. 8.20). Te zmiany stanowią wszystkie modyfikacje zaplanowane dla edytora. Aby zakończyć, klikamy polecenie „Zamknij i Załaduj” na karcie „Strona Główna” (rys. 1.13). Ponieważ edytowano tylko istniejące zapytania i dodano parametr, Power Query nie załaduje nowych danych do programu Excel, ale jedynie zaktualizuje tabelę wyników zapytania „LargestCities”.

Warto również pamiętać, że jeżeli wyniki związanych zapytań nie odświeżają się po zmianie wartości parametru, można wymusić ich odświeżenie, klikając lub rozwijając polecenie „Odśwież Podgląd” na karcie „Strona Główna”. Jest to przydatne, gdy chcemy, aby zmiany były natychmiastowe, a program Excel poprawnie załadował najnowsze dane.

W kontekście parametrów w zapytaniach, warto zaznaczyć, że ich zastosowanie pozwala na elastyczność i dynamiczne filtrowanie danych, co szczególnie przydaje się w sytuacjach, gdy dane są często aktualizowane lub gdy konieczne jest dostosowanie parametrów bez konieczności ręcznej edycji każdego zapytania. Parametry w Power Query mogą być także wykorzystywane do zarządzania ścieżkami plików, co stanowi kluczowy element pracy z wieloma plikami w obrębie jednego folderu.

Jeśli chodzi o edycję zapytań związanych z plikami, które zostały przeniesione do innego folderu, istotnym krokiem jest zaktualizowanie ścieżki pliku w kroku Źródło. Można to zrobić za pomocą parametrów, co znacząco uprości proces dostosowywania zapytań do nowych lokalizacji plików. Zamiast modyfikować każdą ścieżkę ręcznie, wystarczy zaktualizować parametr, co zaoszczędzi czas i minimalizuje ryzyko błędów.

Jak prawidłowo zarządzać ścieżkami plików i prywatnością w Power Query

W Power Query często zachodzi potrzeba dynamicznego odwoływania się do ścieżek plików, zamiast stosowania statycznych wartości w kodzie M. Przykładem jest modyfikacja kroku Source w zapytaniu, gdzie zamiast twardo zakodowanej ścieżki folderu, wykorzystujemy odwołanie do innego zapytania zwracającego właściwą ścieżkę, np.: = Excel.Workbook(File.Contents(tParameters & "LargestCitiesWorld.xlsx"), null, true). Niestety, takie podejście często prowadzi do błędów związanych z dostępem, które wynikają z domyślnych ustawień prywatności w Power Query. Błąd ten objawia się komunikatem uniemożliwiającym połączenie danych pochodzących z różnych źródeł.

Jednym z rozwiązań jest zmiana globalnych ustawień prywatności, gdzie w opcjach zapytań (Query options) należy w zakładce Prywatność (Privacy) wybrać opcję "Zawsze ignoruj ustawienia poziomu prywatności" (Always ignore Privacy Level settings). Po zatwierdzeniu zmian i odświeżeniu podglądu zapytania błąd znika. Ta metoda jest szybka, ale niesie ze sobą ryzyko bezpieczeństwa, gdyż ignoruje potencjalne konflikty danych z różnych źródeł.

Alternatywą, bardziej zgodną z dobrymi praktykami, jest zmiana sposobu zwracania danych przez zapytanie zwracające ścieżkę (tParameters). Zamiast zwracać pojedynczą wartość, zapytanie powinno zwracać tabelę danych, która w kolejnych krokach może być wykorzystana bez naruszania zasad prywatności. W tym celu należy w edytorze Power Query dodać krok odwołujący się do tabeli, a następnie w zapytaniu korzystającym (LargestCities) zastąpić krok Source prostym przypisaniem do zapytania tParameters (np. = tParameters). Taka zmiana eliminuje konflikt i pozwala na prawidłowe połączenie danych.

Kolejny poziom zaawansowania to integracja kodu M w taki sposób, aby jedno zapytanie łączyło dane z dwóch źródeł, np. ścieżki pliku i samego pliku Excela. W tym przypadku, zamiast oddzielnych zapytań Path i LargestCities, kroki odczytujące ścieżkę pliku zostają wklejone do zapytania obsługującego dane o miastach. Ważnym aspektem jest tutaj poprawne zarządzanie nazwami kroków – każdy krok w M musi mieć unikalną nazwę w obrębie zapytania. Pojawienie się dwóch kroków o nazwie Source powoduje konflikt, co generuje błąd kompilacji. Dlatego jeden z tych kroków powinien zostać zakomentowany lub przemianowany. W Power Query komentarze można dodawać za pomocą podwójnych ukośników // dla pojedynczej linii lub /* ... */ dla bloków wielolinijkowych.

Dodatkowo, nazwy kroków zawierające spacje lub znaki specjalne muszą być ujęte w cudzysłowy i poprzedzone znakiem hash, np. #"Changed Type". Znajomość tych reguł jest kluczowa dla poprawnej edycji i utrzymania czytelności kodu M.

Warto również zwrócić uwagę, że funkcje edytora Power Query ułatwiają przeglądanie i edytowanie kodu – kliknięcie na nazwę funkcji podświetla wszystkie jej wystąpienia, co pomaga w nawigacji po skomplikowanych zapytaniach.

Prawidłowe zarządzanie ścieżkami oraz ustawieniami prywatności jest fundamentem pracy z Power Query, zwłaszcza w sytuacjach, gdy konieczne jest łączenie danych z wielu źródeł. Nieprawidłowe ustawienia mogą prowadzić do trudnych do zdiagnozowania błędów i komplikacji, które utrudniają automatyzację i elastyczne zarządzanie danymi.

Niezwykle istotne jest zrozumienie, że każda zmiana w konfiguracji prywatności powinna być przemyślana pod kątem bezpieczeństwa danych i zgodności z polityką firmy. Ignorowanie poziomów prywatności może prowadzić do niezamierzonego udostępniania wrażliwych informacji.

Ponadto, oprócz technicznych aspektów związanych z kodem M, warto rozumieć mechanizmy, jakie stoją za przetwarzaniem danych w Power Query – to pozwala na bardziej świadome projektowanie zapytań oraz efektywniejsze rozwiązywanie pojawiających się problemów. Szczególnie ważne jest zrozumienie, że zapytania Power Query są łańcuchem transformacji, w którym każdy krok bazuje na poprzednim, a błędy w jednym etapie mogą wpływać na całe przetwarzanie.

Zrozumienie zasad budowy i kolejności kroków w M oraz znajomość specyficznych funkcji i ograniczeń języka pozwala na tworzenie bardziej zaawansowanych i elastycznych rozwiązań, które mogą być łatwo modyfikowane i utrzymywane.

Jak dostosować funkcję Transform File w Power Query?

Funkcja Transform File odgrywa kluczową rolę w przetwarzaniu danych w Power Query, umożliwiając ich ekstrakcję i modyfikację z różnych plików w obrębie folderu. Użycie tej funkcji wymaga odpowiednich modyfikacji, które powinny być przeprowadzane na podstawie zapytania Transform Sample File. Zmiany dodane w tym zapytaniu natychmiastowo wpływają na działanie funkcji Transform File, co może być łatwo zweryfikowane poprzez kliknięcie w przycisk Advanced Editor na karcie Home.

Funkcja Transform File w praktyce jest wykorzystywana w zapytaniu Sales do wydobycia i modyfikacji danych z każdego pliku znajdującego się w danym folderze. Ważne jest, aby funkcja została poprawnie dostosowana, co może być osiągnięte dzięki powiązaniu z zapytaniem Transform Sample File. Najprostszym sposobem na dokonanie odpowiednich zmian w funkcji jest modyfikacja kroków bezpośrednio w zapytaniu Transform Sample File. Dalsze kroki opiszemy na przykładzie procesu modyfikacji zapytania powiązanego z funkcją.

Pierwszym krokiem jest zrozumienie kodu M w pasku formuły, który znajduje się w pierwszym (i jedynym widocznym) kroku funkcji Transform File. Kod ten wygląda następująco:

m
Copy code
= (Parameter1) => let 
  Source = Excel.Workbook(Parameter1, null, true), 
  Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], 
  #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]) 
in #"Promoted Headers"

Pierwsza linia zawiera deklarację funkcji, której argumentem jest nazwa pliku (w tym przypadku Parameter1). Następnie mamy konstrukcję let, która rozpoczyna proces definiowania zmiennych i operacji na danych, podobnie jak miało to miejsce w zapytaniu Transform Sample File. Ważnym krokiem jest zrozumienie, że każda modyfikacja w zapytaniu Transform Sample File wpłynie na funkcję Transform File.

Zalecanym pierwszym krokiem w dostosowywaniu funkcji jest usunięcie kroku "Promoted Headers". Dzięki temu uzyskamy surowe dane, które zostały zaimportowane z arkusza, co daje pełną kontrolę nad dalszymi modyfikacjami.

Po tym, należy przejść do dodania nowej kolumny warunkowej, którą tworzymy za pomocą opcji "Conditional Column" dostępnej w zakładce "Add Column". W tym przypadku celem jest dodanie kolumny "Salesperson", która będzie zawierała nazwisko sprzedawcy, gdy w kolumnie "Column1" pojawi się odpowiednia wartość. Jeśli warunek nie zostanie spełniony, w komórce tej kolumny pojawi się wartość null.

Dalszym krokiem jest wyodrębnienie imienia sprzedawcy za pomocą funkcji Text.AfterDelimiter. Chociaż użycie tej funkcji przez interfejs graficzny Power Query spowoduje zastąpienie wartości null pustymi komórkami, wprowadzając odpowiednią modyfikację w kodzie M, zachowamy wartość null, co jest kluczowe dla dalszych transformacji.

Po dodaniu funkcji do kodu M, kolumna "Salesperson" będzie zawierała odpowiednie imiona sprzedawców, a błędne dane zostaną usunięte za pomocą polecenia "Remove Errors" dostępnego w interfejsie. Kolejnym krokiem jest skopiowanie nazwisk sprzedawców do wszystkich komórek w kolumnie, co można wykonać za pomocą polecenia "Fill Down".

Kolejną istotną modyfikacją jest nadanie odpowiednich nazw kolumnom, co można wykonać manualnie na podstawie danych w drugiej linii arkusza. Należy pamiętać, że funkcja "Promote Headers" nie sprawdzi się w tym przypadku, ponieważ ostatnia kolumna już ma odpowiednią nazwę, a druga linia zawiera imię sprzedawcy, a nie nazwę kolumny.

W następnych krokach usuwamy dwie pierwsze linie danych, co można wykonać przy pomocy opcji "Remove Top Rows". Następnie, aby upewnić się, że transformacje w zapytaniu wpływają na wyniki zapytań zależnych, warto usunąć krok "Changed Type" w zapytaniu Sales, aby dane stały się bardziej przejrzyste.

Po zakończeniu wszystkich wymaganych transformacji w zapytaniu Transform Sample File, możemy przejść do dalszych operacji w zapytaniu Sales, które polegają na łączeniu danych z różnych plików Excel. Ostatnim krokiem jest dokonanie modyfikacji nazw kolumn, co można zrobić za pomocą polecenia "Rename Columns", które zmienia nazwę kolumny "Name" na "Country".

Warto także pamiętać, że transformacje w Power Query mogą być różne w zależności od sposobu ich stosowania. Modyfikacje wprowadzone poprzez interfejs graficzny różnią się od tych wykonywanych bezpośrednio w kodzie M, zwłaszcza jeśli chodzi o sposób obsługi wartości null. Korzystanie z kodu M daje pełną kontrolę nad tymi wartościami, co jest szczególnie ważne w bardziej złożonych scenariuszach przetwarzania danych.