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

Možná znáte parametrické dotazy s jejich používáním v SQL nebo Microsoft Query. Parametry Power Query ale mají klíčové rozdíly:    

  • Parametry lze použít v libovolném kroku dotazu. Kromě toho, že fungují jako filtr dat, můžete parametry použít k zadání například cesty k souboru nebo názvu serveru.

  • Parametry se nezýkadí na zadání. Místo toho můžete rychle změnit jejich hodnotu pomocí Power Query. Můžete dokonce ukládat a načítat hodnoty z buněk v Excelu.

  • Parametry se ukládají do jednoduchého parametrizovaného dotazu, ale jsou oddělené od datových dotazů, ve které se používají.  Po vytvoření můžete do dotazů podle potřeby přidat parametr.

Poznámka    Pokud chcete vytvořit parametrické dotazy jiným způsobem, podívejte se na článek Vytvoření parametrizovaného dotazu v Microsoft Query.

Parametr můžete použít k automatické změně hodnoty v dotazu a k zabránění úpravám 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 >další zdroje >spuštění Power Query Editoru.

  2. V Editoru Power Query vyberte Domů a > parametry > Nové parametry.

  3. V dialogovém okně Manage Parameter (Spravovat parametr) vyberte New (Nový).

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

    Název    

    Tato funkce by měla odrážet funkci parametru, ale měla by být co nejkratší.

    Popis    

    Může obsahovat všechny podrobnosti, které lidem pomohou správně používat tento parametr.

    Povinná    

    Proveďte jednu z následujících akcí: Libovolná hodnota: Do parametrizované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. Je také nutné vybrat výchozí hodnotu a aktuální hodnotu níže.

    Dotaz Vyberte seznamový dotaz, který se podobá strukturovanému sloupci Seznam oddělenému čá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"}. Seznamový dotaz musíte vytvořit předem tak, že otevřete Rozšířený editor (vyberte Home > Advanced Editor),odeberete šablonu kódu, zadáte seznam hodnot ve formátu seznamu dotazů a pak vyberete Hotovo.

    Po vytvoření parametru se v hodnotách parametrů zobrazí dotaz seznamu.

    Typ    

    Určuje datový typ parametru.

    Navrhované hodnoty    

    V případě potřeby přidejte seznam hodnot nebo zadejte dotaz, který vám poskytne návrhy pro zadání.

    Výchozí hodnota

    Zobrazí se jenom 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žijete, nemusí dotaz vrátit žádné výsledky, pokud je tento parametr prázdný. Pokud je vybraná možnost Povinný, nesmí 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 při aktualizaci. Pokud třeba za předpokladu, že se podobá schématu a zdroji dat, vytvořte parametr, který snadno změní zdroj dat a pomůže zabránit chybám při aktualizaci dat. Někdy se změní server, databáze, složka, název souboru nebo umístění. Správce databáze si možná občas vymění server, měsíční pokles souborů CSV přejde do jiné složky nebo je potřeba snadno přepínat mezi vývojovou/ testovací nebo produkční prostředí.

Krok 1: Vytvoření parametrizovaného dotazu

V následujícím příkladu máte několik souborů CSV, které importujete pomocí operace importu složky (vyberte Data > Naimportovat data> Ze souborů > Ze složky) ze složky C:\DataFilesCSV1. Někdy se ale jako umístění k přetažení souborů C:\DataFilesCSV2 používá jiná složka. 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 přetažení 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, vyberte v nastavení dotazu včásti Kroky dotazumožnost Zdroja pak vyberte Upravit nastavení.

  2. Ujistěte se, že je možnost Cesta k souboru nastavená na Parametra 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 parametrní dotaz jednoduše aktualizovat.

  1. Vyberte Data > Connections & Queries > Queries tab, klikněte pravým tlačítkem myši na parametrické dotazy 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číst.

  4. Pokud chcete potvrdit výsledky, přidejte do zdroje dat nová data a aktualizujte dotaz na data aktualizovaným parametrem (Vyberte data> Aktualizovat vše).

Někdy potřebujete snadný způsob, jak změnit filtr dotazu, abyste získali různé výsledky bez úpravy dotazu nebo vytvoření mírně odlišných kopií stejného dotazu. V tomto příkladu změníme datum tak, aby se filtr dat pohodlně změnil.

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

  2. Vyberte šipku filtru v libovolném záhlaví sloupce a vyfiltrujte data a pak vyberte příkaz filtru, například Filtry data a času > Za. Zobrazí se dialogové okno Filtrovat řádky.

    Zadání parametru v dialogovém okně Filtr

  3. Vyberte tlačítko nalevo od pole Hodnota a pak proveďte jednu z těchto akcí:

    • Pokud chcete použít existující parametr, vyberte Parametra v seznamu, který se zobrazí vpravo, vyberte parametr, který chcete použít.

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

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

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

  6. Zadejte nové datum do pole Aktuální hodnota.

  7. Vyberte Domů > Zavřít & Načíst.

  8. Pokud chcete potvrdit výsledky, přidejte do zdroje dat nová data a aktualizujte dotaz na data aktualizovaným parametrem (Vyberte data> Aktualizovat vše).

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

  1. Na listu v sešitu, kde 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í dotazu vpravo změňte název dotazu tak, aby byl smysluplnější, například FilterCellValue. 

  4. Pokud chcete předat hodnotu v tabulce, ne samotnou tabulku, klikněte pravým tlačítkem myši 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 bude jako podmínku filtru odkazovat na hodnotu Tabulka. Přímý odkaz na excelové tabulky 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 Jenom 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čtená z Power Query Editoru, výběrem buňky v datech a výběrem možnosti Dotaz> Upravit. Další informace najdete v článku Vytvoření, načtení nebo úprava dotazu v Excelu.

  8. Vyberte šipku filtru v libovolném záhlaví sloupce a vyfiltrujte data a pak vyberte příkaz filtru, například Filtry textu >začíná . Zobrazí se dialogové okno Filtrovat řádky. 

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

  10. Pokud chcete zobrazit celý vzorec, vyberte šipku na pravé straně řádku vzorců. 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í funkce M Intellisense zadejte několik prvních písmen tabulky FilterCellValue, kterou jste vytvořili, a pak ji vyberte ze seznamu, který se zobrazí.

  13. Vyberte Domů >Zavřít > Zavřít & Načíst.

Výsledek

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 aktualizujte dotaz.

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

  1. V Power Query Editoru vyberte Možnosti > a Nastavení >dotazu > Power Query Editor.

  2. V podokně vlevo v části GLOBÁLNÍvyberte Power Query Editor.

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

Viz také

Nápověda k Power Query pro Excel

Použití parametrů dotazu (docs.com)

Potřebujete další pomoc?

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor! Pravděpodobně bude užitečné, když vás spojíme s některým z našich agentů podpory Office.

×