Importowanie danych z folderu zawierającego wiele plików (Power Query)

Użyj Power Query, aby połączyć wiele plików z tym samym schematem przechowywanym w jednym folderze w jednej tabeli. Na przykład każdy miesiąc, w którym chcesz połączyć skoroszyty budżetu z wielu działów, gdzie kolumny są takie same, ale liczba wierszy i wartości różni się w każdym skoroszycie. Po jej skonfigurowaniu możesz zastosować dodatkowe przekształcenia tak samo jak każde jedno zaimportowane źródło danych, a następnie odświeżyć dane , aby wyświetlić wyniki dla każdego miesiąca.

Koncepcyjne omówienie łączenia plików folderów

Uwaga W tym temacie pokazano, jak łączyć pliki z folderu. Możesz również łączyć pliki przechowywane w programie SharePoint, Azure Blob Storage i Azure Data Lake Storage. Proces jest podobny.

Nowsze wersje Office 2013

Zachowaj prostotę:

Upewnij się, że wszystkie pliki, które chcesz połączyć, znajdują się w folderze dedykowanym bez zbędnych plików. W przeciwnym razie wszystkie pliki w folderze i wybrane podfoldery są uwzględniane w danych do połączenia.
Każdy plik powinien mieć ten sam schemat ze spójnymi nagłówkami kolumn, typami danych i liczbą kolumn. Kolumny nie muszą być w tej samej kolejności, w jakiej dopasowywanie odbywa się według nazw kolumn.
Jeśli to możliwe, unikaj niepowiązanych obiektów danych dla źródeł danych, które mogą mieć więcej niż jeden obiekt danych, taki jak plik JSON, skoroszyt programu Excel lub baza danych programu Access.

Każdy z tych plików ma prosty wzorzec — tylko jedna tabela danych w każdym pliku.

Wybierz pozycję Dane > Pobierz > danychz >pliku z folderu. Zostanie wyświetlone okno dialogowe Przeglądanie .
Znajdź folder zawierający pliki, które chcesz połączyć.
Lista plików w folderze zostanie wyświetlona w oknie dialogowym ścieżka folderu <>. Sprawdź, czy na liście znajdują się wszystkie potrzebne pliki.
Wybierz jedno z poleceń u dołu okna dialogowego, na przykład Połącz > Połącz & Załaduj. W sekcji Informacje o wszystkich tych poleceniach omówiono dodatkowe polecenia.
Jeśli wybierzesz jakiekolwiek polecenie Połącz, zostanie wyświetlone okno dialogowe Łączenie plików . Aby zmienić ustawienia pliku, zaznacz każdy plik w polu Przykładowy plik , ustaw żądane ustawienia Pochodzenie pliku, Ogranicznik i Wykrywanie typu danych . Możesz również zaznaczyć lub wyczyścić pole wyboru Pomiń pliki z błędami u dołu okna dialogowego.
Wybierz przycisk OK.

Wynik

Power Query automatycznie tworzy zapytania w celu skonsolidowania danych z każdego pliku w arkuszu. Utworzone kroki i kolumny zapytania zależą od wybranego polecenia. Aby uzyskać więcej informacji, zobacz sekcję Informacje o wszystkich tych zapytaniach.

Wybierz pozycję Dane > Pobierz > danychz >pliku z folderu. Zostanie wyświetlone okno dialogowe Przeglądanie.
Znajdź folder zawierający pliki, które chcesz połączyć.
Lista plików w folderze zostanie wyświetlona w oknie dialogowym ścieżka folderu <>. Sprawdź, czy na liście znajdują się wszystkie potrzebne pliki.
Wybierz jedno z poleceń u dołu okna dialogowego, na przykład Połącz > Połącz & Przekształć. W sekcji Informacje o wszystkich tych poleceniach omówiono dodatkowe polecenia.

Zostanie wyświetlony Edytor Power Query.
Kolumna Wartość to uporządkowana kolumna Lista . Wybierz ikonę Rozwiń , a następnie wybierz pozycję Rozwiń do nowych wierszy.
Kolumna Wartość jest teraz strukturalną kolumną Record (Rekord ). Wybierz ikonę Rozwiń . Zostanie wyświetlone okno dialogowe listy rozwijanej.
Pozostaw zaznaczone wszystkie kolumny. Może być konieczne wyczyszczenie pola wyboru Użyj oryginalnej nazwy kolumny jako prefiksu . Wybierz przycisk OK.
Zaznacz wszystkie kolumny zawierające wartości danych. Wybierz pozycję Narzędzia główne, strzałkę obok pozycji Usuń kolumny, a następnie wybierz pozycję Usuń inne kolumny.
Wybierz pozycję Narzędzia główne > Zamknij & Załaduj.

Wynik

Każde z tych źródeł danych może mieć więcej niż jeden obiekt do zaimportowania. Skoroszyt programu Excel może zawierać wiele arkuszy, tabel programu Excel lub nazwanych zakresów. Baza danych programu Access może zawierać wiele tabel i zapytań.

Wybierz pozycję Dane > Pobierz > danychz >pliku z folderu. Zostanie wyświetlone okno dialogowe Przeglądanie.
Znajdź folder zawierający pliki, które chcesz połączyć.
Lista plików w folderze zostanie wyświetlona w oknie dialogowym ścieżka folderu <>. Sprawdź, czy na liście znajdują się wszystkie potrzebne pliki.
Wybierz jedno z poleceń u dołu okna dialogowego, na przykład Połącz > Połącz & Załaduj. W sekcji Informacje o wszystkich tych poleceniach omówiono dodatkowe polecenia.
W oknie dialogowym Łączenie plików :
- W polu Przykładowy plik wybierz plik do użycia jako przykładowe dane użyte do utworzenia zapytań. Nie można zaznaczyć obiektu lub zaznaczyć tylko jednego obiektu. Nie można jednak wybrać więcej niż jednego.
- Jeśli masz wiele obiektów, użyj pola Wyszukaj , aby zlokalizować obiekt, lub opcji wyświetlania wraz z przyciskiem Odśwież w celu filtrowania listy.
- Zaznacz lub wyczyść pole wyboru Pomiń pliki z błędami u dołu okna dialogowego.
Wybierz przycisk OK.

Wynik

Power Query automatycznie tworzy zapytanie w celu skonsolidowania danych z każdego pliku w arkuszu. Utworzone kroki i kolumny zapytania zależą od wybranego polecenia. Aby uzyskać więcej informacji, zobacz sekcję Informacje o wszystkich tych zapytaniach.

Aby uzyskać większą elastyczność, możesz jawnie łączyć pliki w Edytor Power Query, używając polecenia Połącz pliki. Załóżmy, że folder źródłowy ma połączenie typów plików i podfolderów i chcesz kierować określone pliki z tym samym typem pliku i schematem, ale nie na inne. Może to zwiększyć wydajność i uprościć przekształcenia.

Wybierz pozycję Dane > Pobierz > danychz > plikówz folderu. Zostanie wyświetlone okno dialogowe Przeglądanie .
Znajdź folder zawierający pliki, które chcesz połączyć, a następnie wybierz pozycję Otwórz.
Lista wszystkich plików w folderze i podfolderach zostanie wyświetlona w oknie dialogowym <Ścieżka folderu> . Sprawdź, czy na liście znajdują się wszystkie potrzebne pliki.
Wybierz pozycję Przekształć dane u dołu. Zostanie otwarta Edytor Power Query i zostaną wyświetlone wszystkie pliki w folderze oraz wszystkie podfoldery.
Aby wybrać odpowiednie pliki, przefiltruj kolumny, takie jak Rozszerzenie lub Ścieżka folderu.
Aby połączyć pliki w jedną tabelę, wybierz kolumnę Zawartość zawierającą każdy plik binarny (zazwyczaj pierwsza kolumna), a następnie wybierz pozycję Narzędzia główne > Połącz pliki. Zostanie wyświetlone okno dialogowe Łączenie plików .
Power Query analizuje przykładowy plik, domyślnie pierwszy plik na liście, aby użyć poprawnego łącznika i zidentyfikować pasujące kolumny.

Aby użyć innego pliku dla pliku przykładowego, wybierz go z listy rozwijanej Przykładowy plik .
Opcjonalnie u dołu wybierz pozycję Pomiń pliki z błędami, aby wykluczyć te pliki z wyniku.
Wybierz przycisk OK.

Wynik

Istnieje kilka poleceń, które można wybrać, a każdy z nich ma inny cel.

Łączenie i przekształcanie danych Aby połączyć wszystkie pliki z zapytaniem, a następnie uruchomić Edytor Power Query, wybierz pozycję Połącz > Połącz i Przekształć dane.
Łączenie i ładowanie Aby wyświetlić okno dialogowe Przykładowy plik, utwórz zapytanie, a następnie załaduj je do arkusza, wybierz pozycję Połącz > Połącz i Załaduj.
Połącz i załaduj do Aby wyświetlić okno dialogowe Przykładowy plik, utwórz zapytanie, a następnie wyświetl okno dialogowe Importowanie , wybierz pozycję Połącz > Połącz i Załaduj do.
Obciążenia Aby utworzyć zapytanie w jednym kroku, a następnie załadować je do arkusza, wybierz pozycję Załaduj > Załaduj.
Załaduj do Aby utworzyć zapytanie w jednym kroku, a następnie wyświetlić okno dialogowe Importowanie , wybierz pozycję Załaduj > Załaduj do.
Przekształcanie danychAby utworzyć zapytanie w jednym kroku, a następnie uruchomić Edytor Power Query, wybierz pozycję Przekształć dane.

Jednak łącząc pliki, w okienku Zapytania w grupie "Zapytania pomocników" jest tworzonych kilka zapytań pomocniczych.

Lista zapytań utworzonych w okienku Zapytania

Power Query tworzy zapytanie "Przykładowy plik" na podstawie przykładowego zapytania.
Zapytanie funkcji "Transform File" używa zapytania "Parametr1" do określenia każdego pliku (lub pliku binarnego) jako danych wejściowych do zapytania "Przykładowy plik". To zapytanie tworzy również kolumnę Zawartość zawierającą zawartość pliku i automatycznie rozszerza strukturalną kolumnę Record (Rekord ) w celu dodania danych kolumny do wyników. Zapytania "Przekształć plik" i "Przykładowy plik" są połączone, dzięki czemu zmiany w zapytaniu "Przykładowy plik" są odzwierciedlane w zapytaniu "Przekształcanie pliku".
Zapytanie zawierające końcowe wyniki znajduje się w grupie "Inne zapytania". Domyślnie nazwa folderu pochodzi od folderu, z którego zostały zaimportowane pliki.

W celu dalszego zbadania, kliknij prawym przyciskiem myszy każde zapytanie i wybierz pozycję Edytuj , aby zbadać każdy krok zapytania i zobaczyć, jak zapytania działają w porozumieniu.

Aby rozpocząć proces łączenia wielu plików, najpierw upewnij się, że wszystkie pliki, które chcesz połączyć, znajdują się w jednym folderze plików.

Uwaga: Obsługiwane są pliki programów Excel, Text, CSV, JSON, XML i Access.

Kliknij kartę Power Query, a następnie pozycję Z pliku > Z folderu.
Kliknij przycisk Przeglądaj , aby zlokalizować folder.

Zostanie wyświetlona lista plików w folderze. Wybierz opcję Edytuj, aby otworzyć Edytor Power Query. W takim przypadku załadowano 4 skoroszyty programu Excel.
Po zweryfikowaniu, że na liście znajdują się wszystkie pliki, kliknij przycisk Połącz pliki binarne w kolumnie Zawartość .
Każdy plik jest analizowany i ustalany jest prawidłowy format pliku, taki jak tekst, program Excel lub JSON. W tym przypadku zostanie wyświetlona lista arkuszy z pierwszego skoroszytu programu Excel. Zaznacz arkusz, którego chcesz użyć, a następnie kliknij przycisk OK.
Proces przekształcania plików binarnych łączenia automatycznie utworzy zapytanie w celu skonsolidowania danych z każdego pliku i wyświetlenia podglądu. Jeśli wynik Ci odpowiada, kliknij pozycję Zamknij & Załaduj.

Proces łączenia plików binarnych skonsoliduje wymienione arkusze w jednym arkuszu.
Jeśli oryginalne pliki danych ulegą zmianie, zawsze możesz odświeżyć zaimportowane dane. Kliknij dowolne miejsce w zakresie danych, a następnie przejdź do obszaru Narzędzia zapytań > odświeżania. Ponadto można łatwo zastosować dodatkowe kroki przekształcania lub wyodrębniania, modyfikując automatycznie utworzone zapytanie przykładowe, bez konieczności modyfikowania lub tworzenia dodatkowych kroków zapytania funkcjonalnego. wszelkie zmiany w zapytaniu przykładowym są automatycznie generowane w zapytaniu funkcji połączonych.

Znane problemy

Podczas ładowania procedury łączenia plików binarnych do programu Excel może zostać wyświetlony następujący komunikat o błędzie:

Komunikat o błędzie Połącz pliki binarne. Jest to znany błąd, który jest rozwiązywany.

Jeśli błędy zostaną odrzucone przez kliknięcie przycisku OK, proces łączenia plików binarnych zostanie ukończony i poprawnie załaduje dane pomimo błędu. Pracujemy nad poprawienie błędu.