Połączymy kilka plików Excela z folderu poprzez nazwy kolumn w zbiorczą tabelę danych. Kolejność kolumn nie ma znaczenia. Jeżeli w każdym z plików występuje kolumna o nazwie [produkt] to do tej kolumny będą dołączane dane z pozostałych plików. Analogicznie ten proces zostanie wykonany dla pozostałych kolumn.
Jest to skuteczna metoda, jeżeli spełniamy poniższe założenia:
- nazwy kolumn są w pierwszym wierszu
- tytuły kolumn mogą być napisane różną wielkością liter
- nazwy kolumn w każdym pliku muszą być jednakowe
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:
Usuwanie zbędnych kolumn
Usuniemy niektóre kolumny, aby widok z danymi był bardziej czytelny
- Zaznacz z przyciśniętym CTRL kolumny: [Name] i [Content].
- Następnie Kliknij PPM, na jakąś z zaznaczonych kolumn i wybierz opcję „usuń inne kolumny”. Kolumna [Content] zawiera zawartość plików.
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 dodaję takowy tytuł)
- Zatwierdzić wybór przyciskiem „ok”
Dane prezentują się następująco
Więcej informacji na temat funkcji Excel.Workbook() znajdziesz tutaj:
Zmiana nazw kolumn
- Zmień nazwy kolumn
- [Name] na [nazwa pliku]
- [Name.1] na [Name]
Filtrowanie kolumn
- W kolumnie [Name] pozostaw tylko arkusze o nazwach: 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] i [Data]. Następnie Kliknij PPM, na którąś 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ż w plikach nad docelowymi nagłówkami nie ma żadnych komentarzy, zbędnych informacji itd.
- W każdym z plików mamy te same nazwy kolumn na różnych pozycjach. Nie przeszkodzi to w prawidłowej konsolidacji. Przykładowo kolumna o nazwie [miesiąc i rok] w innym pliku może być w trzeciej kolumnie itd.
- Nie mamy pewności czy w każdym z plików nazwy kolumn są z małych liter. Ma to bardzo duże znaczenie. Te same nazwy kolumn różniące się wielkością liter nie są sobie równe i dane z tych kolumn nie trafią pod jedną kolumnę. Dla bezpieczeństwa zamienimy wszystkie nazwy kolumn w każdym z plików na małe litery
Przenoszenie nazw kolumn do pól nagłówkowych
Należy podnieść nasze nazwy kolumn w każdej minitabelce o jeden wiersz wyżej. W ten sposób trafią w docelowe pola nagłówkowe.
- W oknie z listą zastosowanych kroków kliknij w krok, w którym została użyta funkcja Excel.Workbook(). U mnie jest to trzeci krok o nazwie Dodano kolumnę niestandardową
- Zmodyfikuj go klikając w ikonę zębatki znajdującej się obok nazwy kroku
Obecnie funkcja ma taką postać
Excel.Workbook([Content])Zmodyfikuj ją na taką postać
Excel.Workbook([Content], true)Wartość true jest informacją dla Power Query, że nazwy kolumn są w pierwszym rzędzie i można je umieścić na miejscu docelowych pól nagłówkowych. Zatwierdź zmiany i wróć do ostatniego kroku „Usunięto inne kolumny1„
Spójrz teraz na podgląd minitabelki. Nazwy kolumn są na docelowych pozycjach
Zamiana wszystkich liter w nazwach kolumn na małe
Jeżeli masz pewność, że w swoich plikach wszystkie nazwy kolumn są napisane z małych liter to na tym etapie możesz już rozwinąć dane z minitabelek. Kliknij w ikonę strzałek obok nazwy kolumny [Data] i wybierz kolumny, których dane Cię interesują.
Jeżeli nie masz pewności co do wielkości liter zastosowanych w kolumnach postępuj według instrukcji:
- Dodaj kolumnę niestandardową o nazwie do rozwinięcia
W pasku formuły wpisz funkcję:
Table.TransformColumnNames([Data], Text.Lower)Funkcja w każdej minitabelce zamienia w nazwach kolumn duże litery na małe.
Na podglądzie minitabelki widać, że została zastosowana zmiana wielkości liter.
Rozwijanie kolumn
- Kliknij w ikonę strzałek, która znajduje się obok nazwy kolumny [do rozwinięcia]
- zaznacz wybrane 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 „do rozwinięcia.”
Wszystkie dane trafiły pod odpowiednie kolumny. Otrzymałeś w ten sposób spójną tabelę danych.
