Vytvoření parametrového dotazu (Power Query)

Pomocí parametru můžete automaticky změnit hodnotu v dotazu a vyhnout se úpravě dotazu pokaždé, když chcete hodnotu změnit. Stačí změnit hodnotu parametru. Jakmile vytvoříte parametr, uloží se do speciálního parametrového dotazu, který můžete pohodlně změnit přímo z Excelu.

1. Vyberte Data > Získat data > Jiné zdroje > spustit Editor Power Query.

2. V Editor Power Query vyberte Domů > Spravovat parametry > Nové parametry.

3. V dialogovém okně Spravovat parametr vyberte Nový.

4. Podle potřeby nastavte následující:

   Název	To by mělo odrážet funkci parametru, ale zachovat ji co nejkratší.
   Popis	Může obsahovat jakékoli podrobnosti, které uživatelům pomůžou parametr správně použít.
   Povinná	Udělejte jednu z těchto věcí: Libovolná hodnota Do parametrového dotazu můžete zadat libovolnou hodnotu libovolného datového typu. Seznam hodnot    Hodnoty můžete omezit na konkrétní seznam tak, že je zadáte do malé mřížky. Musíte také vybrat výchozí hodnotu a aktuální hodnotu níže. Dotazu Vyberte dotaz seznamu, který se podobá strukturovanému sloupci Seznam odděleným čárkami a uzavřeným ve složených závorkách. Pole stavu Problémy může mít například tři hodnoty: {"Nový", "Probíhající", "Uzavřeno"}. Dotaz na seznam musíte vytvořit předem tak, že otevřete Rozšířený editor (domů> Rozšířený editor), odeberete šablonu kódu, zadáte seznam hodnot ve formátu seznamu dotazů a pak vyberete Hotovo. Po dokončení vytváření parametru se dotaz seznamu zobrazí v hodnotách parametrů.
   Typ	Určuje datový typ parametru.
   Navrhované hodnoty	V případě potřeby přidejte seznam hodnot nebo zadejte dotaz, který vám nabídne návrhy vstupu.
   Výchozí hodnota	Zobrazí se pouze v případě, že je možnost Navrhované hodnoty nastavená na Seznam hodnot a určuje, která položka seznamu je výchozí. V takovém případě musíte zvolit výchozí nastavení.
   Aktuální hodnota	V závislosti na tom, kde parametr používáte, nemusí dotaz vrátit žádné výsledky, pokud je tento parametr prázdný. Pokud je vybrána možnost Povinné , nemůže být aktuální hodnota prázdná.

5. Pokud chcete vytvořit parametr, vyberte OK.

Tady je způsob, jak spravovat změny umístění zdrojů dat a zabránit chybám aktualizace. Například za předpokladu podobného schématu a zdroje dat vytvořte parametr, který snadno změní zdroj dat a pomůže zabránit chybám aktualizace dat. Někdy se změní server, databáze, složka, název souboru nebo umístění. Správce databáze možná občas prohodí server, měsíční pokles souborů CSV přejde do jiné složky nebo potřebujete snadno přepínat mezi vývojovým/testovacím/produkčním prostředím.

Krok 1: Vytvoření parametrového dotazu

V následujícím příkladu máte několik souborů CSV, které importujete pomocí operace importu složky (Vyberte Data > Získat data > Ze souborů > Ze složky) ze složky C:\DataFilesCSV1. Někdy se ale jako umístění pro odstranění souborů používá jiná složka, C:\DataFilesCSV2. Parametr v dotazu můžete použít jako náhradní hodnotu pro jinou složku.

1. Vyberte Domů > Spravovat parametry > Nový parametr.

2. V dialogovém okně Spravovat parametr zadejte následující informace:

   Název	CSVFileDrop
   Popis	Alternativní umístění pro vyřazení souboru
   Povinná	Ano
   Typ	Text
   Navrhované hodnoty	Libovolná hodnota
   Aktuální hodnota	C:\DataFilesCSV1

3. Vyberte OK.

Krok 2: Přidání parametru do datového dotazu

1. Pokud chcete nastavit název složky jako parametr, v Nastavení dotazu v části Postup dotazu vyberte Zdroj a pak vyberte Upravit nastavení.

2. Ujistěte se, že je možnost Cesta k souboru nastavená na Parametr, a pak v rozevíracím seznamu vyberte parametr, který jste právě vytvořili.

3. Vyberte OK.

Krok 3: Aktualizace hodnoty parametru

Umístění složky se právě změnilo, takže teď můžete jednoduše aktualizovat parametrický dotaz.

1. Vyberte Data > Připojení & Dotazy > kartě Dotazy , klikněte pravým tlačítkem na parametrový dotaz a pak vyberte Upravit.

2. Zadejte nové umístění do pole Aktuální hodnota , například C:\DataFilesCSV2.

3. Vyberte Domů > Zavřít & načtení.

4. Pokud chcete potvrdit výsledky, přidejte do zdroje dat nová data a pak aktualizujte dotaz na data pomocí aktualizovaného parametru (Vyberte Data > Aktualizovat vše).

Někdy potřebujete jednoduchý způsob, jak změnit filtr dotazu, abyste získali různé výsledky, aniž byste museli dotaz upravovat nebo vytvářet mírně odlišné kopie stejného dotazu. V tomto příkladu změníme datum tak, aby se pohodlně změnil datový filtr.

1. Pokud chcete otevřít dotaz, vyhledejte dříve načtený z Editor Power Query, vyberte buňku v datech a pak vyberte Dotaz > Upravit. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excelu.

2. Výběrem šipky filtru v libovolném záhlaví sloupce vyfiltrujte data a pak vyberte příkaz filtru, například Filtry data a času > Po. Zobrazí se dialogové okno Filtrovat řádky .
   
   

3. Vyberte tlačítko nalevo od pole Hodnota a pak udělejte jednu z těchto věcí:

   • Pokud chcete použít existující parametr, vyberte Parametr a pak v seznamu vpravo vyberte požadovaný parametr.

   • Pokud chcete použít nový parametr, vyberte Nový parametr a pak vytvořte parametr.

4. Do pole Aktuální hodnota zadejte nové datum a pak vyberte Domů > Zavřít & Načíst.

5. Pokud chcete potvrdit výsledky, přidejte do zdroje dat nová data a pak aktualizujte dotaz na data pomocí aktualizovaného parametru (Vyberte Data > Aktualizovat vše). Pokud chcete zobrazit nové výsledky, změňte například hodnotu filtru na jiné datum.

6. Do pole Aktuální hodnota zadejte nové datum.

7. Vyberte Domů > Zavřít & načtení.

8. Pokud chcete potvrdit výsledky, přidejte do zdroje dat nová data a pak aktualizujte dotaz na data pomocí aktualizovaného parametru (Vyberte Data > Aktualizovat vše).

V tomto příkladu se hodnota v parametru dotazu přečte z buňky v sešitu. Nemusíte měnit parametrický dotaz, stačí aktualizovat hodnotu buňky. Chcete například filtrovat sloupec podle prvního písmena, ale snadno změnit hodnotu na libovolné písmeno od A do Z.

1. Na listu v sešitu, ve kterém je načten dotaz, který chcete filtrovat, vytvořte excelovou tabulku se dvěma buňkami: záhlavím a hodnotou.
    

   MyFilter
   G

2. Vyberte buňku v excelové tabulce a pak vyberte Data > Získat data > Z tabulky nebo oblasti. Zobrazí se Editor Power Query.

3. V poli Název v podokně Nastavení dotazů na pravé straně změňte název dotazu tak, aby byl smysluplnější, například FilterCellValue. 

4. Pokud chcete předat hodnotu v tabulce, nikoli samotnou tabulku, klikněte pravým tlačítkem na hodnotu v náhledu dat a pak vyberte Přejít k podrobnostem.

   Všimněte si, že vzorec se změnil na = #"Changed Type"{0}[MyFilter]

   Když v kroku 10 použijete excelovou tabulku jako filtr, Power Query odkazuje na hodnotu Tabulka jako podmínku filtru. Přímý odkaz na excelovou tabulku by způsobil chybu.

5. Vyberte Domů > Zavřít & načíst > Zavřít & Načíst do. Teď máte parametr dotazu s názvem FilterCellValue, který použijete v kroku 12.

6. V dialogovém okně Importovat data vyberte Pouze vytvořit připojení a pak vyberte OK.

7. Otevřete dotaz, který chcete filtrovat, s hodnotou v tabulce FilterCellValue, která byla dříve načtena z Editor Power Query, tak, že vyberete buňku v datech a pak vyberete Dotaz > Upravit. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excelu.

8. Výběrem šipky filtru v libovolném záhlaví sloupce vyfiltrujte data a pak vyberte příkaz filtru, například Filtry textu > Začíná na. Zobrazí se dialogové okno Filtrovat řádky . 

9. Do pole Hodnota zadejte libovolnou hodnotu, například G, a pak vyberte OK. V tomto případě je hodnota dočasným zástupným symbolem pro hodnotu v tabulce FilterCellValue, kterou zadáte v dalším kroku.

10. Výběrem šipky na pravé straně řádku vzorců zobrazíte celý vzorec. Tady je příklad podmínky filtru ve vzorci:
    
    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

11. Vyberte hodnotu filtru. Ve vzorci vyberte "G".

12. Pomocí jazyka M IntelliSense zadejte několik prvních písmen tabulky FilterCellValue, kterou jste vytvořili, a pak ho vyberte v zobrazeném seznamu.

13. Vyberte Domů > Zavřít > Zavřít & načtení.

Výsledek

Váš dotaz teď používá hodnotu v excelové tabulce, kterou jste vytvořili, k filtrování výsledků dotazu. Pokud chcete použít novou hodnotu, upravte obsah buňky v původní excelové tabulce v kroku 1, změňte "G" na "V" a pak aktualizujte dotaz.

Můžete určit, jestli jsou nebo nejsou povolené dotazy na parametry.

1. V Editor Power Query vyberte Možnosti a nastavení> souborů > Možnosti dotazu > Editor Power Query.

2. V podokně vlevo v části GLOBAL vyberte Editor Power Query.

3. V podokně vpravo v části Parametry vyberte nebo zrušte zaškrtnutí políčka Vždy povolit parametrizaci ve zdroji dat a dialogových oknech transformace.