0
0 Produkty w koszyku

No products in the cart.

Jak usunąć grupy wierszy, które kończy konkretny tekst — anty scalanie — PowerQuery #11

Czasami potrzebujemy usunąć z danych niepotrzebne bloki wierszy. Przeważnie takie bloki zaczynają się lub kończą konkretnym wierszem (zawierającym konkretną unikalną frazę). W tym wpisie rozpatrzymy sytuację, gdzie znamy wiersz kończący zbędne dla nas informacje w danych oraz wiemy, że blok niepotrzebnych informacji ma zawsze 5 wierszy i w tych wierszach nie ma nigdy takich samych wartości jak w poprawnych wierszach.

PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (1)

W tym wpisie głównym krokiem z jakiego skorzystamy będzie anty scalanie zapytań (czyli będziemy wykluczać wiersze, które się powtarzają w zapytaniach), a w następnym skupimy się na sprawdzaniu kolumn indeksów. Dziś prezentowane rozwiązanie jest trudniejsze od prezentowanego w PQ12, ale każdy sposób rozwiązania problemu nawet ten, który nie jest optymalny, może Cię czegoś nauczyć, a tym samym pomóc rozwiązać inny problem.

Standardowo pobieramy dane z tabeli Excela do zapytania PowerQuery i kasujemy Zmieniono typ, ponieważ jest nam niepotrzebny w tym przykładzie. Następnie potrzebujemy sobie rozwinąć listę zapytań z lewej strony Edytora zapytań i kliknąć we wczytane przez nas zapytanie, żeby stworzyć do niego odwołanie.

PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (2)

Odwołanie, czyli zapytanie, które będzie patrzeć na koniec (ostatni krok) zapytania, do którego się odwołujemy, czyli pierwszego zapytania, które wczytaliśmy do PowerQuery (w pewnym momencie będziemy anty scalać ze sobą ba zapytania).
W pierwszej kolejności do drugiego zapytania potrzebujemy dodać kolumnę Indeksu (musimy przy tym pamiętać, że PowerQuery ma indeksowanie – zaczyna liczyć wiersze, od zera).

PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (3)

Kolejnym krokiem będzie dodanie kolumny, która będzie pobierać numer z kolumny Indeksu jeśli w głównej kolumnie odnajdzie wiersz kończący niepotrzebną frazę (tekst – ‘Koniec Niepotrzebnego bloku’). W przeciwnym razie ma nic nie zwracać. Najłatwiej będzie to zrobić dodając kolumnę warunkową (poleceniem z karty Dodaj kolumnę). Następnie odpowiednio musimy uzupełniać pola okna Dodawania kolumny warunkowej.

PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (4)

Tak naprawdę zależy nam na tym, żeby zwracać numer pierwszego wiersza niepotrzebnych bloków, a nie ostatniego, ale okno dodawania kolumny warunkowej nam tego nie umożliwia. Dlatego potrzebujemy zmodyfikować formułę (musi być zaznaczony checkbox Pasek formuły z karty Widok), czyli od Indeksu odjąć 4. Cała formuła po tej zmianie powinna wyglądać tak:

= Table.AddColumn(#"Dodano indeks", "Custom", each if [Nagłówek] = "Koniec Niepotrzebnego bloku" then [Indeks] - 4 else null)

PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (5)

(Koło zębate, po tej zmianie, przestanie przenosić nas do okno dodawania kolumny warunkowej. Zamiast do niego będzie nas przenosić do okno dodawania kolumny niestandardowej).

Potrzebujemy tylko wierszy, które zawierają początkowy numer wiersza, więc odfiltrowujemy wszystkie wiersze z wartością ‘null’.

PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (6)

Kolejnym krokiem będzie usunięcie wszystkich kolumn poza naszą kolumną warunkową:

PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (7)

Teraz najtrudniejszy krok ponieważ potrzebujemy dodać niestandardową formułę (kolumnę) do danych, która będzie wyciągała wiersze z pierwszego zapytania na podstawie numerów wierszy (przypominam PowerQuery zaczyna liczyć od zera), które wyciągnęliśmy przed chwilą. 

Zakładając, że kolumna z wierszami nazywa się Custom, a pierwsze zapytanie nazywa się ‘tDane’, formuła i chcemy wyciągnąć 5 wierszy, formuła którą wpiszemy przy dodawaniu kolumny niestandardowej (polecenie z karty Dodaj kolumnę), powinna wyglądać tak.

=Table.Range(tDane,[Custom],5)

Do każdego numeru wiersza powinna się dodać tabela, która będzie wyciągać niepotrzebne wiersze z pierwszego zapytania. 

PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (8)

Musimy tę kolumnę rozwinąć klikając w ikonę strzałek wskazujących w przeciwne strony z nagłówka kolumny. Ważne, że chcemy mieć zaznaczoną opcję rozwijania. Drugorzędne jest to, że możemy odznaczyć pole wyboru (checkbox) Użyj oryginalnej nazwy kolumny jako prefiksu.

PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (9)

Po rozwinięciu kolumny musimy anty scalić ją z pierwszym zapytaniem:

PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (10)

Wybieramy odpowiednie zapytania u góry i u dołu oraz klikamy w kolumny nagłówków na podstawie, których będziemy anty scalać zapytania. Najważniejsze, że jako rodzaj sprzężenia wybieramy Prawe anty, czyli zostaną usunięte wszystkie wiersze mające taką samą wartość w jednym zapytania, a zostają tylko takie, które się nie powtarzały z prawego (drugiego/dolnego) zapytania.

PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (11)

Wynikiem scalanie będzie tylko jeden wiersz z komórką zawierającą tabelę, którą musimy rozwinąć.

PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (12)

Istotna jest dla nas tylko jedna kolumna, więc pozostałe usuwamy.
PQ 11 - Jak usuwać grupy wierszy, który kończy konkretny tekst (13)

Teraz możemy załadować nasze zapytania. Ze względu na to, że edytowaliśmy dwa na raz, a tylko jedno chcemy załadować do Excela, to ja wolę najpierw załadować je tylko jako połączenie, a dopiero później drugie zapytanie (to z interesującymi nas wierszami) załadować w konkretne miejsce Excela (rys. 1).

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP