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.
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.
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).
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.
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)
(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’.
Kolejnym krokiem będzie usunięcie wszystkich kolumn poza naszą kolumną warunkową:
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.
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.
Po rozwinięciu kolumny musimy anty scalić ją z pierwszym zapytaniem:
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.
Wynikiem scalanie będzie tylko jeden wiersz z komórką zawierającą tabelę, którą musimy rozwinąć.
Istotna jest dla nas tylko jedna kolumna, więc pozostałe usuwamy.
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