Dzisiaj chce porozmawiać o usuwaniu niepotrzebnych wierszy w Power Query, bo niedawno musiałem sporo zbędnych informacji (wierszy) usunąć zanim przeszedłem do analizy danych.
Mamy odpowiednio spreparowaną tabelę, która pomoże nam w poznaniu funkcjonalności usuwania wierszy w Power Query. Główne jej założeniem jest, że są to dane eksportowane z naszego głównego programu, który dzieli informacje na strony. Każda strona ma określoną ilość wierszy (15, żeby nie było ich za dużo w przykładzie 😉 ).Na każdej stronie są 2 linijki stopki. Cały dokument na wiersze Tytułu (zaczynające dokument/dane) oraz wiersze z informacjami końcowymi kończące dokument, które są nam zbędne.
Mamy jeszcze kilka pustych wierszy, zduplikowanych wierszy, i wierszy z "błędami".
Wczytujemy dane z tabeli do Power Query i zaczynamy usuwanie zbędnych wierszy (Na początku mamy 56 wierszy). Rozwijamy polecenie Usuń wiersze na karcie Narzędzia główne i w pierwszej kolejności usuwamy pierwsze wiersze (jest ich 7)
Otworzy się okno, w które wpisujemy ile pierwszych wierszy chcemy usunąć (7) i zatwierdzamy wybór przyciskiem OK – zostaje nam 49 wierszy.
Kolejnym krokiem jest usuwanie wierszy sekwencyjnie (naprzemiennie).
Mogliśmy to zrobić równie dobrze jako pierwszy krok, tylko musielibyśmy odpowiednio policzyć, kiedy chcemy zacząć usuwanie. Patrząc na nasze dane, po tym jak usunęliśmy 7 pierwszych wierszy tytułu, to chcemy zacząć usuwanie od 7 wiersza, usunąć 2 wiersze, a następnie zachować kolejne 13 wierszy (przypominam strona ma 15 linijek).
Po pierwszym usunięciu wiersze dalej są usuwane sekwencyjnie (naprzemienne), czyli, jak usunęli 2 wiersz od 7 linijki (7 i 8 wiersz) następnie zachowaliśmy 13 wierszy, to jeśli jeszcze nie doszliśmy do ostatniego wiersze, to znowu usuwamy 2 wiersze i zachowujemy 13 i tak aż do końca danych. Zostaje nam 43 wiersze.
Teraz usuwamy ostatnie wiersze.
Czyli wpisujemy 5 w okno usuwania ostatnich wierszy (zostaje 38).
Kolejną operacją jaką chcemy wykonać jest usuwanie pustych wierszy, czyli takich wierszy, gdzie we wszystkich wierszach występuje wartość null. Teraz są to wiersze nr 10 i 11. Wiersz 8 ma wartość null tylko w drugiej kolumnie, więc nie zostanie usunięty (zostaje 36 wierszy).
Jeśli chcielibyśmy usunąć wiersze z wartością null w kolumnie NrWierszy, to najprościej byłoby je odfiltrować i tak najprościej postąpić w sytuacji w której w danej kolumnie są wartości dla których chcemy usunąć całe wiersze (zostaje 35 wierszy).
Teraz chcemy usunąć błędy, tylko wartości w drugiej kolumnie podpisane jako 'błąd' nie są faktycznie błędem tylko tekstem. Zakładamy, że jeśli drugiej kolumnie występuje tekst, a nie liczba, to ten wiersz chcemy usunąć. Najprościej jest zmienić typ danych dla drugiej kolumny na liczbę (w tym przykładzie całkowitą). Ponieważ tekstów nie da się zamienić na liczbę Power Query w ich miejsce wstawia informację o błędzie (Error) i teraz możemy usunąć błędy. Musimy tylko pamiętać, że ma być zaznaczona kolumna, która zawiera błędy, które chcemy usunąć (zostaje 30 wierszy).
Została nam ostatnia operacja usuwania duplikatów. Przy niej musimy bacznie zwracać uwagę ile i które wiersze mamy zaznaczone, gdyż wiersz będzie uznany za duplikat, kiedy wcześniej istniał wiersz, który miał takie same wartości we wszystkich zaznaczonych kolumnach.
Jeśli w naszym przykładzie zaznaczymy tylko pierwszą kolumnę (Dane zaimportowane).
To tylko ta kolumna będzie przy sprawdzaniu duplikatów, więc, jeśli jakaś wartość się w niej powtórzy (wcześniej już był wiersz, który miał taką samą wartość w kolumnie Dane zaimportowane), to zostanie usunięty cały wiersz. Po tym zostałoby nam tylko 15 wierszy (jeśli tak zrobiłeś usuń ostatni krok zapytania).
My szukamy duplikatów po obu wierszach, a identyczne wartości w obu wierszach mamy tylko w wierszach 27 i 28,
więc usuwamy te duplikaty po obu kolumnach i zostaje nam 29 wierzy. Teraz możemy je załadować do Excela.
P.S. Jak mogliśmy na podane powyżej sposoby usuwać wiersze, możemy je też zachować. Zachowujemy tylko wiersze wskazane. Pozostałe są usuwane.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP