Ostatni webinar nt. pobierania plików z folderu z Power Query dla Excela i Power BI rozpoczął ciekawą dyskusję. Jeden z uczestników zadał pytanie: Skąd możemy mieć pewność, że kolumny w nowym pliku są identyczne jak w pierwszym? Oto odpowiedź.
Przypomnienie: Pobieranie danych z folderu poprzez Power Query
Zbudowanie zapytania do folderu wygląda tak:
- Utworzyć folder na dysku lokalnym, dysku sieciowym lub online
- Zbudować zapytanie do folderu i połączyć wszystkie dane (odpowiednik ręcznej operacji Dołączanie)
- Dodawać kolejne pliki do folderu i aktualizować dane poprzez kliknięcie Odśwież
Oto przykład działającego zapytania:
Co się stanie, jeśli nie wychwycimy błędów?
Standardowy proces pobierania danych bazuje na przykładowym pliku. Jeśli w nowym pliku dojdzie nowa kolumna lub stara ulegnie zmianie, raport będzie niekompletny.
W naszym scenariuszu:
- zmianie ulega kolumna Numer zamówienia na Numer faktury
- dochodzi nam nowa kolumna Marża.
Po dodaniu nowego pliku do folderu i odświeżeniu zapytania, otrzymamy puste numery zamówienia i zgubimy nową kolumnę.
Pamiętaj: Power Query to też narzędzie do sprawdzania jakości danych
Power Query jest nie tylko programem do przygotowania procesu pobierania i transformacji danych, ale też świetnym miejscem na wychwycenie błędów. Błędy można zbierać w dodatkowych arkuszach lub raportach błędów i weryfikować prze udostępnieniem raportu.
Sprawdzanie błędów może odbywać się na wiele różnych sposobów, ale zwykle jako dodatkowe zapytania zatytułowane: Błędy. Dodatkowe zapytania tworzymy poprzez Scalanie, Duplikuj lub Odwołanie.
Krok 1: Zbuduj zapytanie z listą kolumn dla pierwszego pliku
Tworzymy odwołanie do zapytania Przekształć przykładowy plik.
Teraz czas na opuszczenie nagłówków do pierwszego wiersza.
Na koniec zostawiamy tylko 1 wiersz i transponujemy.
Krok 2: Zbuduj zapytanie z listą kolumn ostatniego pliku
Zapytanie rozpocznij od zduplikowania zapytania głównego.
Następnie usuń z niego niepotrzebne kroki, zostawiając tylko dwa pierwsze (1) > Posortuj pliki (2) tak, aby ostatni plik był na końcu (tu przydaje się odpowiednie nazewnictwo plików) > Zachowaj ostatni 1 wiersz (3).
Dodaj kolumnę niestandardową z poleceniem Excel.Workbook (1), a w kolejnym kroku wejdź do środka arkusza, klikając Table (2) x 2.
Następnie zachowaj 1 wiersz.
Na koniec Transponuj.
Krok 3: Scal zapytania jako Pełne zewnętrzne
Zapytania scalamy, wybierając jako rodzaj sprzężenia Pełne zewnętrzne.
Dzięki temu po rozpakowaniu dostaniemy wszystkie kolumny z obu plików.
Pamiętajmy, że kolejność kolumny przy pobieraniu plików z folderu nie ma znaczenia – liczy się nazwa kolumny.
Na koniec możemy dodać np. kolumnę warunkową.
Krok 4: Przygotuj raport w Excelu lub w Power BI
Do Excela (lub modelu danych Power BI) ładujemy tylko ostatnie zapytanie sprawdzające, a następnie tworzymy dowolny raport. Może być on pełną listą kolumn lub tylko raportem błędów.
Krok 5: Testujemy błędny plik
Co się wydarzy, gdy dodamy do folderu plik o innych kolumnach? Dodając dodatkowo Indeks kolumny możemy szybko zobaczyć, które kolumny się zmieniły.
Pobierz plik z zapytaniem i zerknij do środka Power Query
Tu możesz pobrać plik Excel z gotowym raportem.
