Parametry Power Query pobierane z 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.