Najprostszym sposobem dostosowania zapytania Power Query dla użytkownika końcowego jest wykorzystanie parametru. Opcja ta wymaga jednak otwarcia Power Query, zarówno w Excelu, jak i w Power BI. Czy da się budować parametry bezpośrednio w komórce arkusza Excel?
Tekst jako parametr Power Query
Krok 1: Zdefiniuj parametr
Z pomocą opcji Poprawność danych (na karcie Dane), tworzymy listę wyboru w komórce A2.
Krok 2: Wstaw tabelę
Na zakresie komórek wstawiamy tabelę, aby mieć obiekt widziany przez Power Query. Zmieniam jej nazwę na „Segment”.
Krok 3: Załaduj dane źródłowe i tabelę
Przygotowujemy 2 zapytania: główne i to z parametrem.
Krok 4: Wejdź do komórki z parametrem
To najciekawszy krok. Możemy w Power Query stanąć na konkretnej komórce i wybrać pod prawym przyciskiem myszy polecenie Wyszczególnij. Zapytanie zamienia się wtedy na ciąg tekstowy.
Krok 5: Utwórz krok wykorzystujący parametr (np. filtr)
Teraz czas na posłużenie się parametrem wewnątrz głównego zapytania. Najpierw tworzymy krok filtrujący.
Krok 6: Podmień parametr
Następnie podmieniamy w pasku formuły filtrowany tekst na parametr „Segment”.
Krok 7: Przetestuj działanie
- Wybierz segment
- Odśwież raport (na przykładzie za pomocą dodatkowo wstawionego przycisku)
- Sprawdź wynik
Data Od i Data Do jako parametr
Krok 1: Zdefiniuj parametry z datami
Tworzymy 2 komórki do wpisania dat i zabezpieczamy je regułami sprawdzania poprawności.
Krok 2: Nazwij komórki
Dla urozmaicenia posłużymy się nie tabelą a zakresem komórek. W tym celu nazywamy komórki w Polu nazwy „Daty”.
Krok 3: Wczytaj dane do Power Query i zmień typ danych na liczbę całkowitą
Sposób opisany w pierwszym scenariuszu z tekstem nie sprawdza się dla dat – Power Query potrafi tylko wyszczególnić tekst lub liczbę. Dlatego typ danych dla kolumn z datami zmieniamy na liczbę całkowitą.
Krok 4: Wyszczególnij liczbę
Krok 5: Wstaw liczbę z formułą Date.From() do paska formuły
Podobnie jak poprzednio, filtrujemy dane, a następnie podmieniamy tekst w pasku formuły. Tym razem wzbogacamy go o formułę tworzącą w locie datę z liczby. Ważne, aby kolumna z datą miała typ danych Data krótka. Jeśli dodamy także datą końcową, krok będzie miał postać:
Pobierz szablon Excel z parametrami w Power Query
Tu możesz pobrać plik Excel z parametrami w arkuszu i podstawić swoje dane.
Świetny wpis. Zastanawiałem się ostatnio jak przygotować zapytanie, aby odbiorca sam mógł nim sterować bez znajomości PQ.
PS. Plik do pobrania jest zapisany w .xlsx co spowodowało utracenie możliwości odświeżenia danych przyciskiem z makrem. Ręczne odświeżanie tabeli działa bez zarzutów.
Dzięki za komentarz. Nie chciałem wrzucać pliku .xlsm, żeby nie wprowadzać zamieszania 🙂
Cześć – czy takie przekazanie parametru jest możliwe w odniesieniu do źródła jakim jest zapytanie SQL? Chciałbym móc sterować zakresem dat z jakich zapytanie ma pobierać dane z bazy SQL. Z góry dziękuję..
Tak. Jeśli całe zapytanie będzie „wyklikane” w Power Query, parametry stają się częścią zapytania SQL generowanego przez Power Query. Zapytanie takie jest wykonywane na serwerze, a do Power Query wracają tylko niezbędne dane. To zachowanie nazywa się Query folding i wkrótce o nim napiszę na blogu.
Super metoda.
Ja mam inny podobny problem.
Pobieram dane z plików znajdujących się w folderze.
Konfiguruję plik przykładowy, w którym w kolumnie 6 w wierszu 2 znajduje się tekst (Data), który chcę umieścić w dodanej kolumnie, we wszystkich wierszach taki sam.
Jak to zrobić?
Próbowałbym w następujący sposób:
1. Dodać nową kolumnę warunkową, która zwróci datę tylko w jednej komórce
2. Wypełnić w górę i w dół, żeby uzupełnić całą kolumnę.
Jeśli te wyjaśnienia wydają się niejasne, proszę o podesłanie pliku na kontakt.
Fajny artykuł. Pytanie czy jest możliwość stworzenia parametru składającego się z listy wyszukiwanych elementów? Nawiązując do przykładu z artykułu, gdybym chciał wyciągnąć wszystkie dane w których priorytet jest wysoki, lub krytyczny.
Tak. Wówczas tabela uzupełniania przez użytkownika musi zostać pobrana jako oddzielne zapytanie i wziąć udział w operacji scalanie.
Świetna robota. Bardzo mi ten wpis pomógł. Dzięki!
Cieszę się, że wiedza się przydała.