Połączymy wiele plików Excela z folderu w jedną spójną tabelę danych. Nasz problem dotyczy plików, w których docelowe nazwy kolumn są w różnych wierszach.
Metoda skuteczna, jeżeli:
- Nazwy kolumn są w różnych wierszach
- Nazwy kolumn są takie same, ale różnią się wielkością liter
- Nad docelowymi nazwami kolumn mogą być komentarze, informacje i inne zbędne dane
Analiza struktury plików
Docelowy wiersz z nazwami kolumn w każdym z plików jest na różnej pozycji. Odpowiednimi funkcjami zaznaczymy docelowy rekord z tytułami kolumn i przeniesiemy go w docelowe pola nagłówkowe. Pliki zostaną połączone w spójną tabelę przy użyciu nazw kolumn. Problem dla tego typu łączenia stanowią także wielkości liter. Przed łączeniem przekształcimy nasze docelowe nazwy kolumn na małe litery, a na końcu scalimy pliki w zbiorczą tabelę.
- w pliku 2016.xlsx nazwy kolumn są w trzecim wierszu, a nad nimi są zbędne informacje, które należy pominąć
- w pliku 2017.xlsx nazwy kolumn są w pierwszym wierszu
- w pliku 2018.xlsx nazwy kolumn są w czwartym wierszu
- w pliku 2019.xlsx nazwy kolumn są w drugim wierszu, a nad nimi są zbędne informacje do pominięcia
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 „Excelowy”. Dzięki niej pozyskamy takie dane jak: nazwy arkuszy, zawarte w nich dane i inne informacje o plikach. Po dodaniu kolumny niestandardowej Power Query zwróci nam kolumnę [ex] z typem TABLE.
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 dodaję 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
- Zaznacz z przyciśniętym CTRL kolumny: [nazwa pliku], [Name] i [Data]. Następnie Kliknij PPM, na którąś z zaznaczonych kolumn i wybierz opcję „usuń inne kolumny”.
Dane powinny teraz prezentować się następująco:
Łączenie plików w spójną tabelę
- Dodaj kolumnę niestandardową o nazwie „do rozwinięcia”.
- W pasku formuły wpisz funkcję:
Table.TransformColumnNames(Table.PromoteHeaders(Table.Skip([Data], List.PositionOf([Data][Column1], "miesiąc i rok"))), Text.Lower)Wynikiem funkcji będzie uporządkowane struktura każdej tabeli. Kolumny po rozwinięciu będą poprawnie połączone w jedną spójną bazę. W Power Query funkcje czyta się od końca. Ostatni krok jest pierwszym.
Przeanalizujmy działanie funkcji od początku
Wskazanie numeru wiersza z nazwami kolumn
- Dodaj nową kolumnę niestandardową o nazwie [pozycja]
w pasku formuły wpisz funkcję:
List.PositionOf([Data][Column1], "miesiąc i rok")Struktura funkcji
List.PositionOf(lista elementów, "pobieranie pozycji wskazanej nazwy")List.PositionOf([Data][Column1], "miesiąc i rok")[Data][Column1] – zaznacza kolumnę [Column1] z tabeli [Data] tworząc listę elementów typu List.
List.PositionOf() ma wskazać dla każdej tabeli z kolumny [Data] numer pierwszego napotkanego wiersza w kolumnie [Column1] równego nazwie „miesiąc i rok”. W pliku „2016.xlsx” ten tekst jest na drugim miejscu – to pozycja z nazwami kolumn. Funkcje typu List zaczynają numerację od 0.
Pomijanie pierwszych wierszy
- Dodaj kolumnę niestandardową o nazwie [nagłówki w 1 wierszu]
w pasku formuły wpisz funkcję:
Table.Skip([Data], List.PositionOf([Data][Column1], "miesiąc i rok"))Struktura funkcji
Table.Skip(źródło typu TABLE, liczba pierwszych wierszy do pominięcia)Table.Skip([Data], List.PositionOf([Data][Column1], "miesiąc i rok"))Źródło typu TABLE – jest to kolumna z minitabelkami
Liczba pierwszych wierszy do pominięcia – to liczba wskazana formułą ListPositionOf()
Funkcja Table.Skip() w każdej minitablce z kolumny [Data] pomija liczbę pierwszych wierszy wskazaną przez funkcję List.PositionOf(). Wynikiem funkcji jest nowa tabela danych, w której docelowe nazwy kolumny zostały przeniesione do pierwszego wiersza.
Przenoszenie nazw kolumn do pól nagłówkowych
- Dodaj kolumnę niestandardową o nazwie [docelowa pozycja nagłówków]
w pasku formuły wpisz funkcję:
Table.PromoteHeaders(Table.Skip([Data], List.PositionOf([Data][Column1], "miesiąc i rok")))Struktura funkcji
Table.PromoteHeaders(źródło typu TABLE)Table.PromoteHeaders(Table.Skip([Data], List.PositionOf([Data][Column1], "miesiąc i rok")))Funkcja Table.PromoteHeaders() we wskazanej tabeli przesuwa nazwy kolumn z pierwszego wiersza do docelowych pól nagłówkowych.
Nagłówki są już na docelowej pozycji, ale różnorodna wielkość liter w każdym z plików komplikuje zasadę zachowania spójności nazewnictwa, co skutkuje niepoprawnym połączeniem danych w jedną spójną tabelę np. kolumny [kategoria] i [Kategoria] będą w osobnych kolumnach. Power Query jest Key sensitive, dlatego wielkość liter w tytułach kolumn ma znaczenie. W następnym kroku zamienimy duże litery na małe.
Zamiana wszystkich liter w nazwach kolumn na małe
- Dodaj kolumnę niestandardową o nazwie [dane do rozwinięcia]
w pasku formuły wpisz funkcję:
Table.TransformColumnNames(Table.PromoteHeaders(Table.Skip([Data], List.PositionOf ([Data][Column1], "miesiąc i rok"))), Text.Lower)Struktura funkcji
Table.TransformColumnNames(źródło typu TABLE, funkcja zmiany)Table.TransformColumnNames(Table.PromoteHeaders(Table.Skip([Data], List.PositionOf ([Data][Column1], "miesiąc i rok"))), Text.Lower)Funkcja Table.TransformColumnNames() we wskazanej tabeli zamienia wszystkie nazwy kolumn na małe litery.
Kolumna [dane do rozwinięcia] zawiera minitabelki z uporządkowanymi danymi. Na powyższym obrazku widać ostateczną formę nagłówków.
Rozwijanie kolumn
- Rozwiń wszystkie dane z kolumny [do rozwinięcia] klikając w ikonę strzałek obok nazwy kolumny
Otrzymaliśmy spójną tabelę danych.
Ominęliśmy w plikach zbędne informacje nad docelowymi nazwami kolumn. Następnie przenieśliśmy tytuł kolumn do pól nagłówkowych i zmieniliśmy ich format tekstu na małe litery. Dane zostały poprawnie skonsolidowane, a efekt widać na załączonym obrazku.
