Podział tekstu ciągiem kolejnych ograniczników separatorów – Power Query #15

W tym poście zajmiemy się dzielenie tekstu za pomocą ciągu kolejnych ograniczników. W naszym przykładzie zakładamy, że najpierw dane chcemy podzielić (stworzyć podział na kolumny), gdy znajdziemy pierwszy średnik, potem przecinek i dalej zgodnie z układem danych (kolejne separatory to średnik, przecinek, pionowa linia i spacja).

Power Query 15 - Podział tekstu ciągiem kolejnych ograniczników (separatorów) - 01

Ważne, że ten podział działa jako ciąg, czyli najpierw znajdowany jest średnik (w tym miejscu wstawiany jest pierwszy podział na kolumny). Później od miejsca znalezionego średnika jest szukany przykładowy przecinek (kolejny podział na kolumny), a potem kolejne znaki, które wstawimy.

Standardowo zaczytujemy dane do Power Query i pozbywamy się kroków związanych ze zmianą typu danych w kolumnach. Kolejnym krokiem będzie wstawienie podziału kolumny po ograniczniku.

Power Query 15 - Podział tekstu ciągiem kolejnych ograniczników (separatorów) - 02

Pierwszy podział chcemy zrobić po średnik najbardziej z lewej strony.

Power Query 15 - Podział tekstu ciągiem kolejnych ograniczników (separatorów) - 03

Oczywiście to nie wszystkie podziały bo w sumie mamy 4 ograniczniki (separatory). Moglibyśmy zrobić analogiczną operację na kolejno rozdzielanych kolumnach, ale możemy zrobić to szybciej w jednym kroku, wystarczy, że mamy włączony pasek formuły w Power Query (karta Widok odpowiedni checkbox). Wtedy widzimy jako formuła (funkcja języka M została użyta do podziału na 2 kolumny po pierwszy ograniczniku (średniku). Została użyta funkcja

Power Query 15 - Podział tekstu ciągiem kolejnych ograniczników (separatorów) - 04

której możemy podać po kolei (w ciągu) kolejne ograniczniki (separatory) i później odpowiednio nadać nazwy kolumną, czyli formuła po zmianach powinna wyglądać tak:

Power Query 15 - Podział tekstu ciągiem kolejnych ograniczników (separatorów) - 05

Zauważ, że w danych specjalnie zostały popełnione błędy. Dlatego podział robi się niepoprawnie, bo Power Query musi znaleźć ograniczniki (separatory) w ciągu (w ustalonej kolejności) jeśli nie znajdzie kolejnego ogranicznika (separatora) z ciągu nie będzie szukał kolejnego, czyli nie wstawi podziału na kolumny i pojawią się puste kolumny (wypełnione wartościami ‚null’). Na szczęście jeśli poprawimy dane to nasze zapytanie poprawi wynik po jego odświeżeniu.

Można zauważyć, że w niektórych kolumnach mamy spacja na początku danych. Żeby je usunąć wystarczy dodać spacja jako tekst ogranicznika (ogranicznik/separator może być więcej niż 1 znakiem).

Prawidłowy wynik końcowy (wgrany do Excela) powinien wyglądać tak:

Power Query 15 - Podział tekstu ciągiem kolejnych ograniczników (separatorów) - 06

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

Usuwanie pierszych, ostatnich, naprzemiennych i pustych wierszy – Power Query #14

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”.

Power Query 14 - Usuwanie wierszy - 01

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)

Power Query 14 - Usuwanie wierszy - 02

Otworzy się okno, w które wpisujemy ile pierwszych wierszy chcemy usunąć (7) i zatwierdzamy wybór przyciskiem OK – zostaje nam 49 wierszy.

Power Query 14 - Usuwanie wierszy - 03

Kolejnym krokiem jest usuwanie wierszy sekwencyjnie (naprzemiennie).

Power Query 14 - Usuwanie wierszy - 04

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).

Power Query 14 - Usuwanie wierszy - 05

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.

Power Query 14 - Usuwanie wierszy - 06

Czyli wpisujemy 5 w okno usuwania ostatnich wierszy (zostaje 38).

Power Query 14 - Usuwanie wierszy - 07

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).

Power Query 14 - Usuwanie wierszy - 08

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).

Power Query 14 - Usuwanie wierszy - 09

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).

Power Query 14 - Usuwanie wierszy - 10

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).

Power Query 14 - Usuwanie wierszy - 11

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,

Power Query 14 - Usuwanie wierszy - 12

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.

Power Query 14 - Usuwanie wierszy - 13

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

Automatyczne odświeżanie zapytań – Power Query #13

Często chcemy, żeby nasze zapytania odświeżały się automatycznie, albo przynajmniej co jakiś czas. W tym wpisie zobaczysz jakie możliwości są w samych połączeniach/zapytaniach (w poradzie VBA 22 zobaczysz kod VBA do podpięcia pod przycisk, który odświeża wszystkie zapytania).

Pierwszą możliwością jak możesz się dostać do właściwości połączenia jest kliknięcie prawym przycisku na jego wyniku w arkuszu Excela, rozwinięciu w podręcznym menu pozycji Tabela i wybraniu Właściwości danych zewnętrznych.

PQ 13 - Automatyczne odświeżanie zapytań 01

Otworzy się okno właściwości danych zewnętrznych, gdzie możesz zmienić właściwości formatowania danych oraz ich zachowanie podczas odświeżania danych.

PQ 13 - Automatyczne odświeżanie zapytań 02

Możesz w tym oknie kliknąć ikonkę, która Cię przeniesie do okna właściwości połączenia, gdzie znajdują się opcje bardziej nas interesujące.

PQ 13 - Automatyczne odświeżanie zapytań 03

Można tam między innymi zaznaczyć checkboxa, który będzie automatycznie odświeżał zapytania przy otwieraniu pliku lub po określonym czasie.

Do właściwości połączenia możesz dostać się też przez polecenia karty Dane.

PQ 13 - Automatyczne odświeżanie zapytań 04

Tylko, żeby zobaczyć właściwości połączenia, które umożliwiają odświeżanie automatyczne przy otwieraniu pliku (rys. 3) będziesz musiał je jeszcze wybrać z listy po naciśnięciu polecenia Połączenia z karty Dane.

PQ 13 - Automatyczne odświeżanie zapytań 05

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

Odświeżanie zapytań PowerQuery za pomocą VBA #22

W tym wpisie chcemy napisać kod VBA, który odświeża wszystkie zapytania albo tylko pojedyncze zapytanie PowerQuery. Odświeżanie wszystkich zapytań jest prostsze, ponieważ możesz nagrać ten kod sam i później podpiąć go do przycisku.
Najpierw musimy włączyć rejestrowanie makr – polecenie Zarejestruj makro z karty Deweloper.

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 01

W oknie, które się otworzy

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 02

nadajesz nazwę makru (np.: Refresh), ewentualnie podpinasz skrót klawiszowy. To trochę bez sensu ponieważ polecenie odśwież wszystko z karty Dane (rys. 3) ma już przypisany do siebie skrót klawiszowy (Ctrl + Alt + F5). Upewniasz się, że makro zostanie zapisane do tego skoroszytu i naciskasz klawisz OK. Teraz ważne, żebyś nie klikał nigdzie w komórki arkusza, tylko od razu przeszedł do karty Dane i kliknął polecenie odśwież wszystko.

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 03

Teraz możesz już wyłączyć rejestrację makra – polecenie Zatrzymaj rejestrowanie z karty Deweloper.
VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 04

Teraz nawet bez zaglądania do kodu makra możesz wstawić dowolny kształt lub obraz i kliknąć na niego prawym przyciskiem myszy, by z podręcznego menu wybrać opcję przypisz makro.

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 05

Następnie z listy dostępnych makr wybierasz to, które zarejestrowałeś.

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 06

I już możesz odświeżać wszystkie zapytania PowerQuery (oraz tabele przestawne i inne połączenia) za pomocą kliknięcia w ‘przycisk’ (kształt/obraz).

Cały kod makra wygląda tak:

Jeśli chciałbyś odświeżać tylko pojedyncze zapytanie to trudniejsze zadanie i rejestrator makr Ci raczej w tym nie pomoże (patrz film), za to możesz skorzystać z kodu poniżej.

Ważne, że do nazwy zapytania dodajesz prefiks „Zapytanie — ” (w innych język ten prefiks jest inny), a i myślnik nie jest standardowy, więc najlepiej, żebyś skopiować cały kod VBA (w Excelu ten myślnik ma KOD = 151).

Na koniec jeszcze pętla (jaką możesz znaleźć na różnych portalach w internecie), która pomoże Ci odświeżyć wszystkie zapytania PowerQuery w pliku (zmieniłem tylko prefiks na polski):

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

Jak usunąć grupy wierszy, które kończy konkretny tekst – kolumny indeksów – Power Query #12

Wykonamy ponownie usuwanie z danych niepotrzebnych bloków wierszy jak w PQ 11, tylko tym razem zrobimy to prościej – głównie za pomocą kolumn indeksów i sprawdzaniu prostych warunków.
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.

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 01

Standardowo pobieramy dane z tabeli Excela do zapytania PowerQuery i kasujemy Zmieniono typ, ponieważ jest nam niepotrzebny w tym przykładzie. Następnie dodajemy kolumnę indeksu (od jakiej wartości zaczynamy liczenie jest drugorzędne).

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 02

Następnie za pomocą kolumny warunkowej (polecenie z karty Dodaj kolumnę) sprawdzamy kiedy pojawia się ostatni wiersz zbędnego bloku, czyli ten, który zawiera tekst – ‘Koniec Niepotrzebnego bloku’. Musimy następująco wypełnić pola w oknie wstawiania kolumny warunkowej:

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 03

Teraz potrzebujemy wypełnić tą kolumnę w górę (polecenie z karty Przekształć).

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 04

Wtedy każda liczba będzie kopiowana w górę, zastępując wartości null, dopóki nie dojdzie do kolejnej liczby. Teraz musimy sprawdzić czy wartość z kolumny Indeks jest większa niż wartość z kolumny Custom (tej przed chwilą wypełnianej w górę) pomniejszona o 5. Możemy to zrobić dodając kolumnę niestandardową (polecenie z karty Dodaj kolumnę). Nazwa kolumny jest nieistotna bo za chwilę ją skasujemy, a formułą prosta 😉

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 05

Teraz wystarczy odfiltrować wiersze z wartościami TRUE.

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 06

A następnie usunąć wszystkie kolumny poza kolumną Nagłówek, bo służyły one tylko temu, żeby odfiltrować dla nas zbędne wiersze – wystarczy kliknąć prawym przyciskiem myszy w nagłówek kolumny Nagłówek.

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 07

A później możemy już wczytać zapytanie do Excela.

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