Jak Power Query przetwarza i łączy dane z wielu plików

Power Query, narzędzie do przetwarzania danych w Excelu, oferuje szeroki wachlarz funkcji, które umożliwiają użytkownikom wygodne łączenie danych z wielu źródeł, w tym także z folderów zawierających wiele plików. Proces ten, choć złożony, jest intuicyjny dzięki automatycznym funkcjom wykrywającym kodowanie plików, delimitery, a także odpowiednie formatowanie danych. Istnieje jednak kilka ważnych kroków, które pozwalają na pełne wykorzystanie jego możliwości.

Po rozpoczęciu procesu, Power Query generuje wiele zapytań i kroków, jednak pierwszym zadaniem jest otwarcie okna „Combine Files”. To okno przypomina okno importu pliku CSV, ale dodatkowo pozwala na zdefiniowanie, który z plików będzie używany jako plik referencyjny do transformacji wszystkich wybranych plików. W przypadku, gdy użytkownik zdecyduje się na połączenie plików z folderu, Power Query wyświetli podgląd danych, a także automatycznie wykryje odpowiednie parametry (kodowanie i delimiter), co pozwala na szybką kontynuację procesu.

Po wybraniu plików i zatwierdzeniu operacji, użytkownik zostaje przeniesiony do widoku zapytań. Tutaj można zobaczyć kilka różnych zapytań, zgrupowanych w folderach. Wśród tych zapytań znajduje się: zapytanie „Sample File”, które zawiera pierwszy plik z folderu używany do transformacji danych, oraz zapytanie „Transform File”, które zawiera funkcję odpowiedzialną za przetwarzanie każdego z plików. Kolejne zapytania są wykorzystywane do zdefiniowania parametrycznych plików oraz zapisanych transformacji.

Następnie Power Query przechodzi do utworzenia serii kroków w głównym zapytaniu, które odpowiada za łączenie plików. Jednym z kluczowych kroków jest „Filtered Hidden Files”, który usuwa ukryte pliki, stosując filtr na podstawie wartości w kolumnie Hidden. Kolejne etapy obejmują aplikację funkcji transformacyjnej na każdy plik oraz usunięcie zbędnych kolumn, pozostawiając jedynie te, które zawierają tabele. Dalsze kroki to rozpakowywanie danych z tabel oraz przypisanie odpowiednich typów danych do kolumn.

Po przekształceniu danych, można zauważyć, że brakuje jednej ważnej informacji – nazwy kraju, która była zawarta w kolumnie „Name” w pliku, ale została usunięta w jednym z wcześniejszych kroków. Istnieją dwa sposoby na jej przywrócenie: można kliknąć ikonę koła zębatego obok kroku „Removed Other Columns” i w oknie wyboru kolumn zaznaczyć kolumnę „Name”, lub, co bardziej zaawansowane, ręcznie edytować kod M, aby dodać tę kolumnę do odpowiedniego etapu.

Po przywróceniu brakującej kolumny, można kontynuować dalsze przetwarzanie danych, takie jak obliczenie przychodu na podstawie danych sprzedażowych, a następnie załadować przetworzone dane do tabeli Excel.

W przypadku korzystania z danych z bazy Access, Power Query pozwala na zaimportowanie danych z tabeli związaną relacjami między sobą. W przypadku bazy danych Access ważnym aspektem jest zrozumienie, jak relacje między tabelami wpływają na wyniki zapytań. W przykładzie dotyczącym zarządzania miastami i menedżerami, mamy do czynienia z tabelami „Cities”, „CityManager” i „Managers”, które są ze sobą powiązane za pomocą tabeli pośredniczącej, co pozwala na zachowanie jednego do wielu relacji.

W takich przypadkach, Power Query umożliwia zaimportowanie danych za pomocą opcji „From Microsoft Access Database” i przetworzenie ich zgodnie z relacjami, co pozwala na uzyskanie spójnych wyników.

Po zrozumieniu tych podstawowych zasad, warto pamiętać, że Power Query daje ogromne możliwości, ale wymaga nie tylko podstawowej znajomości jego funkcji, ale także umiejętności precyzyjnego dopasowywania kroków przetwarzania do danych. Kluczowe jest, aby zawsze sprawdzać, jakie transformacje są stosowane do każdego pliku oraz jak zmieniają się dane w procesie, zwłaszcza przy pracy z wieloma plikami lub danymi z relacyjnymi bazami danych.

Zrozumienie zależności między krokami i zapytaniami oraz świadomość ich wzajemnego wpływu na dane jest kluczem do efektywnego wykorzystania Power Query.

Jak Power Query radzi sobie z łączeniem i agregacją danych w złożonych zapytaniach?

Power Query automatycznie nadaje kolumnom tworzone przy użyciu domyślnych obliczeń nazwy, które często nie są adekwatne ani użyteczne, np. "Multiplication" przy mnożeniu. W takich przypadkach należy te nazwy zmieniać – zamiast dodawać kolejne kroki w zapytaniu, co może komplikować dłuższe transformacje, lepiej modyfikować bezpośrednio formułę w pasku formuły. Przykładowo, nazwę "Multiplication" można zastąpić nazwą bardziej opisową, np. "Income". Dzięki temu cały proces pozostaje bardziej czytelny i efektywny.

Należy zwrócić uwagę, że jeśli dla jednej kolumny zostanie wybrana więcej niż jedna agregacja, Power Query pokaże zbiorcze etykiety zamiast pojedynczej nazwy. Domyślne nazwy nowych kolumn zawierają informację o typie agregacji i nazwę kolumny, co ułatwia orientację w wynikach. Zmiana tych nazw jest możliwa i często wskazana dla lepszej czytelności.

Kolejny ważny aspekt to różnice między typami połączeń: Left Outer Join, Right Outer Join oraz Full Outer Join. W praktyce right join jest rzadziej stosowany, ponieważ jest to w zasadzie left join z zamienioną kolejnością tabel. W przypadku right join w wynikowej tabeli pojawiają się wszystkie wiersze z prawej tabeli, nawet jeśli nie mają dopasowania w lewej – dla tych wierszy wartości z lewej tabeli będą puste (null). Full Outer Join natomiast łączy wszystkie wiersze z obu tabel, pokazując null tam, gdzie brak jest dopasowania po którejkolwiek stronie.

Po wykonaniu łączenia i wybraniu odpowiedniego typu join, należy zwrócić uwagę na sposób rozwijania kolumn z połączonych tabel. Możliwe jest wybranie konkretnych kolumn do ekspansji i decydowanie o zachowaniu prefiksów w nazwach kolumn – zazwyczaj dla przejrzystości warto je wyłączyć. Wyniki łączenia pokazują, jak różne typy join wpływają na ostateczny zestaw danych oraz jak prezentowane są dane z tabel nieposiadających odpowiedników w drugiej tabeli.

Ważne jest, aby rozumieć konsekwencje wyboru poszczególnych metod łączenia i agregacji, gdyż wpływają one na strukturę danych, ich kompletność oraz możliwość dalszej analizy. Złożone transformacje w Power Query powinny być przemyślane pod kątem efektywności działania i czytelności zapytania, zwłaszcza gdy pracujemy z dużymi zbiorami danych. Umiejętność pracy z różnymi rodzajami joinów oraz świadome stosowanie agregacji pozwala na tworzenie precyzyjnych zestawień i raportów, które nie powielają informacji i jednocześnie prezentują wszystkie niezbędne dane.

Jak efektywnie przekształcać dane w Power Query? Promowanie nagłówków, wypełnianie, odwracanie i dodawanie kolumn

Proces przygotowania danych do analizy w Power Query wymaga znajomości kilku kluczowych operacji transformacyjnych, które umożliwiają uporządkowanie i standaryzację tabel przed dalszą analizą. Jednym z pierwszych kroków jest promowanie pierwszego wiersza na nagłówki kolumn — dzięki temu Power Query rozpoznaje odpowiednie nazwy kolumn, co znacznie ułatwia dalszą pracę z danymi. Po tej operacji należy zweryfikować, czy nagłówki zostały poprawnie przypisane, aby uniknąć błędów w kolejnych krokach.

Kolejnym istotnym etapem jest wypełnianie pustych wartości w kolumnach, które w oryginalnych danych mogą pojawiać się tylko w pierwszym wierszu danej sekcji, a w kolejnych wierszach pozostają puste (null). Dzięki funkcji Fill Down można rozpropagować wartości w dół kolumny, tak aby każdy wiersz posiadał odpowiednią wartość, co jest szczególnie ważne dla kolumn opisujących np. nazwiska sprzedawców czy inne dane kategoryczne. Warto zaznaczyć, że ta funkcja zastępuje tylko wartości null, natomiast puste ciągi tekstowe pozostają niezmienione.

Po przekształceniu tabeli można zmienić nazwy kolumn powstałych po unpivot, na przykład na „Month” i „Revenue”, co zwiększa czytelność danych i ich zgodność z późniejszą analizą.

Dodanie nowych, stałych kolumn, takich jak „Country”, jest kolejnym elementem przygotowania danych. W Power Query umożliwia to polecenie Custom Column, gdzie można wprowadzić stałą wartość tekstową lub formułę, wzbogacając tym samym zestaw danych o dodatkowe atrybuty kontekstowe.

Kiedy dane posiadają skomplikowane, złożone nagłówki, które łączą informacje o miesiącu i rodzaju danych (np. godziny pracy i koszty), często konieczne jest rozdzielenie ich na oddzielne kolumny, aby umożliwić dalszą analizę. W takim przypadku po imporcie danych do Power Query i zastąpieniu pustych komórek wartościami null można zastosować operację „Unpivot Other Columns” na kolumnach bazowych. Efektem będzie tabela z kolumną atrybutów i wartości, w której brakujące dane (null) początkowo mogą być pomijane. Aby zachować wiersze z null w transformacji, konieczne jest odpowiednie zarządzanie kolejnością kroków w zapytaniu, co pozwoli uwzględnić te dane w końcowym zestawie.

Kluczowe jest rozumienie, że operacje takie jak wypełnianie wartości, odwracanie kolumn oraz dodawanie nowych stałych kolumn są niezbędnymi narzędziami do konwersji surowych, często nieuporządkowanych danych do formatu nadającego się do efektywnej analizy i raportowania. Znajomość subtelności działania tych funkcji, szczególnie w kontekście pustych komórek, zmieniających się struktur tabel czy potencjalnej rozbudowy danych w przyszłości, umożliwia tworzenie elastycznych, odpornych na błędy zapytań.

Jak radzić sobie z błędami w Power Query przy importowaniu danych z PDF

Proces pracy z Power Query w Excelu często wiąże się z wieloma wyzwaniami, szczególnie gdy dane pochodzić będą z różnych źródeł, takich jak pliki PDF. W niniejszym rozdziale opisano szczegółowo przypadek, w którym błąd pojawia się w jednym z kroków zapytań, spowodowanym zmianą nazwy kolumny, a także wyjaśniono proces tworzenia funkcji na podstawie zapytania z parametrami oraz przetwarzanie danych z pliku PDF.

m
Copy code
= Table.SelectColumns(#"Renamed Columns1", {"Country", "Transform File"})

Dzięki temu można uniknąć błędów wynikających z niewłaściwego odniesienia do nieistniejącej już kolumny.

Po wykonaniu tego kroku, kolejnym etapem jest manipulacja danymi w kolumnie "Country". W tym celu należy wybrać tę kolumnę, a następnie na karcie "Transform" rozwinąć polecenie "Extract" i wybrać opcję "Text Before Delimiter". W oknie "Text Before Delimiter" należy wpisać kropkę jako delimiter, a następnie potwierdzić wybór. Ostateczny rezultat powinien wyglądać zgodnie z oczekiwaniami, a dane będą odpowiednio przekształcone, jak pokazano na ilustracji 9.14.

W przypadku pracy z funkcjami opartymi na parametryzowanych zapytaniach, proces zaczyna się od importu danych z pliku Excel zawierającego pełne ścieżki dostępu do plików PDF. Na przykład, plik Crypto_Archive.xlsx zawiera w komórce B1 formułę, która umożliwia pobranie pełnej ścieżki dostępu do pliku, w którym się znajduje. Te pełne ścieżki będą następnie wykorzystywane do konsolidacji danych pochodzących z plików PDF. Tego rodzaju podejście jest szczególnie przydatne, gdy dane muszą zostać pobrane i przekształcone z wielu podobnych plików.

W tym przypadku celem jest stworzenie funkcji, która będzie wykorzystywała parametryzowane zapytanie, umożliwiające obróbkę danych z wielu podobnych źródeł. Proces zaczyna się od zaimportowania pojedynczego pliku PDF (np. Crypto_Archive_20250330.pdf) i przygotowania jego zawartości do dalszych transformacji. Aby zaimportować plik PDF do Power Query, użytkownik powinien wykonać następujące kroki:

1. Wybieramy polecenie "Get Data" w karcie "Data", rozwijamy opcję "From File", a następnie wybieramy "From PDF".

2. Wyszukujemy i wybieramy interesujący nas plik.

3. W oknie "Navigator" wybieramy zawartość, którą chcemy zaimportować do Power Query. Ważne jest, aby pamiętać, że jedna tabela w pliku PDF nie może rozciągać się na wiele stron, dlatego w oknie Navigator tabela będzie oznaczona numerem strony, na której się znajduje.

Po załadowaniu danych, w Power Query zostaną utworzone cztery domyślne kroki:
• Krok Źródło, który wskazuje na źródło danych.
• Krok Nawigacja, który wybiera odpowiednią tabelę lub stronę.
• Krok Promowanie Nagłówków, który promuje pierwszy wiersz do roli nagłówków.
• Krok Zmieniony Typ, który automatycznie przypisuje typy danych do każdej kolumny.

Jednak pojawić się mogą problemy związane z typami danych, szczególnie w sytuacji, gdy kolumny zawierają dane liczbowe, a regionalne ustawienia pliku są niezgodne z danymi zawartymi w pliku. Na przykład w przypadku pliku Crypto_Archive_20250330.pdf może wystąpić problem z przypisaniem odpowiednich typów danych do kolumn procentowych (% 1h, % 24h, % 7d). Warto zauważyć, że Power Query może źle zinterpretować dane, jeśli używa ustawień regionalnych, w których przecinek jest używany jako separator tysięcy, a w pliku PDF przecinek jest używany jako separator dziesiętny.

Aby naprawić takie problemy, konieczne będzie zastosowanie kilku kroków transformacji:

1. Zmiana typu danych kolumny % 24h na tekst.

2. Zastosowanie polecenia "Zamień wartości" i usunięcie symbolu mniejszości (<) z odpowiednich komórek.

3. Zmiana typu danych dla tych kolumn na właściwe, uwzględniające ustawienia regionalne, np. ustawienia regionalne Polski, gdzie przecinek jest separatorem dziesiętnym.

Po tych korektach dane będą prawidłowo przetworzone, a Power Query będzie mogło je załadować do arkusza w odpowiednim formacie.

Istotne jest, aby przy pracy z danymi w Power Query z plików PDF zwracać szczególną uwagę na ustawienia regionalne, ponieważ nieprawidłowa interpretacja danych liczbowych może prowadzić do poważnych błędów w dalszej analizie. Ponadto, przy tworzeniu funkcji parametryzowanych warto uwzględnić możliwość wystąpienia błędów, np. braku plików w określonych lokalizacjach, oraz odpowiednio zaplanować mechanizmy obsługi błędów w celu ich poprawnej obsługi.