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.
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 😉 ).
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.
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ść.
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.
Teraz filtrujemy kolumnę Sprzedawca po dowolnym sprzedawcy (np.: po Janie).
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))
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.
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.
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ę.
let
Źródło = Excel.CurrentWorkbook(){[Name="tWarunki"]}[Content],
Sprzedawca = Źródło{0}[Sprzedawca]
in
Sprzedawca
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).
let
//
Źródło = Excel.CurrentWorkbook(){[Name="tWarunki"]}[Content],
Sprzedawca = Źródło{0}[Sprzedawca]
//
Źródło = Excel.CurrentWorkbook(){[Name="tProdukty"]}[Content],
#"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Data", type date}, {"Sprzedawca", type text}, {"Produkt", type text}}),
#"Przefiltrowano wiersze" = Table.SelectRows(#"Zmieniono typ", each ([Sprzedawca] = tWarunki)),
#"Przefiltrowano wiersze1" = Table.SelectRows(#"Przefiltrowano wiersze", each ([Produkt] = tWarunki2))
in
#"Przefiltrowano wiersze1"
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:
let
//
Warunki = Excel.CurrentWorkbook(){[Name="tWarunki"]}[Content],
Warunek1 = Źródło{0}[Sprzedawca],
//
Źródło = Excel.CurrentWorkbook(){[Name="tProdukty"]}[Content],
#"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Data", type date}, {"Sprzedawca", type text}, {"Produkt", type text}}),
#"Przefiltrowano wiersze" = Table.SelectRows(#"Zmieniono typ", each ([Sprzedawca] = Warunek1)),
#"Przefiltrowano wiersze1" = Table.SelectRows(#"Przefiltrowano wiersze", each ([Produkt] = tWarunki2))
in
#"Przefiltrowano wiersze1"
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).
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).
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