Bezpłatny kurs Power Query: czyszczenie danych - wiersze techniczne, nagłówki, typy i duplikaty

W skrócieEksport z systemu prawie nigdy nie nadaje się do raportu bez czyszczenia: ma wiersze tytułowe, sumy, niespójny tekst i duplikaty. W tej lekcji usuwamy wiersze techniczne, ustawiamy nagłówki i typy danych, poprawiamy wielkość liter i spacje oraz usuwamy powtórzone wiersze.

Usuń wiersze techniczne, ustaw nagłówki i typy danych

Kliknij w panelu zapytań Przekształć przykładowy plik. Wszystkie kroki z tej lekcji robimy na nim, dopóki nie napiszemy inaczej.

Usuń trzy pierwsze wiersze

1. Na karcie Strona główna rozwiń Usuń wiersze i wybierz Usuwanie pierwszych wierszy. To samo menu ma też usuwanie końcowych i naprzemiennych wierszy, duplikatów, pustych wierszy oraz wierszy z błędami.

2. W oknie Usuwanie pierwszych wierszy wpisz 3 (dwa wiersze tytułu i jeden pusty) i kliknij OK.

Użyj pierwszego wiersza jako nagłówków

3. Teraz w pierwszym wierszu stoją nazwy kolumn. Kliknij Użyj pierwszego wiersza jako nagłówków na karcie Strona główna (ten sam przycisk jest też na karcie Przekształć). Kolumny dostaną nazwy Data, Nr paragonu, Sklep, Kod produktu, Ilość, Cena netto, Rabat %, Wartość netto, Kanał i Płatność. Strzałka obok tego przycisku ma też operację odwrotną, która przenosi nagłówki z powrotem do pierwszego wiersza danych. Przydaje się, gdy źródło ma dwa wiersze nagłówków i chcesz je najpierw scalić w jeden.

Power Query po promowaniu nagłówków sam dodaje krok Zmieniono typ, zgadując typy kolumn. W zapytaniu pomocniczym ten krok szkodzi: typy ustawimy raz, na połączonej tabeli. Usuń go krzyżykiem na liście Zastosowane kroki.

Odfiltruj wiersz Razem i puste wiersze

4. Na końcu każdego pliku jest pusty wiersz i wiersz z sumą. Kliknij strzałkę przy nagłówku kolumny Data i wybierz Usuń puste. Potem jeszcze raz otwórz filtr, wejdź w Filtry tekstu i wybierz Nie równa się....

5. W oknie Filtrowanie wierszy wpisz Razem w polu wartości obok warunku nie równa się i kliknij OK.

Filtr zależy od typu kolumny. Kolumna tekstowa ma Filtry tekstu, liczbowa Filtry liczb (większe niż, między, pierwsze N), a kolumna z typem daty ma Filtry dat z warunkami względnymi, na przykład w poprzednim lub bieżącym miesiącu albo w ostatnich N dniach. Filtr względny przesuwa się sam przy każdym odświeżeniu.

Dlaczego filtrujemy wartość, a nie usuwamy ostatnich wierszy? Bo w kolejnym eksporcie podsumowanie może zajmować dwa wiersze albo pojawić się dodatkowa stopka. Filtr po treści jest odporny na takie zmiany, a usunięcie stałej liczby wierszy z końca nie jest.

Wróć do zapytania głównego i napraw błąd kolumny Column1

6. Kliknij zapytanie Sprzedaz_miesieczna. Zamiast danych zobaczysz żółty pasek z błędem. To najczęstszy błąd przy łączeniu plików i dobrze zobaczyć go od razu:

Krok Zmieniono typ w zapytaniu głównym powstał w chwili łączenia plików, gdy kolumny nazywały się jeszcze Column1, Column2. Po naszych zmianach w zapytaniu przykładowym nazwy są inne i krok wskazuje kolumny, które nie istnieją. Rozwiązanie: usuń ten krok krzyżykiem. Typy ustawimy na nowo.

Ustaw typy danych

7. Zaznacz wszystkie kolumny (kliknij nagłówek pierwszej kolumny i naciśnij Ctrl+A), przejdź na kartę Przekształć i kliknij Wykryj typ danych. Power Query przejrzy wartości i nada typy: data dla kolumny Data, liczby całkowite dla Ilość i Rabat %, liczby dziesiętne dla cen i wartości, tekst dla reszty.

Typ danych to w Power Query coś więcej niż format. Od typu zależy, czy kolumnę da się zsumować, czy filtry pokażą zakres dat, czy scalanie po kluczu zadziała i czy model w Power BI policzy miary. Pełną listę typów zobaczysz po kliknięciu ikony typu przy nazwie kolumny:

Popraw tekst i usuń duplikaty

Nazwy sklepów w eksporcie bywają zapisane z doklejonymi spacjami albo wielkimi literami („NORDVELLA WROCŁAW"), a kody produktów czasem małymi literami ze spacją na końcu („bex-1021 "). Dla człowieka to ten sam sklep i ten sam produkt, dla Power Query i dla tabeli przestawnej to różne wartości. Poprawiamy to w zapytaniu Sprzedaz_miesieczna.

1. Zaznacz kolumnę Sklep, na karcie Przekształć rozwiń Format i wybierz Przycięcie, a potem jeszcze raz Format > Zamień pierwszą literę każdego wyrazu na wielką.

2. Zaznacz kolumnę Kod produktu i zastosuj Format > Przycięcie oraz Format > Wielkie litery. Kody mają teraz jeden zapis, co jest warunkiem poprawnego scalenia z katalogiem produktów w następnym kroku.

3. Zostały duplikaty. W lutowym eksporcie system kasowy wysłał sześć linii paragonów dwa razy. Zaznacz wszystkie kolumny (Ctrl+A po kliknięciu nagłówka), na karcie Strona główna rozwiń Usuń wiersze i wybierz Usuń duplikaty.

Ile duplikatów zniknęło? Wszystkie cztery pliki mają razem 9256 linii, a po usunięciu duplikatów zostaje 9250. Liczbę wierszy pokazuje profil kolumny, który włączymy w lekcji o profilowaniu danych.

Inne operacje na tekście

Kilka operacji, których nie potrzebowaliśmy w danych Nordvelli, a które przy eksportach z systemów są codziennością:

• Scal kolumny (karta Przekształć) łączy kilka kolumn tekstowych w jedną, z wybranym separatorem, na przykład imię i nazwisko albo ulicę i numer domu. Na karcie Dodaj kolumnę ta sama operacja zostawia kolumny źródłowe bez zmian.
• Wyodrębnij wyciąga fragment tekstu: pierwsze lub ostatnie znaki, zakres od wskazanej pozycji, długość tekstu, a także tekst przed, po albo między ogranicznikami, na przykład domenę z adresu e-mail.
• Przenieś zmienia kolejność kolumn (na początek, na koniec, w lewo, w prawo). Kolejność możesz też zmienić, przeciągając nagłówek kolumny myszą.
• Twarde spacje. Dane kopiowane ze stron WWW i z PDF-ów często zawierają spację niełamliwą między słowami. Przycięcie usuwa ją tylko z początku i końca tekstu, więc „Nordvella Wrocław" ze spacją niełamliwą w środku dalej nie pasuje do słownika. Pomaga zamiana w kolumnie niestandardowej: Text.Replace([Sklep], "#(00A0)", " "), gdzie #(00A0) to zapis znaku spacji niełamliwej w języku M.
• Zera wiodące. Kod, który system zapisał jako liczbę 123, a słownik jako tekst 000123, nie scali się. Zamień kolumnę na tekst i uzupełnij zera formułą Text.PadStart(Text.From([Kod]), 6, "0").

Zamienianie wartości, wypełnianie w dół i transpozycja

Trzy kolejne narzędzia przydają się przy prawie każdym eksporcie, choć w danych Nordvelli akurat nie były potrzebne:

• Zamienianie wartości (karta Strona główna albo Przekształć) podmienia jedną wartość na inną w zaznaczonych kolumnach, na przykład „Telefon" na „Infolinia". Pozostawienie pustego pola Zamień na przy tekście zamienia go na pusty tekst, a wpisanie null na wartość pustą. W opcjach zaawansowanych zaznaczysz dopasowanie całej zawartości komórki, żeby „Kraków" nie zamieniło się wewnątrz „Kraków Galeria".
• Wypełnij > W dół (karta Przekształć) uzupełnia puste komórki wartością z wiersza powyżej. To lekarstwo na raporty, w których nazwa regionu czy działu stoi tylko w pierwszym wierszu grupy. Uwaga: wypełniane są wyłącznie wartości puste (null), więc jeśli komórki zawierają pusty tekst, najpierw zamień go na null.
• Transponuj (karta Przekształć) zamienia wiersze z kolumnami. Przydaje się przy nagłówkach rozpisanych w pionie i przy tabelach odwróconych na potrzeby wydruku.

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

✕