Parametry Power Query komórki Excel

Zmienna, która w raportach pojawia się najczęściej to zakresy dat. Pobierając dane z różnych źródeł ograniczamy ogromne zbiory poprzez założenie filtrów na datę od i datę do, w zależności od tego jaki okres czasu nas interesuje. Jak wiemy, idealnym narzędziem do pobierania i przekształcania dużych baz danych, jest Power Query. Aby ułatwić pracę użytkownikom, którzy nie znają tego narzędzia, dobrą praktyką jest ustawienie zmiennych parametrów zapytań. Przykładem mogą być wspomniane daty. Stosując parametry Power Query pobierane z komórki Excel każdy użytkownik będzie w stanie modyfikować tak przygotowanie zapytanie zupełnie nie znając tego narzędzia.

Parametry zapytań Power Query pobierane bezpośrednio z komórki Excel

Dla uproszczenia całego procesu dane bazowe przechowuje w tym samym skoroszycie, w który będą znajdowały się nasze parametry Power Query oraz finalna tabela wynikowa. W arkuszu dodajemy tabelę z nagłówkami „Od” i „Do”. Wpisujemy do niej interesujący nas okres czasu. Wg tych danych będziemy sterować parametrami naszego zapytania.

Zaczytujemy do Power Query tabelę z naszymi danymi oraz tabelę z zakresami dat wybierając opcję „Z tabeli/zakresu”. Zapytania zapisujemy i załadujemy jako połączenia. Na razie bez zaczytywania do arkuszy.

Wchodzimy do edytora Power Query. Klikając na ikonki obok nagłówków naszych dwóch kolumn zmieniamy typy danych z „Data” na „Liczba całkowita”. Następnie prawym przyciskiem myszy duplikujemy zapytanie z datami. Zmieniamy nazwę pierwszego na „Od”, a drugiego na „Do”.

Wyszczególnienie parametrów

Teraz najważniejsza sprawa. Klikamy prawym przyciskiem myszy na liczbę oznaczającą naszą datę „Od” i wybieramy opcję wyszczególnij. Power Query jest w stanie wyszczególnić jedynie liczbę lub tekst. Z tego powodu w poprzednim kroku zmienialiśmy datę właśnie na liczbę. Jak się okazuje, wyszczególniać możemy zasadniczo dowolne dane, nie musi zatem być to tylko liczba czy tekst.  

Powyższą czynność powtarzamy dla daty „Do”, z tym, że wyszczególniamy liczbę z drugiej kolumny. Powinniśmy uzyskać efekt jak poniżej. Ikony zapytań uległy zmianie i widzimy, że zwracają one same wartości bez żadnych tabel. Dokładnie o to nam chodziło.

Zagnieżdżenie parametrów w zapytaniu

Teraz użyjemy tych wartości i zagnieździmy je w filtrze dat dla naszego zbioru danych. Najpierw ustawiamy dowolny filtr dat w naszym zapytaniu przechowującym raport ze wszystkimi danymi.

Edytujemy krok filtrujący nasze dane w pasku formuły okna Power Query. Podmienimy wybrane ręczne daty na parametry „Od” i „Do”, które przygotowaliśmy wcześniej. Ponadto zagnieżdżamy je w funkcję Date.From(). Wielkość liter w Power Query ma znaczenie. Warto o tym pamiętać przy wpisywaniu formuł.

Formuła mojego kroku przed zmianą to = Table.SelectRows(#”Zmieniono typ”, each [Latest Launch] >= #date(2018, 5, 1) and [Latest Launch] <= #date(2018, 6, 30))

A po zmianie = Table.SelectRows(#”Zmieniono typ”, each [Latest Launch] >= Date.From(Od) and [Latest Launch] <= Date.From(Do))

Zmianę zatwierdzamy enterem. Nasze przekształcone zapytanie zaczytujemy finalnie do arkusza wybierając opcję „Zamknij i załaduj do”. Widać, że tabela wynikowa zawiera tylko zakres ustawiony przez nas w komórkach Excela.

Podsumowanie

Największą zaletą tej metody jest to, że teraz możemy dowolnie zmienić zawartość komórek tabelki z datami „Od” i „Do”. Odświeżając tabelę wynikową załadują się dane dla nowego, wybranego przez nas wycinka czasu.  Za każdym razem gdy chcemy zaczytać inne dane nie musimy już wchodzić do edytora Power Query, a następnie wyszukiwać odpowiednich kroków i zmieniać daty wewnątrz zapytań. Całym procesem łatwo sterujemy z poziomu arkusza Excel przy pomocy kilku kliknięć. Plik możemy udostępnić współpracownikom, który nigdy nie słyszeli o Power Query, a z łatwością sami będą w stanie edytować zapytanie.

Inne zastosowanie parametrów

Powyższą metodę możemy stosować nie tylko dla zakresów dat. Dzięki  niej zafiltrujemy dowolne dane przechowywane w zasadniczo dowolnej kolumnie.



1 gwiazdka2 gwiazdki3 gwiazdki4 gwiazdki5 gwiazdek (6 głosów, średnia: 5,00 z 5)
Loading...


Powiązane

  1. Bill Szysz

    Cytat – „Power Query jest w stanie wyszczególnić jedynie liczbę lub tekst. ”
    Niestety, popełniasz zasadniczy błąd, PQ może wyszczególnić dowolny rodzaj danych a nie tylko liczby i tekst (może to zrobić nawet z funkcjami).
    Wystarczy więc jako regułę filtra zastosować bezpośrednio daty wyciągnięte z tabelki z parametrami, czyli:
    = Table.SelectRows(#”Zmieniono typ”, each [Latest Launch] >= Od and [Latest Launch] <= Do)
    Wcześniej należy zadbać aby kolumny tabeli z parametrami miały typ date.
    Konwersja na liczby nie jest potrzebna.
    Pozdrawiam.

    1. Dzięki za cenną uwagę. Miło mi, że taka osobistość zagląda na moją stronkę 🙂
      Przypuszczam, że w starszych wersjach PQ mogło nie być innych opcji wyszczególniania jak z tekstu lub liczby. Opisałem metodę, którą znam i z której sam korzystam już od dość dawna.
      Dobrze wiedzieć, że można tu jeszcze coś uprościć. Sprawdziłem i faktycznie – działa tak jak napisałeś.

  2. Pawel

    Czy jest możliwe użycie parametru w źródle danych zastępując datę w zapytaniu

    = Odbc.Query(„DRIVER={Oracle in OraClient11g_home1};SERVER=OA.EXA;;DBQ=OA.EXA”, „select * from OA.KURSY_WALUT where repo_date > ‚2021-02-26′”)

    1. Tak, powinno udać się coś takiego zrobić. Parametr daty musisz w tym wypadku przekazać do zapytania SQL jako ciąg tekstowy. Ustaw zapytanie StartDate, które wyszczególni z komórki w Excelu datę i następnie przekaże ją do Twojego kroku:
      = Odbc.Query(„DRIVER={Oracle in OraClient11g_home1};SERVER=OA.EXA;;DBQ=OA.EXA”, „select * from OA.KURSY_WALUT where repo_date > ‚„ & StartDate & ”′”)

  3. tomaasz

    Cześć próbuję użyć tej metody do załadowania z excela dynamicznej ścieżki do pliku

    Wpisując stałą ścieżkę działa:
    = Excel.Workbook(File.Contents(„S:\Raporty\2021-09\kwoty 09.2021.xlsm”), null, true)

    ale gdy próbuję użyć wyszczególnionego parametru to wywala mi błąd
    = Excel.Workbook(File.Contents(SciezkaDoPliku), null, true)

    „Formula.Firewall: Element Zapytanie „XXXXXXX” (krok „Źródło”) przywołuje inne zapytania lub kroki, dlatego nie może uzyskać bezpośredniego dostępu do źródła danych. Utwórz ponownie tę kombinację danych.”

    SciezkaDoPliku sprawdzona i dobrze się generuje.

    Czy wiesz może skąd ten błąd i jak go usunąć ?

    1. Cześć,
      Nie zamieściłeś pełnego zapytania, ale prawdopodobnie Power Query nie akceptuje tutaj połączenia zewnętrznego źródła danych z innym zapytaniem.
      Ten wpis wpis powinien Ci pomóc: https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

  4. Jola

    Nie dawno zakochałam się w Power Query. Przetrenowałam tę „sztuczkę” z datami i działa. Dziękuję za artykuł.

  5. Marek

    Cześć Kuba

    Bardzo dokładnie to opisałeś.

    Trochę z przymusu zacząłem się interesować Power Query i bardzo mi to pomogło: bez filozofii i bez błędów.

    Pozdrawiam serdecznie

Komentarze