Raport CSV z wierszami nad nagłówkiem i podsumowaniem na końcu - jak je usunąć

Power Query w Excelu, Power BI i Fabric

Raport CSV z wierszami nad nagłówkiem i podsumowaniem na końcu - jak je usunąć

W skrócie

Usuwanie górnych wierszy w Power Query to pierwszy krok przy raportach CSV z systemów kasowych i księgowych, które nad nagłówkiem dopisują tytuł i okres, a na końcu wiersz z sumą. Pokazujemy, dlaczego stała liczba w kroku usuwania pierwszych wierszy przestaje działać, gdy układ pliku się zmienia, i jak zastąpić ją warunkiem. Przykłady opieramy na eksportach sprzedaży fikcyjnej firmy Nordvella z kursu, a każdy wariant sprawdziliśmy w polskim Excelu.

Jak to wygląda w praktyce

Eksport z systemu kasowego ma nad właściwym nagłówkiem dwa wiersze tytułu i pusty wiersz, a na końcu pusty wiersz i wiersz Razem z sumą. W kursie usuwamy je trzema krokami: Usuwanie pierwszych wierszy z liczbą 3, Użyj pierwszego wiersza jako nagłówków i filtr odrzucający puste wiersze oraz wiersz Razem. To działa, dopóki każdy plik ma ten sam układ.

Gdy w kolejnym eksporcie nad nagłówkiem pojawi się dodatkowy wiersz, na przykład z datą wygenerowania raportu, ten sam krok usuwa o jeden wiersz za mało. Nagłówki pochodzą wtedy z pustego wiersza: w naszym teście pierwsza kolumna dostała pustą nazwę, a kolejne nazwy _1, _2 i _3. Filtr kolumny Data zgłosił Expression.Error: Nie można znaleźć pola „Data” w rekordzie. (ang. The field 'Data' of the record wasn't found.), a przy łączeniu plików z folderu taki błąd zatrzymuje całe zapytanie główne. Druga odmiana problemu siedzi na dole: nowa stopka pod wierszem Razem, na przykład wiersz z nazwą systemu, przechodzi przez filtr i trafia do danych.

Dlaczego tak się dzieje

Okno Usuwanie pierwszych wierszy ma jedno pole, Liczba wierszy, i zapisuje ją w kroku Usunięto pierwsze wiersze na stałe, na przykład Table.Skip(Źródło, 3). Przy odświeżeniu Power Query nie sprawdza, co jest w tych wierszach, tylko odcina trzy pierwsze. Tak samo zachowuje się usuwanie końcowych wierszy i filtr jednej konkretnej wartości: oba opisują wygląd pliku z dnia, w którym budowano zapytanie.

Funkcja Table.Skip ma jednak drugi tryb. Opis funkcji podaje, że gdy zamiast liczby dostanie warunek, pomija wiersze spełniające ten warunek aż do pierwszego, który go nie spełnia. Tak samo od końca tabeli działa Table.RemoveLastN. Warunek oparty na treści, na przykład „pomijaj, dopóki pierwsza kolumna nie zawiera tekstu Data”, dopasowuje się do każdego pliku. Pamiętaj też, że pusty wiersz pliku CSV daje w Power Query pusty tekst, a nie wartość null, co widać w warunkach poniżej.

Jak to rozwiązać krok po kroku

1. Kliknij krok Usunięto pierwsze wiersze, a przy łączeniu plików z folderu zrób to w zapytaniu Przekształć przykładowy plik. W pasku formuły zobaczysz = Table.Skip(Źródło, 3).
2. Zamień liczbę na warunek: = Table.Skip(Źródło, each [Column1] <> "Data") i zatwierdź Enterem. Krok pominie wszystkie wiersze aż do tego, w którym pierwsza kolumna zawiera nazwę pierwszego nagłówka. W naszym teście dał poprawne nagłówki Data, Nr paragonu, Sklep i Ilość w pliku z trzema i z czterema wierszami nad nagłówkiem. Krok Nagłówki o podwyższonym poziomie zostaw bez zmian.
3. Dół tabeli obsłuż warunkiem od końca. Na karcie Strona główna rozwiń Usuń wiersze, wybierz Usuwanie końcowych wierszy, wpisz 2 i kliknij OK. W pasku formuły kroku Usunięto ostatnie wiersze zamień liczbę na warunek: each [Data] = "" or [Data] = "Razem". Wiersze znikają od dołu, dopóki spełniają warunek, więc dane w środku tabeli zostają nietknięte.
4. Najodporniejszy filtr opisuje to, co ma zostać, a nie to, co trzeba usunąć. Kliknij prawym przyciskiem krok Nagłówki o podwyższonym poziomie, wybierz Wstaw krok po i wpisz w pasku formuły = Table.SelectRows(#"Nagłówki o podwyższonym poziomie", each (try Date.FromText([Data], [Format = "dd.MM.yyyy"]) otherwise null) <> null). Zostają tylko wiersze z poprawną datą, więc znikają puste wiersze, wiersz Razem i każda nowa stopka. W naszym teście z pliku z dodatkową stopką zostały dokładnie dwa wiersze danych.
5. Uważaj na plik, w którym nie ma szukanego nagłówka. Gdy dostawca zmieni nazwę pierwszej kolumny, na przykład na Data sprzedaży, warunek z kroku 2 pominie cały plik: w naszym teście wynik miał zero wierszy i kolumny Column1 do Column4, bez żadnego błędu. Po każdej zmianie układu eksportu sprawdzaj więc liczbę wierszy z każdego pliku.
6. Ustaw typy w zapytaniu głównym i porównaj sumę kontrolną z wierszem Razem. W naszym teście suma kolumny Ilość po poprawce wyniosła 4, tyle samo co w wierszu Razem pliku źródłowego.

Jak sprawdzić, że zadziałało

Porównaj sumę kluczowej kolumny z wierszem Razem w pliku źródłowym, osobno dla kilku plików. Przy łączeniu folderu pogrupuj wynik po Source.Name poleceniem Grupowanie według i policz wiersze: każdy plik powinien mieć ich tyle, ile linii danych, bez wierszy tytułowych i stopek. Otwórz też filtr kolumny Data i sprawdź, czy na liście wartości nie ma tekstów Razem, Okres ani pustych pozycji.

Żeby problem nie wrócił, przetestuj zapytanie na pliku o zmienionym układzie, zanim takie pliki zaczną przychodzić naprawdę. Skopiuj jeden eksport, dopisz w nim wiersz nad nagłówkiem i linię pod wierszem Razem, wrzuć kopię do folderu i odśwież wynik. Warunek w Table.Skip i filtr po dacie powinny obsłużyć ją bez zmian w krokach. Kopię usuń po sprawdzeniu.

Wróć do listy: 88 najczęstszych pytań i problemów związanych z Power Query

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5