Połączymy dane z wielu plików Excela w jedną spójną tabelę danych. Zakładamy w tej metodzie, że nazwy kolumn w plikach różnią się miedzy sobą, ale są ułożone w takiej samej kolejności. Użyjemy do tego standardowych nazw kolumn nadawanych przez Power Query: Column1, Column2, Column3.
Jest to skuteczna metoda, jeżeli spełniamy poniższe założenia:
- Nazwy kolumn są w różnych wierszach
- Kolejność kolumn z danymi jest taka sama np. w pierwszej kolumnie w każdym pliku jest kolumna „Data” o różnym nazewnictwie
Wczytywanie plików do edytora Power Query
- Utwórz pusty skoroszyt Excela.
- Wykonaj poniższe kroki, aby zaimportować dane z folderu
- Z karty Dane kliknij w Nowe zapytanie
- Następnie wybierz opcję Z pliku
- Z folderu
- Wskaż folder z plikami
- Kliknij w przycisk Otwórz
Otworzy się okno z aktualną listą plików z folderu.
- Kliknij w przycisk „Przekształć dane”. Po chwili pojawi się edytor Power Query z wczytanymi plikami z folderu.
Po załadowaniu plików z folderu edytor Power Query powinien pojawić się w podobnej konfiguracji:
Więcej informacji na temat załadowywania plików z folderu do edytora Power Query znajdziesz tutaj:
Zmiana nazwy kolumny
- Zmień nazwę kolumny [Name] na [nazwa pliku]
Usuwanie zbędnych kolumn
Usuniemy niektóre kolumny, aby widok z danymi był bardziej czytelny.
- Zaznacz z przyciśniętym CTRL kolumny: [nazwa pliku] i [Content].
- Następnie Kliknij PPM, na jakąś z zaznaczonych kolumn i wybierz opcję „usuń inne kolumny”.
Dane prezentują się następująco
Zwracanie zawartości skoroszytu funkcją Excel.Workbook()
- Dodaj kolumnę niestandardową o nazwie „ex”
- w pasku formuły wpisz funkcję
Excel.Workbook([Content])Funkcja przekształca typ „Binarny” na typ „Table”. Power Query zwróci nam kolumnę [ex] z typem Table. Wewnątrz tej struktury są takie dane jak: nazwy arkuszy, źródła danych i inne informacje o plikach Excela
Rozwijanie danych z kolumny [ex]
- Kliknij w ikonę strzałek w prawym narożniku kolumny [ex], aby rozwinąć kolumny
- Zaznacz na liście wszystkie kolumny
- Odznacz opcję „Użyj oryginalnej nazwy jako prefiksu” (odznaczona opcja nie dodaje nazwy „ex.” przed nazwą każdej rozwiniętej kolumny. Zaznaczona opcja dodaje takowy tytuł)
- Zatwierdzić wybór przyciskiem „ok”
Dane prezentują się następująco
Więcej informacji na temat funkcji Excel.Workbook() znajdziesz tutaj:
Filtrowanie kolumn
- W kolumnie [Name] pozostaw tylko arkusze: 2016, 2017, 2018, 2019. Tam znajdują się docelowe dane.
- Kliknij w ikonę strzałki obok nazwy kolumny [Name]
- na liście danych zaznacz nazwy: 2016, 2017, 2018, 2019
- zatwierdź wybór przyciskiem „ok”
Usuwanie zbędnych kolumn
Usuniemy niektóre kolumny, aby widok z danymi był bardziej czytelny
- Zaznacz z przyciśniętym CTRL kolumny: [nazwa pliku], [Name] i [Data]. Następnie Kliknij PPM, na jakąś z zaznaczonych kolumn i wybierz opcję „usuń inne kolumny”.
Dane prezentują się następująco
Analiza struktury danych
W Kolumnie [Data] mamy wszystkie dane z wybranych arkuszy. Na podglądzie minitabelki:
- Nazwy kolumn mamy w pierwszym rzędzie, ponieważ nad docelowymi tytułami kolumn nie ma żadnych komentarzy, zbędnych informacji itd.
- kolejność kolumn o takim samym przeznaczeniu w każdej minitabelce jest identyczna,
- nazwy kolumn w pierwszym wierszu różnią się wielkością liter
W tej metodzie pozostawimy standardowe tytuły kolumn nadawane przez Power Query: Column1, Column2, Column3 itd. Połączymy dane przy użyciu tych tytułów. Ta metoda ma sens i działa poprawnie tylko wtedy, jeżeli spełniamy powyższe założenia i wiemy, że w każdej minitablce kolejność kolumn o tym samym przeznaczeniu jest identyczna.
Rozwijanie kolumn
- Kliknij w ikonę strzałek, która znajduje się obok nazwy kolumny [Data]
- Zaznacz wszystkie kolumny, aby wyodrębnić dane z minitabelek
- Odznacz opcję „Użyj oryginalnej nazwy kolumny jako prefiksu„. Zaznaczona opcja dla każdej rozwiniętej kolumny dodaje prefiks „Data.”
- zatwierdź wybór przyciskiem „OK”
Dane z plików zostały skonsolidowane. W pierwszym wierszu i na pewno jeszcze w kolejnych znajdują się tytuły kolumn z każdego pliku. Dzieje się tak, bo połączyliśmy dane z plików standardowymi nazwami kolumn (Column1, Column2…).
Należy je wyfiltrować, ale zanim to zrobisz ustal nowe nazwy kolumn.