Tworzenie zapytania parametrycznego (Power Query)

Parametr umożliwia automatyczne zmienianie wartości w zapytaniu i unikanie edytowania zapytania za każdym razem w celu zmiany wartości. Wystarczy zmienić wartość parametru. Po utworzeniu parametru jest on zapisywany w specjalnym zapytaniu parametrycznym, które można wygodnie zmienić bezpośrednio w programie Excel.

1. Wybierz pozycję Dane > Pobierz dane > inne źródła > uruchom Edytor Power Query.

2. W Edytor Power Query wybierz pozycję Narzędzia główne > Zarządzaj parametrami > Nowe parametry.

3. W oknie dialogowym Zarządzanie parametrami wybierz pozycję Nowy.

4. W razie potrzeby ustaw następujące ustawienia:

   Nazwa	Powinno to odzwierciedlać funkcję parametru, ale pozwalać na możliwie najkrótszą.
   Opis	Może to zawierać wszelkie szczegóły, które pomogą użytkownikom w prawidłowym użyciu parametru.
   Wymagane	Wykonaj jedną z następujących czynności: Dowolną wartość Możesz wprowadzić dowolną wartość dowolnego typu danych w zapytaniu parametrycznym. Lista wartości    Wartości można ograniczyć do określonej listy, wprowadzając je w małej siatce. Należy również wybrać wartość domyślną i wartość bieżącą poniżej. Kwerendy Wybierz zapytanie listowe, które przypomina kolumnę strukturalną listy rozdzieloną przecinkami i ujętą w nawiasy klamrowe. Na przykład pole stanu Problemy może mieć trzy wartości: {"New", "Ongoing", "Closed"}. Zapytanie listy należy wcześniej utworzyć, otwierając Edytor zaawansowany (wybierz pozycję Narzędzia główne> Edytor zaawansowany), usuwając szablon kodu, wprowadzając listę wartości w formacie listy zapytań, a następnie wybierając pozycję Gotowe. Po zakończeniu tworzenia parametru zapytanie listy jest wyświetlane w wartościach parametrów.
   Typ	Określa to typ danych parametru.
   Sugerowane wartości	W razie potrzeby dodaj listę wartości lub określ zapytanie, aby podać sugestie dotyczące danych wejściowych.
   Wartość domyślna	Ta opcja jest wyświetlana tylko wtedy, gdy wartość Sugerowane jest ustawiona na wartość Lista wartości i określa element listy jako domyślny. W takim przypadku należy wybrać ustawienie domyślne.
   Bieżąca wartość	W zależności od tego, gdzie używasz parametru, jeśli jest on pusty, zapytanie może nie zwracać żadnych wyników. Jeśli jest zaznaczona opcja Wymagane , wartość bieżąca nie może być pusta.

5. Aby utworzyć parametr, wybierz przycisk OK.

Poniżej przedstawiono sposób zarządzania zmianami w lokalizacjach źródeł danych i zapobiegania błędom odświeżania. Zakładając na przykład podobny schemat i źródło danych, utwórz parametr, aby łatwo zmienić źródło danych i zapobiec błędom odświeżania danych. Czasami zmienia się serwer, baza danych, folder, nazwa pliku lub lokalizacja. Być może menedżer bazy danych od czasu do czasu zamienia serwer, comiesięczna kropla plików CSV trafia do innego folderu lub musisz łatwo przełączać się między środowiskiem deweloperskim/testowym/produkcyjnym.

Krok 1. Tworzenie zapytania parametrycznego

W poniższym przykładzie masz kilka plików CSV, które importujesz za pomocą operacji importowania folderu (Wybierz dane > Pobierz dane> z plików > z folderu) z folderu C:\DataFilesCSV1. Jednak czasami jako lokalizacja do upuszczania plików jest czasem używany inny folder, C:\DataFilesCSV2. Parametru w zapytaniu można użyć jako wartości zastępczej dla innego folderu.

1. Wybierz pozycję Narzędzia główne > zarządzaj parametrami > nowy parametr.

2. W oknie dialogowym Zarządzanie parametrami wprowadź następujące informacje:

   Nazwa	CSVFileDrop
   Opis	Alternatywna lokalizacja upuszczania plików
   Wymagane	Tak
   Typ	Tekst
   Sugerowane wartości	Dowolna wartość
   Bieżąca wartość	C:\DataFilesCSV1

3. Wybierz przycisk OK.

Krok 2. Dodawanie parametru do zapytania danych

1. Aby ustawić nazwę folderu jako parametr, w obszarze Ustawienia zapytania w obszarze Kroki zapytania wybierz pozycję Źródło, a następnie wybierz pozycję Edytuj ustawienia.

2. Upewnij się, że opcja Ścieżka pliku jest ustawiona na Parametr, a następnie wybierz parametr utworzony właśnie z listy rozwijanej.

3. Wybierz przycisk OK.

Krok 3. Aktualizowanie wartości parametru

Lokalizacja folderu właśnie się zmieniła, więc teraz możesz po prostu zaktualizować zapytanie parametryczne.

1. Wybierz pozycję Dane > połączenia & zapytania > kartę Zapytania , kliknij prawym przyciskiem myszy zapytanie parametryczne, a następnie wybierz pozycję Edytuj.

2. Wprowadź nową lokalizację w polu Bieżąca wartość , na przykład C:\DataFilesCSV2.

3. Wybierz pozycję Narzędzia główne > Zamknij & Załaduj.

4. Aby potwierdzić wyniki, dodaj nowe dane do źródła danych, a następnie odśwież zapytanie danych za pomocą zaktualizowanego parametru (Wybierz dane > Odśwież wszystko).

Czasami potrzebujesz łatwego sposobu zmiany filtru zapytania w celu uzyskania różnych wyników bez edytowania zapytania lub tworzenia nieco innych kopii tego samego zapytania. W tym przykładzie zmieniamy datę w celu wygodnej zmiany filtru danych.

1. Aby otworzyć zapytanie, znajdź zapytanie załadowane wcześniej z Edytor Power Query, zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie > Edytuj. Aby uzyskać więcej informacji , zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel.

2. Wybierz strzałkę filtru w dowolnym nagłówku kolumny, aby przefiltrować dane, a następnie wybierz polecenie filtru, takie jak Filtry daty/godziny > Po. Zostanie wyświetlone okno dialogowe Filtrowanie wierszy .
   
   

3. Wybierz przycisk z lewej strony pola Wartość , a następnie wykonaj jedną z następujących czynności:

   • Aby użyć istniejącego parametru, wybierz pozycję Parametr, a następnie wybierz odpowiedni parametr z listy wyświetlanej po prawej stronie.

   • Aby użyć nowego parametru, wybierz pozycję Nowy parametr, a następnie utwórz parametr.

4. Wprowadź nową datę w polu Bieżąca wartość , a następnie wybierz pozycję Narzędzia główne > Zamknij & Załaduj.

5. Aby potwierdzić wyniki, dodaj nowe dane do źródła danych, a następnie odśwież zapytanie danych za pomocą zaktualizowanego parametru (Wybierz dane > Odśwież wszystko). Na przykład zmień wartość filtru na inną datę, aby wyświetlić nowe wyniki.

6. Wprowadź nową datę w polu Bieżąca wartość .

7. Wybierz pozycję Narzędzia główne > Zamknij & Załaduj.

8. Aby potwierdzić wyniki, dodaj nowe dane do źródła danych, a następnie odśwież zapytanie danych za pomocą zaktualizowanego parametru (Wybierz dane > Odśwież wszystko).

W tym przykładzie wartość parametru zapytania jest odczytywana z komórki w skoroszycie. Nie musisz zmieniać zapytania parametrycznego— wystarczy zaktualizować wartość komórki. Na przykład chcesz filtrować kolumnę według pierwszej litery, ale łatwo zmień wartość na dowolną literę z A na Z.

1. W arkuszu skoroszytu, w którym jest ładowane zapytanie, które chcesz filtrować, utwórz tabelę programu Excel zawierającą dwie komórki: nagłówek i wartość.
    

   MyFilter
   G

2. Zaznacz komórkę w tabeli programu Excel, a następnie wybierz pozycję Dane > Pobierz dane > z tabeli/zakresu. Zostanie wyświetlony Edytor Power Query.

3. W polu Nazwa okienka Ustawienia kwerendy po prawej stronie zmień nazwę zapytania na bardziej opisową, na przykład WartośćFiltru. 

4. Aby przekazać wartość w tabeli, a nie w samej tabeli, kliknij prawym przyciskiem myszy wartość w obszarze Podgląd danych, a następnie wybierz pozycję Wyszczególnij.

   Zwróć uwagę, że formuła zmieniła się na = #"Changed Type"{0}[MyFilter]

   Podczas używania tabeli programu Excel jako filtru w kroku 10 Power Query odwołuje się do wartości Tabela jako warunku filtru. Bezpośrednie odwołanie do tabeli programu Excel spowodowałoby błąd.

5. Wybierz pozycję > główneZamknij & Załaduj > Zamknij & Załaduj do. Teraz masz parametr zapytania o nazwie "FilterCellValue", którego używasz w kroku 12.

6. W oknie dialogowym Importowanie danych wybierz pozycję Utwórz tylko połączenie, a następnie wybierz przycisk OK.

7. Otwórz zapytanie, które chcesz przefiltrować przy użyciu wartości z tabeli FilterCellValue (dane poprzednio załadowane z Edytor Power Query), zaznaczając komórkę w danych, a następnie wybierając pozycję Zapytanie > Edytuj. Aby uzyskać więcej informacji , zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel.

8. Wybierz strzałkę filtru w dowolnym nagłówku kolumny, aby przefiltrować dane, a następnie wybierz polecenie filtru, takie jak Filtry tekstu > rozpoczyna się od. Zostanie wyświetlone okno dialogowe Filtrowanie wierszy . 

9. Wprowadź dowolną wartość w polu Wartość , na przykład "G", a następnie wybierz przycisk OK. W takim przypadku wartość jest tymczasowym symbolem zastępczym wartości w tabeli FilterCellValue, którą wprowadzono w następnym kroku.

10. Wybierz strzałkę po prawej stronie paska formuły, aby wyświetlić całą formułę. Oto przykład warunku filtru w formule:
    
    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Nazwa], "G"))

11. Wybierz wartość filtru. W formule wybierz pozycję "G".

12. Za pomocą funkcji M Intellisense wprowadź kilka pierwszych liter utworzonej tabeli FilterCellValue, a następnie wybierz ją z wyświetlonej listy.

13. Wybierz pozycję > główneZamknij > Zamknij & załaduj.

Wynik

Zapytanie używa teraz wartości w utworzonej tabeli programu Excel w celu filtrowania wyników zapytania. Aby użyć nowej wartości, edytuj zawartość komórki w oryginalnej tabeli programu Excel w kroku 1, zmień wartość "G" na "V", a następnie odśwież zapytanie.

Możesz określić, czy zapytania parametryczne są dozwolone, czy niedozwolone.

1. W Edytor Power Query wybierz pozycję Opcje> plików i Ustawienia > Opcje zapytania > Edytor Power Query.

2. W okienku po lewej stronie w obszarze GLOBALNA wybierz pozycję Edytor Power Query.

3. W okienku po prawej stronie w obszarze Parametry zaznacz lub wyczyść pole wyboru Zawsze zezwalaj na parametryzację w oknach dialogowych źródeł danych i przekształceń.