- Power Query umożliwia konsolidację wielu plików z folderu lokalnego lub w chmurze, pod warunkiem, że współdzielą ten sam schemat danych.
- Użycie pliku przykładowego ułatwia przeprowadzanie zbiorczych przekształceń, takich jak usuwanie górnych wierszy lub zmiana nagłówków.
- Występują ograniczenia, gdy kolumny nie są dokładnie takie same, co wymaga ręcznego czyszczenia lub użycia skryptów VBA w złożonych przypadkach.
Wyobraź sobie, że masz mnóstwo miesięcznych lub tygodniowych raportów rozproszonych po folderach i zamiast tracić poranek na gorączkowe kopiowanie i wklejanie danych, możesz połączyć wszystko w jednym arkuszu kalkulacyjnym Excela za pomocą zaledwie kilku kliknięć. Właśnie tutaj pojawia się Power Query – narzędzie, które jest praktycznie prawą ręką każdego, kto pracuje z danymi, umożliwiając bezproblemową konsolidację plików o tej samej strukturze.
Najfajniejszą cechą tego systemu jest to, że nie ogranicza Cię on do lokalnego folderu na komputerze; możesz pobierać dane z witryn SharePoint, Azure Data Lake lub obiektów blob Azure . Kluczem do płynnego działania tego systemu jest to, aby pliki miały ten sam schemat, co oznacza, że kolumny są zgodne, chociaż Power Query jest na tyle inteligentny, że pomaga oczyścić dane, jeśli zawierają one szum lub niepotrzebne wiersze.
Proces łączenia krok po kroku
Po połączeniu się ze źródłem danych, pierwszym wyświetlonym widokiem jest widok systemu plików. W tym przypadku najważniejszą kolumną jest „Zawartość”, która zawiera binarną reprezentację każdego dokumentu. Kliknięcie jednej z tych wartości binarnych spowoduje próbę określenia, czy jest to plik CSV, JSON, czy Excel, aby go otworzyć. Aby połączyć cały plik wsadowy, masz dwie możliwości: kliknij przycisk „Scal” bezpośrednio lub, jeśli jesteś bardziej skrupulatny, wybierz „Przekształć dane”, aby przefiltrować listę plików (na przykład zignorować określone foldery) przed kontynuowaniem scalania.
Po otwarciu okna dialogowego scalania, Power Query analizuje strukturę na podstawie przykładowego pliku . Domyślnie wybiera pierwszy plik z listy, ale można to zmienić, jeśli istnieje inny, który lepiej odzwierciedla zbiór danych. W tym miejscu można wybrać opcję pomijania plików z błędami , co jest zbawienne, gdy raport jest uszkodzony lub pusty, zapobiegając zatrzymaniu całego procesu.
Zarządzanie wynikami i oczyszczanie danych
Po zaakceptowaniu scalenia system wykonuje w tle pewne techniczne sztuczki: tworzy przykładowe zapytanie i sparametryzowaną funkcję. Oznacza to, że wszelkie zmiany wprowadzone w przykładowym pliku zostaną automatycznie zreplikowane do wszystkich pozostałych plików w folderze. Jeśli na przykład pliki CSV zawierają cztery wiersze zbędnych nagłówków przed właściwymi danymi, można przejść do zapytania transformacyjnego i skorzystać z opcji usunięcia górnych wierszy , aby uporządkować ten bałagan.
Po wyczyszczeniu wierszy konieczne jest zastosowanie funkcji „ Użyj pierwszego wiersza jako nagłówków” . Należy pamiętać, że w takim przypadku Power Query zazwyczaj dodaje krok automatycznej konwersji typu danych. Jeśli po powrocie do głównego zapytania pojawi się błąd informujący, że nie można znaleźć kolumny „Kolumna1”, nie martw się. To normalne, ponieważ zmieniłeś nazwy kolumn w przykładzie , a kolejny krok nie rozpoznaje już starych nazw. Wystarczy usunąć ten błędny krok z panelu „Zastosowane kroki”, a wszystko wróci do normy.
Typowe wyzwania i zaawansowane alternatywy
Czasami życie nie jest takie proste i napotykamy pliki, które nie są identyczne. Na przykład, jeśli masz raporty ServiceNow, w których jeden nagłówek nazywa się „resolved_on”, a drugi „closed_on”, Power Query może sprawiać problemy, ponieważ wymaga spójnej struktury . Jeśli przykładowy plik nie będzie dokładnie pasował do pozostałych, narzędzie może pominąć informacje z niezgodnych plików lub zgłosić błędy importu.
W przypadkach, gdy struktura jest całkowicie chaotyczna lub ilość danych jest ogromna (przekraczająca milion wierszy w Excelu), istnieją inne opcje. Niektórzy użytkownicy wybierają skrypty VBA wykorzystujące interfejsy CSV , które umożliwiają łączenie plików bez powtarzania nagłówków, niezależnie od tego, czy ogranicznik pola lub rekordu różni się w zależności od pliku. Chociaż powłoka systemu Windows jest niezwykle szybka, często zawodzi w obsłudze osadzonych podziałów wiersza, podczas gdy solidne rozwiązanie VBA nie przeciąża pamięci RAM i z łatwością obsługuje miliony rekordów.
Aby zakończyć proces, po połączeniu danych w Power Query, pamiętaj o przypisaniu prawidłowego typu danych do każdej kolumny (Data dla dat, Waluta dla dochodów itd.) przed załadowaniem tabeli końcowej. Możesz sprawdzić, czy wszystko jest poprawne, filtrując kolumnę Nazwa źródła, aby upewnić się, że wszystkie pliki w folderze zostały pomyślnie przetworzone.
Możliwość automatyzacji łączenia wielu plików CSV pozwala odejść od żmudnej pracy ręcznej i przejść na profesjonalny przepływ pracy, wykorzystując elastyczność narzędzia Power Query do oczyszczania danych lub uciekając się do języka VBA, gdy struktury są niezgodne lub objętość przekracza limity arkuszy kalkulacyjnych.