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).
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.
Pierwszy podział chcemy zrobić po średnik najbardziej z lewej strony.
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
Splitter.SplitTextByEachDelimiter
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:
= Table.SplitColumn(Źródło, "Dane", Splitter.SplitTextByEachDelimiter({";", ",", "|", " "}, QuoteStyle.Csv, false), {"Imię i Nazwisko", "Miejsce urodzenia", "Adres", "Kod pocztowy", "Miasto"})
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:
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
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
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.
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.
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.
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.
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.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
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.
W oknie, które się otworzy
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.
Teraz możesz już wyłączyć rejestrację makra – polecenie Zatrzymaj rejestrowanie z karty Deweloper.
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.
Następnie z listy dostępnych makr wybierasz to, które zarejestrowałeś.
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:
Sub Refresh()
ActiveWorkbook.RefreshAll
End Sub
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.
Sub Makro1()
ActiveWorkbook.Connections("Zapytanie — tProdukty_k").Refresh
End Sub
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):
Sub Makro1()
Dim con As WorkbookConnection
Dim Cname As String
For Each con In ActiveWorkbook.Connections
If Left(con.Name, 12) = "Zapytanie — " Then
Cname = con.Name
With ActiveWorkbook.Connections(Cname).OLEDBConnection
.BackgroundQuery = False 'or true, up to you
.Refresh
End With
End If
Next
End Sub
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
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.
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).
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:
Teraz potrzebujemy wypełnić tą kolumnę w górę (polecenie z karty Przekształć).
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 😉
=[Indeks] > [Custom] – 5
Teraz wystarczy odfiltrować wiersze z wartościami TRUE.
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.
A później możemy już wczytać zapytanie do Excela.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
