Power Query: łączenie plików z wielu folderów

W tym poście będziemy pracować w Power Query, łączyć pliki z wielu folderów w naszej sieci, na komputerze stacjonarnym, OneDrive lub SharePoint.

W tym przykładzie mamy trzy pliki CSV w folderze, a dane wyglądają tak:

Będziemy używać Power Query w Excelu, który działa dokładnie tak samo jak Power Query w LuckyTemplates.

Spis treści

• Podłączanie do folderu w sieci
• Czyszczenie danych
• Łączenie z folderem w SharePoint lub OneDrive
• Wniosek

Podłączanie do folderu w sieci

Zaczniemy od naszego samouczka dotyczącego Power Query, połączymy pliki z wielu folderów z pustym plikiem Excel, przejdziemy do karty danych, klikniemy Get Data , From File , a następnie From Folder .

W ten sposób łączysz się z folderem znajdującym się na dysku C lub w sieci. Jeśli chcesz połączyć się z plikiem w programie SharePoint, użyj tego łącznika.

Użyj pola przeglądania, aby wyszukać swój folder. Następnie kliknij przycisk Połącz i przekształć dane .

Następnie zostaniemy zapytani, jaki to typ pliku CSV. Kliknij OK , a jeśli to nie zadziała, wróć i zmień na właściwy typ pliku CSV.

Otrzymujemy ten duży blok kroków w zapytaniu o moc. Może to być nieco zniechęcające i mylące. Te trzy pliki są ułożone jeden na drugim, co nie jest tym, co chcemy osiągnąć.

Klikniemy pierwszy plik, a następnie kliknij przycisk Przekształć przykładowy plik . Jakiekolwiek zmiany wprowadzimy w tym pliku, zostaną one również zastosowane do innych plików.

Czyszczenie danych

Najpierw uporządkujmy to. Chcemy, aby ta data zapełniała się przy każdym rekordzie. Jeśli kolumna 1 zawiera słowo data, chcemy umieścić datę w pozostałych kolumnach i wypełnić tę datę.

Dodamy kolumnę warunkową , która mówi, że jeśli Kolumna1 jest równa słowu data, to chcemy wartość z Kolumny2. W przeciwnym razie pozostaw to pole puste.

Wybierz kolumnę z datą i wartościami pustymi, a następnie kliknij prawym przyciskiem myszy i wypełnij.

Wynikiem będzie kolumna ze wszystkimi datami.

Aby pozbyć się spacji i wartości zerowych tutaj, kliknij Usuń puste .

Nie chcemy używać pierwszego wiersza jako nagłówków, ale nie możemy też użyć przycisku Użyj pierwszego wiersza jako nagłówków, ponieważ spowoduje to przesunięcie dat do wiersza nagłówków.

Spowoduje to zmianę daty dla każdego pliku. Najprostszym rozwiązaniem jest dla nas ręczna zmiana nazw tych kolumn.

Wybierz wszystkie kolumny, kliknij prawym przyciskiem myszy, a następnie kliknij usuń inne kolumny .

Po edycji podtytułów jesteśmy już prawie gotowi.

Kiedy klikniemy DemoFolder , otrzymamy komunikat o błędzie. Otrzymujemy to przez większość czasu, gdy wprowadzamy niewielką zmianę w przykładowym pliku transformacji.

W większości przypadków błąd jest spowodowany tym krokiem Changed Type, ponieważ próbuje zmienić oryginalną kolumnę 1, która już nie istnieje.

Po prostu usunięcie tego kroku naprawia błąd.

Wszystko, co zrobiliśmy z tym pierwszym plikiem, zostanie teraz zastosowane do każdego pliku. To naprawdę polega na tym, że nasze pliki źródłowe mają taką samą strukturę. Nie jest to niemożliwe, jeśli są różne, ale będzie to o wiele trudniejsze.

Spójrzmy więc wstecz na te zapytania i sprawdźmy, co się dzieje. Jeśli przejdziemy do kroku źródłowego przykładowego pliku, odnosi się on do tej rzeczy o nazwie Parameter1 .

A jeśli spojrzymy w lewo, parametr1 nazywa się plikiem przykładowym.

A jeśli przejdziemy do źródła przykładowego pliku, przejdziemy do rekordu 0.

Power Query jest językiem opartym na zerach, co oznacza, że ​​Rekord1 jest w rzeczywistości Pozycją 0. Po prostu pobiera pierwszy plik, a następnie automatycznie konwertuje pozostałe kroki, co jest po prostu genialne. Kiedyś trzeba było to robić ręcznie na karcie Przekształć plik.

Jeśli to rozwiniemy, poda nazwę pliku, a następnie przebieg wszystkich kroków.

Jeśli zamierzasz korzystać z zaawansowanego edytora, otrzymasz to ostrzeżenie. Zalecamy unikanie tego, ponieważ nie można ich później przywrócić. Ogólnie rzecz biorąc, po prostu wykonaj wszystkie poprawki w przykładowym pliku.

To, co wydarzyło się na tym etapie konsolidacji, polega na tym, że trafił do folderu i odfiltrował ukryte pliki lub pliki systemowe. Następnie dodał wywołaną funkcję niestandardową, zmienił nazwy kilku kolumn, a następnie usunął wszystko inne.

Jedyną rzeczą, którą zauważysz, jest utrata typów kolumn. Zatem jakiekolwiek typy kolumn wybrane w przykładowym pliku transformacji nie zostaną odziedziczone przez plik konsolidacji.

Następnie klikniemy Zamknij i załaduj . Za każdym razem, gdy klikniemy odśwież, przeskanuje ten folder.

Łączenie z folderem w SharePoint lub OneDrive

Jeśli Twoje pliki znajdują się w folderze OneDrive lub SharePoint, proces jest praktycznie taki sam, ale nieco inny. Potrzebujesz właściwej ścieżki do OneDrive, czyli adresu URL aż do Twojego imienia i nazwiska. Klikniemy Pobierz dane, a następnie folder SharePoint.

Poprosi nas o adres URL, pod którym możemy wkleić link. Następnie wyświetli prawie każdy plik, który masz w OneDrive. Kiedy przejdziemy do Transform Data , będziemy musieli wykonać kilka różnych kroków. Najpierw musimy znaleźć odpowiedni folder, którym jest DemoOneDriveFolder .

Klikniemy Filtr tekstu , następnie Zawiera , a następnie wpisz DemoOneDrive . Jeśli tego nie zrobimy, możemy przewijać przez wieki i nie znaleźć tego, czego szukamy.

Ten proces przefiltruje go do odpowiedniego folderu. Jak widać, mamy trzy pliki. Doskonały.

Przejdziemy do kolumny Ścieżka folderu , kliknij prawym przyciskiem myszy, przejdź do Text Filter i wybierz Equals .

W ten sposób możemy pobrać właściwą ścieżkę zamiast części zawierającej. Możemy po prostu pozbyć się tego ostatniego kroku, ponieważ go nie potrzebujemy.

Inną radą byłoby utworzenie filtra, w którym filtr tekstowy jest równy plikom CSV – na wypadek, gdyby inne pliki zostały tutaj upuszczone.

Ostatnim zaleceniem jest wywołanie tego zapytania OneDriveFolder , a następnie odwołanie się do niego. Jest to przydatne, gdy później próbujesz debugować i musisz wrócić, a nawet gdy musisz zmienić folder.

Byłoby to bardziej złożone niż wcześniejszy przykład, nad którym pracowaliśmy. W tym przypadku potrzebujemy tylko kolumn Treść i Nazwa .

Po prostu klikniemy prawym przyciskiem myszy, aby usunąć inne kolumny.

Kliknij małą dwukierunkową strzałkę, która połączy pliki.

Od tego momentu proces będzie identyczny jak w poprzednim przykładzie, w którym utworzono również zapytania pomocnicze.

Tylko krótka uwaga, że ​​jeśli konsolidujesz pliki Excela, jeden z wyskakujących ekranów pośrednich zapyta, który arkusz chcesz skonsolidować. Arkusze naprawdę muszą mieć tę samą nazwę, ponieważ konsolidacja wielu plików programu Excel może spowodować bałagan.

Oto nasz przekształcony przykładowy plik, starannie powiązany, dokładnie tak, jak zrobiliśmy to w poprzednim przykładzie.

I wreszcie, oto plik konsolidacji , który zostanie wypchnięty i załadowany do modelu danych.

Mamy też jeden dodatkowy folder o nazwie OneDriveFolder . Na koniec nie zapomnij zmienić nazwy swoich kroków na coś przydatnego i znaczącego.

Power Query — najlepsze praktyki dotyczące modelu danych
Organizowanie zapytań w grupy w usłudze LuckyTemplates

Wniosek

W tym samouczku pracowaliśmy nad Power Query, łączeniem plików z wielu folderów z naszej sieci, pulpitu, OneDrive lub SharePoint.

Jeśli podobały Ci się treści omówione w tym samouczku, zasubskrybuj kanał telewizyjny LuckyTemplates. Cały czas publikujemy ogromną ilość treści ode mnie i wielu twórców treści, których celem jest ulepszenie sposobu, w jaki korzystasz z usługi LuckyTemplates i Power Platform.