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

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:

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.

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

Jak pobrać wartość z komórki Excela do zapytania PowerQuery #10

Naszym zadaniem na dziś jest pobranie wartości z komórki Excela jako warunek do zapytania PowerQuery. Bezpośrednio nie jest to możliwe, ale można pobrać dane z tabeli, a później wyszczególnić komórkę, którą nam chodzi.
Mamy przygotowaną tabelę z prostymi warunkami – chcemy ograniczyć wyniki zapytania do tylko tych wskazanych w tabeli z warunkami.

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 01

Mamy na to dwa podstawowe sposoby – albo będziemy pobierać wynik jednego zapytania do drugiego zapytania, albo dołożyć odpowiedni kod M do głównego zapytania.

Zaczniemy od prostszego sposobu odwoływania się do wyników zapytań. W pierwszej kolejności pobierzemy dane z tabeli warunków (standardowo za pomocą polecenia PowerQuery z tabeli).

Nie będzie nam potrzebny krok Zmieniania typów (domyślnie dodawany przez PowerQuery), więc go usuwamy. Następnie będziemy potrzebować zduplikować zapytanie, czyli rozwijamy listę zapytań z lewej strony edytora zapytań i klikamy prawym przyciskiem na zapytanie, a następnie wybieramy opcję Duplikuj z listy rozwijanej (możemy jeszcze ewentualnie zmienić nazwę zapytania na takie, które się nam bardziej podoba 😉 ).

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 02

Następnie potrzebujemy wyszczególnić interesujące nas warunki, czyli klikamy prawym przyciskiem myszy na komórkę, która nas interesuje i wybieramy odpowiednią opcję z podręcznego menu.

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 03

Dla obu zapytań wykonujmy analogiczne przejście do szczegółów i możemy po nim zauważyć, że nasze zapytania przestają mieć formę tabeli – zmieniają się na wartość.

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 04

Przy tym kroku ważne jest to, że PowerQuery ma indeksowanie (liczenie wierszy) od zera (pamiętaj o zaznaczeniu checkboxa Pasek formuły na karcie Widok). Przykładowo (rys. 4) wyszczegółowiliśmy wartość Ciasteczka, która znajdują się w pierwszym wierszu ({0}) kolumny Produktu kroku Źródło – zostało to zapisane za pomocą formuły.

Potrzebujemy nasze zapytania załadować nasze zapytania tylko do pamięci PowerQUery, więc rozwijamy polecenie Załaduj do i wybieramy odpowiednie opcje.

Teraz pobieramy do nowego zapytania dane z tabeli, którą będziemy chcieli filtrować. Potrzebujemy poprawić domyślny krok Zmieniono typ, ponieważ PowerQuery, źle rozpoznał typ danych dla kolumny Data (wybrał Data i czas, zamiast samej daty). Najprościej kliknąć ikonkę w nagłówku kolumny lewym przyciskiem myszy i wybrać odpowiedni typ z podręcznego menu.

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 05

Teraz filtrujemy kolumnę Sprzedawca po dowolnym sprzedawcy (np.: po Janie).

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 06

W stworzonej formule (patrz pasek formuły dla kroku filtrowania):

= Table.SelectRows(#”Zmieniono typ”, each ([Sprzedawca] = „Jan”))

Potrzebujemy zmienić wartość wpisaną na stałe („Jan”) na odwołanie do odpowiedniego zapytania – wystarczy wstawić w formule jego nazwę (pamiętając o tym, że PowerQuery zwraca uwagę na wielkość liter):

= Table.SelectRows(#”Zmieniono typ”, each ([Sprzedawca] = tWarunki))

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 07

Analogicznie robimy dla drugiej kolumny – filtrujemy po dowolnym produkcie, a później podmieniamy wartość w formule na odwołanie do wyniku zapytania.

Potem możemy załadować zapytanie do arkusza Excela i zobaczyć, że jego wynik zmienia się w zależności od wartości w tabeli warunków.

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 08

Chcemy jeszcze omówić rozwiązanie, które bardziej modyfikuje język M (w pierwszym modyfikowaliśmy tylko pojedyncze formuły). Ponieważ nie zawsze chcemy generować tyle zapytań przechowujących warunku/parametry, które wykorzystujemy.

Musimy wrócić do edytora zapytań (np.: edytując załadowane przez nas zapytanie).Będziemy je chcieli zduplikować (rys. 2).
Teraz potrzebujemy zobaczyć cały kod języka M dla jednego z zapytań pobierających warunki. Zaznaczamy je i z karty Narzędzia główne wybieramy Edytor zaawansowany.

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 09

Zobaczymy tam kod wszystkich kroków zapytania. Standardowo zapytanie rozpoczyna słowo kluczowe let. Każda linijka zakończona przecinkiem, to krok zapytania. Tylko ostatni krok nie jest zakończony przecinkiem. Po nim pojawia się słowo kluczowe in i nazwa kroku, które zwraca zapytanie. Domyślnie kolejny krok odwołuje się do wyniku wcześniejszego kroku przez jego nazwę.

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 10

Potrzebujemy stąd (rys. 10) skopiować kod 2 kroków. Następnie przechodzimy do edytora zaawansowanego nowego zapytania filtrującego dane. Wklejamy te dwie linijki zaraz po słowie kluczowym let, a dodatkowo przed i po dodajemy po dwa slashe (//), żeby wyróżnić ten fragment kodu (służą one jako znaczniki komentarza).

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 11

PowerQuery od razu nas informuje, że nasze zapytanie jest błędne. Po pierwsze brakuje przecinka na końcu 2 kroku, ale poważniejszym błędem jest to, że dwa kroki mają taką samą nazwę (Źródło). Załóżmy, że nazwę pierwszego kroku zmienimy na ‘Warunki’, co spowoduje, że będziemy musieli skorygować drugi krok, żeby odwoływał się do wyniku pierwszego kroku, a nie 3. Zmienimy też nazwę 2 kroku ze ‘Sprzedawca’ (czyli pierwszy wyraz w drugim kroku. Zapis ‘[Sprzedawca]’ to odwołanie do nazwy kolumny) na ‘Warunek1’. Musimy jeszcze zmienić krok #”Przefiltrowano wiersze” (jego nazwa jest w podwójnych cudzysłowach („) i poprzedzona hashem (#) ponieważ zawiera nietypowy znak – spację), żeby odwoływał się do kroku ‘Warunek1’, a nie do wyniku zapytania tWarunki (wystarczy zamienić nazwę).

Uff. Jeśli pierwszy raz modyfikuje język M w edytorze zaawansowanym, to była ciężka przeprawa, ale Twój kod powinien teraz wyglądać tak:

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 12

Wpisany kod po zatwierdzeniu przyciskiem gotowe powinien wygenerować odpowiednie kroki widoczne w edytorze zapytań (mylące jest dla nas to, że 2 krok wyświetla się jako ‘Nawigacja’, a nie ‘Warunek1’, ponieważ właśnie nazwy ‘Warunek1’ musimy używać, żeby prawidłowo pobrać parametr dla filtru).

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 13

Ważne –napisany przez nas kod (zapytanie) będzie działać bez zapytania z pierwszym warunkiem. Żeby to sprawdzić potrzebujemy je skasować, czyli kliknąć prawym przyciskiem myszy i wybrać odpowiednie polecenie z podręcznego menu. PowerQuery nie powinien Ci pozwolić na usunięcie tego zapytanie, ponieważ w innym zapytaniu (tProdukty – tym od filtrowania, które zduplikowaliśmy, przed modyfikowaniem języka M).

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 14

Musimy je najpierw usunąć, a dopiero potem usunąć zapytanie pobierające warunek. Przy okazji możemy zobaczyć, że zarówno zapytanie odwołujące się do innych zapytań jak i to, któremu zmodyfikowaliśmy język M dają ten sam wynik. To, z którego sposobu skorzystasz zależy od Ciebie. Ogólnie jeśli nie pobierasz dużo warunków i masz mało zapytań w PowerQuery wygodniejsze jest odwoływanie się do wyników zapytań, niż modyfikacja języka M w edytorze zaawansowanym.

Jeśli chcesz możesz wczytać zapytanie do Excela (ewentualnie zmodyfikować kod M, pod 2 warunek filtrowania).
Ponieważ usunęliśmy wcześniej załadowane zapytanie, przestanie się ono aktualizować, gdy będziemy odświeżać wszystkie zapytanie, ponieważ stało się teraz zwykłą tabelą (pozostałością, po ostatnim odświeżeniu zapytania)

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