Wraz z marcową aktualizacją, dodatek Power Query został wzbogacony o funkcjonalność  automatycznej konsolidacji plików MS Excel, umieszczonych w tym samym folderze. Niestety nie w każdym przypadku jest możliwe posłużenie się tym narzędziem. Zależy to od wersji posiadanego dodatku lub przypadku z jakim mamy do czynienia. Niemniej Power Query może być nadal pomocne w wykonaniu takiego zadania.

Sposobów wykonania takiego zapytania jest kilka. Poniżej opiszę  jeden z nich:

Krok 1: Import pojedynczego pliku w celu ustalenia kolejnych etapów transformacji zawartości pliku, a następnie przerabia do postaci funkcji.

Krok 2: Odczyt zawartości folderu i wykonanie funkcji na aktualnej zawartości folderu.

Co istotne, ta metoda  może zostać wykorzystana do innych wariantów parametryzowania kwerendy. W stosunku do automatycznego sposobu importu, nie są generowane dodatkowe kwerendy pośrednie. Są one najczęściej możliwe do wyeliminowania.

W pierwszym kroku należy zebrać w jednym folderze wszystkie importowane pliki, o spójnym układzie danych. W przykładzie zostały wykorzystane cztery pliki zawierające tabele krzyżowe z wynikami różnych pracowników za kolejnych dwanaście miesięcy.

Rysunek 1. Zawartość folderu

Pliki zawierają różną ilość wierszy oraz różnią się identyfikatorami pracowników.

Rysunek 2. Przykładowa zawartość pliku

Import rozpoczynamy wybierając z PowerQuery, polecenie importu danych z pliku MS Excel. W przypadku pracy z MS Excel w wersji 2016, PowerQuery nie jest już dodatkiem i stanowi integralną część aplikacji. Wszystkie polecenia dostępne są z poziomu karty Dane/Data – Nowe zapytanie – Z pliku.

Rysunek 3. Wybór polecenia importu danych z pliku.

Następnie wskazujemy dowolny plik z folderu. Po wskazaniu pliku narzędzie wyświetla zawartość pliku. Zaznaczamy arkusz do importu i zatwierdzamy poleceniem Edytuj/Edit. Pozwala to dokonać koniecznych transformacji na danych umieszczonych w arkuszu.

Do często stosowanych poleceń należy użycie pierwszego wiersza jako nagłówków.

Rysunek 4. Użyj pierwszego wiersza jako nagłówków

Inna ważna transformacja to anulowanie przestawienia kolumn, w stosunku do wskazanych (Unpivot other columns).

Rysunek 5. Anuluj przestawienie innych kolumn/Unpivot Other Columns

Zapewnia to uzyskanie układu danych w postaci listy z jednolitą zawartością pól i rekordami. Jest to potrzebne w przypadku tabel krzyżowych umieszczonych w importowanych arkuszach. Docelowo pozwala na konsolidację wielu plików. Konieczne może być również dostosowanie nagłówków uzyskanych w ten sposób kolumn oraz zmiana typu danych w poszczególnych kolumnach. Szczególną uwagę należy zwrócić na dane liczbowe oraz daty.

Dla wygody późniejszego wykorzystania jako funkcji, zaleca się zmianę zapytania na przyjazne użytkownikowi.

Rysunek 6. Układ danych po transformacji oraz zmianie nazwy zapytania

Po wykonaniu powyższych kroków, można przejść do edytora zaawansowanego w celu wyedytowania wygenerowanego kodu kwerendy. Przed istniejącym kodem dodać należy polecenie „let” i po istniejącym kodzie „in”. Przed edycją, kwerenda wygląda jak na rysunku poniżej:

W kroku Źródło/Source znajduje się ścieżka dostępu oraz nazwa wybranego pliku. W celu zmiany zapytania na funkcję należy zastąpić nazwę konkretnego pliku na ogólną, którą wcześniej deklaruje się jako zmienną i przekazuje się do kwerendy. W Power Query stosuje się język M.

Gotowa treść funkcji wygląda następująco:

Rysunek 7. Gotowa funkcja Power Query

Po przygotowaniu funkcji, można przejść do kolejnego etapu. Polega on na odczytaniu listy plików umieszczonych w folderze i wykonaniu funkcji na każdym z nich. W tym celu należy wykonać kolejne zapytanie: Z pliku – Z folderu/ From file – From Folder.

Rysunek 8. Import danych z folderu

Po wskazaniu folderu, zostaje odczytana zawartość folderu wraz z właściwościami. Do konsolidacji jest potrzebna tylko kolumna z listą nazw plików.

Rysunek 9. Lista plików po usunięciu pozostałych kolumn z właściwościami plików umieszczonych w folderze

Następny krok to dodanie kolumny wykonującej funkcję niestandardową, utworzoną w poprzednim zapytaniu.

Rysunek 10. Wstawianie kolumny niestandardowej, wykonującej funkcję import na kolumnie Nazwa/Name

W wyniku tego, obok kolumny z nazwami plików powstaje kolumna zawierająca wyniki funkcji wykonanej na każdym z nich. Trzeba rozładować zawartość tej kolumny, używając przycisku znajdującego się po prawej stronie nagłówka kolumny.

Rysunek 11. Rozszerzenie zawartości kolumny

Końcowy wynik wymaga doprecyzowania szczegółów jak na przykład poprawy typu danych w kolumnach i ich nazw. Dodatkowo można wykorzystać nazwy plików do opisu skonsolidowanych danych.

Rysunek 12. Podgląd wyniku gotowego zapytania

Na koniec pozostaje zamknięcie zapytania i załadowanie danych. Można je załadować do arkusza, do modelu danych Power Pivot lub pozostawić tylko zapytanie.

Ta sama metoda importu danych może zostać wykorzystana w wielu innych przypadkach.

Rysunek 13. Dane załadowane do arkusza

Co ważne, jeżeli w folderze pojawią się kolejne pliki z danymi w takim samym układzie jak wykorzystane do utworzenia kwerendy, wszystkie zostaną skonsolidowane.