Jak wykorzystywać zaawansowane filtry i filtrowanie w Power Query do pracy z danymi ukrytymi w Excelu?

W trakcie pracy z danymi w Excelu, zwłaszcza w kontekście analizy za pomocą Power Query, często napotykamy na ukryte obiekty, które mogą stanowić istotną część przetwarzanych danych. Warto znać metody radzenia sobie z tymi obiektami, by nie przeoczyć żadnej istotnej informacji. Przykład tego typu obiektów obejmuje różne ukryte arkusze, zakresy nazwane, jak również obiekty związane z filtrowaniem zaawansowanym. W tym kontekście warto zrozumieć, jak Power Query pomaga w efektywnym zarządzaniu danymi, które nie są bezpośrednio widoczne w oknie nawigatora.

Pierwszym krokiem jest zrozumienie, jakie rodzaje obiektów mogą zostać ukryte w Excelu. Przykładem jest ukryty arkusz, który może być ukryty w Excelu poprzez standardową funkcję „Hidden”. Drugim typem ukrytego arkusza jest „VeryHidden”, który może być ustawiony za pomocą VBA. Tego typu arkusze nie są dostępne w standardowym interfejsie Excel, a dostęp do nich możliwy jest wyłącznie przez VBA. Kolejnym przykładem jest zakres nazwany, taki jak _xlmn._FilterDatabase, który jest przypisany do zakresu danych filtrowanych za pomocą filtrów zaawansowanych. Filtry zaawansowane również tworzą inne zakresy nazwane, takie jak _xlmn.Criteria czy _xlmn.Extract, ale te obiekty są widoczne w Excelu. Ukryte obiekty, takie jak _xlmn._FilterDatabase, w tym przykładzie nie są istotne, dlatego powinny zostać odfiltrowane.

Proces ten przypomina filtrowanie tabel w Excelu, gdzie użytkownik może kliknąć ikonę w nagłówku kolumny, aby deselectować niepotrzebne elementy. Gdy użytkownik zatwierdzi wybór, klikając OK, Power Query doda krok „Filtered Rows”, który ograniczy liczbę wierszy do 14. Po tym działaniu pozostaną jedynie obiekty widoczne w oknie nawigatora. Warto zwrócić uwagę na cztery dodatkowe kolumny, które pojawią się w danych:

1. Kolumna „Kind” zawiera tekstowe wartości określające typ obiektu, jak Arkusz, Tabela czy Nazwa Zdefiniowana.

2. Kolumna „Name” przechowuje nazwę obiektu (w formacie tekstowym).

3. Kolumna „Item” zawiera nazwę obiektu, ale różni się od kolumny „Name” w przypadku nazwanych zakresów przypisanych do konkretnego arkusza. Na przykład, zakresy związane z filtrami zaawansowanymi i obszarem druku (_xlnm.Print_Area) zawierają nazwę arkusza, a potem wykrzyknik.

4. Kolumna „Data” zawiera dane w formie tabeli dla każdego obiektu. Aby wyświetlić dane, wystarczy kliknąć na wybraną komórkę w tej kolumnie, a dane pojawią się jako zmienna Tabela.

Należy zwrócić uwagę, że typ danych „Tabela”, jak pokazano w kolumnie „Data”, nie może być przypisany ręcznie. Jest to typ danych przypisany automatycznie na podstawie struktury importowanych danych. Ważnym aspektem pracy z Power Query jest również zrozumienie, że importując dane z pliku Excel, każda tabela będzie również pojawiać się jako obiekt Arkusza. Jeśli arkusz zawiera tylko tabelę, różnica między tymi obiektami jest tylko w nagłówkach. Power Query automatycznie rozpozna nagłówki w tabelach, natomiast w arkuszach przypisze domyślne nazwy kolumn.

Po dokładnej analizie danych, jak zmienne tabeli w kolumnie „Data”, zauważamy, że dane w tabelach najczęściej odpowiadają danym z arkuszy. Wyjątkiem jest Arkusz7, co oznacza, że jednoczesne importowanie tabel i arkuszy staje się zbędne. W takim przypadku konieczne jest podjęcie decyzji o preferowanym źródle danych. Choć zakresy nazwane nie są w tym przypadku istotne, wybór między tabelami a arkuszami może nie być oczywisty. Tabele są zazwyczaj lepszym wyborem, ponieważ muszą przestrzegać określonego formatu. Jednak w tym przykładzie, jedna tabela ma inną nazwę niż pozostałe, co może sugerować, że nie powinna być importowana. To założenie jest jednak błędne, ponieważ należy zaimportować wszystkie dane, które nie mają nazw domyślnych (czyli takich, które nie zaczynają się od słowa „Sheet”).

Aby osiągnąć ten cel, należy zastosować filtr w kolumnie „Kind”, podobnie jak w przypadku filtrowania w kolumnie „Hidden”, ale tym razem nie zaznaczamy opcji „DefinedName” i „Table”. Zatwierdzenie filtra i dodanie go do istniejącego kroku „Filtered Rows” jako dodatkowego warunku spowoduje, że wynikowy kod będzie wyglądał następująco:

pgsql
Copy code
= Table.SelectRows(Source, each ([Hidden] = false) and ([Kind] = "Sheet"))

Power Query stara się pisać najkrótszy możliwy kod, a w tym przypadku wystarczy jedno porównanie, zamiast dwóch. Następnie filtry należy zastosować w sposób podobny do filtrów tekstowych w Excelu, klikając na nagłówek kolumny „Item” i wybierając opcję „Does Not Begin With”, aby wykluczyć wszystkie obiekty, które zaczynają się od słowa „Sheet”.

Po zastosowaniu filtra tekstowego Power Query doda nowy krok „Filtered Rows1”, a ikona filtra pojawi się w nagłówku kolumny. Warto również zauważyć, że po usunięciu innych kolumn, pozostaną tylko te, które zostały wybrane do analizy, jak na przykład kolumny „Name” i „Data”. Dzięki temu możemy przejść do etapu rozszerzania danych przechowywanych w kolumnie „Data”. Zanim to zrobimy, należy upewnić się, które dodatkowe informacje będą potrzebne, takie jak nazwy krajów, gdzie sprzedawano produkty. W przypadku, gdy chcemy usunąć trzy kolumny (Item, Kind, Hidden), pozostawiając jedynie „Name” i „Data”, należy wybrać te kolumny i kliknąć opcję „Remove Other Columns”.

WaŜnym jest również, aby pamiętać o kolejności, w jakiej wybierane są kolumny przed użyciem opcji „Remove Other Columns”, ponieważ ma to wpływ na układ pozostałych kolumn w wynikowych danych.

Jak działa indeksowanie i nawigacja w Power Query na przykładzie Excel: numeracja, drill down i klucze główne

Numeracja w Power Query zaczyna się od zera, co może być mylące dla użytkowników przyzwyczajonych do Excela, gdzie wiersze i kolumny numerowane są od jedynki. Na przykładzie porównania dwóch pierwszych kolumn w tabeli widać wyraźnie różnicę między standardową numeracją Excela a indeksacją Power Query. W praktyce oznacza to, że pierwszy wiersz w Power Query ma indeks 0, drugi 1 itd. Ta różnica ma kluczowe znaczenie podczas odwoływania się do danych na poziomie wierszy i kolumn.

W przykładzie podanym w tekście pokazano także różne obliczenia, które na pierwszy rzut oka wydają się opierać na numerach wierszy i kolumn, lecz w rzeczywistości są oparte na pozycjach elementów znalezionych przez funkcje Excela. W kontekście Power Query odpowiada to wyszukiwaniu danych po kluczu. Dzięki temu, nawet jeśli dane zostaną posortowane lub zmieni się kolejność kolumn, wyniki wyliczeń pozostaną niezmienne, ponieważ odwołania opierają się na wartościach kluczy, a nie na pozycjach.

Proces „drill down” pozwala wyodrębnić pojedynczą wartość z tabeli w Power Query. Po zaimportowaniu danych z Excela do Power Query można kliknąć na wybraną komórkę i wybrać „Drill Down”. W efekcie edytor pokaże tylko tę wartość, a w pasku formuły pojawi się kod w języku M. Ten kod ma strukturę:
= Source{wiersz}[Kolumna]
gdzie Source to tabela źródłowa, {wiersz} to indeks wiersza zaczynający się od zera, a [Kolumna] to nazwa kolumny. Trzeba pamiętać, że wiersz jest wskazywany na podstawie pozycji, więc zmiana sortowania może spowodować odwołanie do innej wartości. Natomiast kolumna jest wywoływana po nazwie, więc jej kolejność nie ma wpływu na wynik.

Funkcja Excel.CurrentWorkbook() zwraca listę tabel i nazwanych zakresów w skoroszycie, co jest wygodne do dalszej pracy w Power Query. Kluczem do stabilnej nawigacji w tabeli jest stosowanie kluczy głównych (primary keys). Power Query może automatycznie uznać kolumnę „Name” jako klucz w pewnych krokach, jednak w domyślnym imporcie danych z Excela nie przypisuje automatycznie kluczy do tabeli. W efekcie, domyślny „Drill Down” działa na podstawie indeksu wiersza.

Aby nadać tabeli klucz ręcznie, można użyć funkcji Table.AddKey, na przykład:
= Table.AddKey(Source, {"ID"}, true)
co oznacza, że kolumna „ID” jest teraz kluczem głównym tabeli. W takim przypadku „Drill Down” będzie odwoływać się do wartości w kolumnie ID, a nie do pozycji wiersza, co zwiększa niezawodność i odporność zapytań na zmiany kolejności danych.

Oprócz pojedynczych komórek, Power Query umożliwia też wydobycie całych kolumn za pomocą „Drill Down”. Po kliknięciu nagłówka kolumny i wybraniu tej opcji kolumna zostaje przekształcona w listę wartości, a edytor przechodzi w tryb „List Tools”. Tę listę można przekształcić z powrotem w tabelę przy pomocy funkcji „To Table” lub poprzez modyfikację formuły M, np. = Custom1[[Feb]] zwraca pojedynczą kolumnę jako tabelę z jedną kolumną „Feb”. Można też wybrać wiele kolumn jednocześnie, wymieniając ich nazwy w podwójnych nawiasach, co pozwala na precyzyjne kontrolowanie zwracanego zbioru danych i ich kolejności.

Warto mieć świadomość, że indeksowanie od zera i mechanizmy kluczy są fundamentem pracy z Power Query i decydują o stabilności oraz przewidywalności wyników. Znajomość tych zasad umożliwia efektywną manipulację danymi bez obaw o błędy związane ze zmianą układu tabeli lub kolejności wierszy. Wykorzystanie kluczy głównych w tabelach zapobiega pomyłkom wynikającym z odwoływania się do pozycji i pozwala na tworzenie bardziej dynamicznych i odpornych na zmiany zapytań. Dodatkowo, rozumienie różnicy między strukturą tabeli a listą w Power Query oraz umiejętność przełączania się między nimi daje większą elastyczność podczas przetwarzania danych.

Jak dynamicznie usuwać zmienną liczbę pierwszych wierszy podczas łączenia plików w Power Query?

Łączenie danych z wielu plików o zróżnicowanej strukturze nagłówków wymaga elastycznego podejścia do identyfikacji właściwego wiersza z nagłówkami. Często pliki źródłowe zawierają na początku dodatkowe, opisowe dane, których liczba może się różnić w poszczególnych plikach, co uniemożliwia ręczne wskazanie numeru wiersza nagłówka. Kluczowym wyzwaniem jest więc dynamiczne określenie wiersza, który będzie pełnił rolę nagłówka tabeli.

Pierwszym krokiem jest załadowanie wszystkich plików z wybranego folderu do Power Query. Po wskazaniu folderu i zaimportowaniu plików pojawia się zestaw danych, gdzie większość kolumn ma nazwy domyślne, a prawidłowe nagłówki znajdują się dopiero w jednej z kolejnych linii. Przed przystąpieniem do dalszej obróbki konieczne jest usunięcie zbędnych kolumn, takich jak „Source.Name”, które wynikają z mechanizmu łączenia plików, a które nie będą używane. Usunięcie tych kolumn wymaga jednak ostrożności, ponieważ kolejne kroki zapytań mogą od nich zależeć i usunięcie może powodować błędy. Warto krok po kroku usuwać odwołania do niepotrzebnych elementów, monitorując pojawiające się komunikaty o błędach i je eliminując.

Kolejnym, fundamentalnym elementem jest odnalezienie indeksu wiersza, który zawiera właściwe nagłówki. Skuteczną techniką jest dodanie kolumny indeksowej rozpoczynającej się od zera, co umożliwia precyzyjne odniesienie się do numeru wiersza. Następnie tworzy się kolumnę warunkową, w której sprawdzamy, czy wartość w pierwszej kolumnie jest równa określonemu tekstowi – na przykład „Date”. Wiersz, gdzie warunek jest spełniony, otrzymuje wartość indeksu, a pozostałe pozostają puste. Na tej podstawie, wykorzystując statystykę minimalnej wartości w tej kolumnie, ustalamy dokładną pozycję pierwszego wiersza nagłówka w danym pliku.

Mając tę wartość, można zastosować funkcję usuwającą określoną liczbę pierwszych wierszy tabeli. Najczęściej używaną funkcją jest Table.Skip, która pomija wskazaną liczbę wierszy od góry. W tym przypadku parametr określający ilość pomijanych wierszy jest dynamicznie podstawiany do formuły, bazując na wcześniej obliczonej wartości minimalnego indeksu. W ten sposób dla każdego pliku proces identyfikacji i usuwania nieistotnych wierszy przebiega automatycznie, co pozwala na późniejsze bezproblemowe połączenie wszystkich danych pod wspólnym nagłówkiem.

Cała procedura ilustruje zaawansowane możliwości Power Query w kontekście pracy z heterogenicznymi źródłami danych, gdzie struktura plików nie jest jednolita, a jednocześnie wymaga spójnego przygotowania do analizy. Takie podejście jest szczególnie użyteczne w środowiskach biznesowych, gdzie dane pochodzą z różnych systemów, raportów czy lokalizacji, a ich ręczne porządkowanie byłoby pracochłonne i podatne na błędy.

Ważne jest, aby użytkownik rozumiał, że każda zmiana w kolejności lub zawartości kroków w zapytaniu może wpływać na końcowy rezultat, dlatego modyfikacje należy przeprowadzać świadomie i etapowo, śledząc pojawiające się błędy. Dodatkowo, choć Power Query umożliwia wizualną edycję, często przydatna jest znajomość języka M, pozwalającego na precyzyjne dostosowanie transformacji danych.

Zrozumienie sposobu, w jaki Power Query identyfikuje i manipuluje strukturą tabeli, jest kluczowe, aby efektywnie zarządzać danymi pochodzącymi z niejednorodnych źródeł. Bez tej wiedzy istnieje ryzyko utraty informacji lub błędnej interpretacji danych wynikowych, co może prowadzić do błędnych wniosków i decyzji biznesowych.