Možná jste dobře obeznámeni s parametrickými dotazy a jejich použitím v SQL nebo Microsoft Query. Parametry Power Query ale mají zásadní rozdíly:
- Parametry lze použít v jakémkoli kroku dotazu. Kromě toho, že fungují jako filtr dat, lze parametry použít k určení takových věcí, jako je cesta k souboru nebo název serveru.
- Parametry nevyzývají k zadání vstupu. Místo toho můžete jejich hodnotu rychle změnit pomocí Power Query. Můžete dokonce ukládat a načítat hodnoty z buněk v Excelu.
- Parametry jsou uložené v jednoduchém parametrickém dotazu, ale jsou oddělené od dotazů na data, ve kterých se používají. Po vytvoření můžete do dotazů podle potřeby přidat parametr.
Poznámka Pokud chcete použít jiný způsob vytváření parametrických dotazů, přečtěte si téma Vytvoření parametrického dotazu v Microsoft Query.
Vytvoření parametru
Parametr můžete použít k automatické změně hodnoty v dotazu a vyhnout se tak opakovaným úpravám dotazu při změně hodnoty. Stačí změnit hodnotu parametru. Jakmile vytvoříte parametr, uloží se do speciálního parametrického dotazu, který můžete pohodlně změnit přímo v Excelu.
Výběr dat>Získání dat>Jiné zdroje>Spusťte Editor Power Query.
V editoru Editor Power Query vyberte Domů>Správa parametrů > Nové parametry.
V dialogovém okně Spravovat parametr vyberte Nový.
Podle potřeby nastavte následující:
Název Měl by odrážet funkci parametru, ale měl by být co nejkratší. Popis Ty můžou obsahovat veškeré podrobnosti, které lidem pomohou správně použít daný parametr. Povinné Udělejte jednu z těchto věcí:
Libovolná hodnota Do parametrického dotazu můžete zadat libovolnou hodnotu libovolného datového typu.
Seznam hodnot Hodnoty můžete omezit na určitý seznam tak, že je zadáte do malé mřížky. Níže je taky nutné vybrat Výchozí aAktuální hodnotu .
Dotaz Vyberte dotaz seznamu, který se podobá strukturovanému sloupci seznamu oddělených čárkami a uzavřených do složených závorek.
Například pole stavu problémů může nabývat tří hodnot: {"Nový", "Průběžný", "Uzavřeno"}. Dotaz seznamu musíte vytvořit předem tak, že otevřete Rozšířený editor (vyberte 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 můžete přidat seznam hodnot nebo zadat dotaz, který zobrazí návrhy pro zadání vstupu. Výchozí hodnota Tato možnost se zobrazí pouze v případě, že možnost Navrhované hodnoty je nastavena na hodnotu Seznam hodnot a určuje, která položka seznamu je výchozí. V takovém případě musíte vybrat výchozí možnost. Aktuální hodnota V závislosti na tom, kde parametr použijete a bude prázdný, nemusí dotaz vrátit žádné výsledky. Je-li vybrána možnost Požadováno , nemůže být aktuální hodnota prázdná. Pokud chcete vytvořit parametr, vyberte OK.
Změna zdroje dat pomocí parametru
Tady je způsob, jak spravovat změny umístění zdrojů dat a zabránit chybám aktualizace. Pokud například předpokládáte podobné schéma a zdroj dat, vytvořte parametr, který zdroj dat snadno změní a pomůže zabránit chybám aktualizace dat. Někdy se může změnit název serveru, databáze, složky, názvu souboru nebo umístění. Správce databází může občas vyměnit server, měsíční uvolnění souborů CSV jde do jiné složky nebo potřebujete snadno přepínat mezi vývojovým, testovacím a produkčním prostředím.
Krok 1: Vytvoření parametrického dotazu
V následujícím příkladu máte několik souborů CSV, které importujete pomocí operace importu složky (Select Data> GetData>From FilesFrom>Folder) ze složky C:\DataFilesCSV1. Někdy se ale jako umístění pro umístění souborů použije jiná složka: C:\DataFilesCSV2. Parametr v dotazu můžete použít jako náhradní hodnotu pro jinou složku.
Vyberte možnost Domů>Spravovat parametry>Nový parametr.
Do dialogového okna 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 Vyberte OK.
Krok 2: Přidání parametru do dotazu na data
- Pokud chcete nastavit název složky jako parametr, v Nastavení dotazu v části Kroky dotazu vyberte Zdroj a pak vyberte Upravit nastavení.
- Ujistěte se, že je možnost cesty k souboru nastavená na Parametr, a pak v rozevíracím seznamu vyberte parametr, který jste právě vytvořili.
- Vyberte OK.
Krok 3: Aktualizace hodnoty parametru
Umístění složky se právě změnilo, takže teď můžete parametrický dotaz jednoduše aktualizovat.
- Vyberte Datová>připojení & kartě Dotazy> dotazů, klikněte pravým tlačítkem na parametrický dotaz a pak vyberte Upravit.
- Zadejte nové umístění do pole Aktuální hodnota , například C:\DataFilesCSV2.
- Vyberte Domů>Zavřít & Načíst.
- Výsledky potvrdíte tak, že do zdroje dat přidáte nová data a pak aktualizujete datový dotaz s aktualizovaným parametrem (Select>Data Refresh All).
Použití parametru k filtrování dat
Někdy potřebujete snadný způsob, jak změnit filtr dotazu tak, abyste získali jiné 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, abychom mohli pohodlně změnit filtr dat.
Pokud chcete otevřít dotaz, vyhledejte dotaz načtený dříve z Editor Power Query, vyberte buňku v datech a pak vyberte Upravit dotaz>. Další informace naleznete v tématu Vytvoření, načtení nebo úprava dotazu v aplikaci Excel.
Vyberte šipku filtru v libovolném záhlaví sloupce, abyste mohli filtrovat data, a pak vyberte příkaz filtru, třeba Filtry >data a časuza. Zobrazí se dialogové okno Filtrovat řádky .
Vyberte tlačítko nalevo od pole Hodnota a udělejte jednu z těchto věcí:
- Pokud chcete použít existující parametr, vyberte Parametr a pak vyberte požadovaný parametr ze seznamu, který se zobrazí vpravo.
- Pokud chcete použít nový parametr, vyberte Nový parametr a pak vytvořte parametr.
Do pole Aktuální hodnota zadejte nové datum a pak vyberte Domů>Zavřít & Načíst.
Výsledky potvrdíte tak, že do zdroje dat přidáte nová data a pak aktualizujete datový dotaz s aktualizovaným parametrem (Select>Data Refresh All). Chcete-li například zobrazit nové výsledky, změňte hodnotu filtru na jiné datum.
Do pole Aktuální hodnota zadejte nové datum.
Vyberte Domů>Zavřít & Načíst.
Výsledky potvrdíte tak, že do zdroje dat přidáte nová data a pak aktualizujete datový dotaz s aktualizovaným parametrem (Select>Data Refresh All).
Použití hodnoty buňky k filtrování dat
V tomto příkladu se hodnota v parametru dotazu čte z buňky v sešitu. Parametrický dotaz nemusíte měnit, stačí aktualizovat hodnotu buňky. Chcete například filtrovat sloupec podle prvního písmene, ale hodnotu snadno změnit na libovolné písmeno od A do Z.
Na listu v sešitě, ve kterém je načtený dotaz, který chcete filtrovat, vytvořte excelovou tabulku se dvěma buňkami: záhlavím a hodnotou.
Můj filtr G Vyberte buňku v excelové tabulce a pak vyberte Data>získat data>z tabulky/oblasti. Zobrazí se Editor Power Query.
V poli Název v podokně Nastavení dotazu vpravo změňte název dotazu tak, aby byl smysluplnější, například FilterCellValue.
Pokud chcete, aby se předala hodnota v tabulce a ne v tabulce samotné, klikněte pravým tlačítkem myši na hodnotu v Náhledu dat a pak vyberte Procházet k podrobnostem.
Všimněte si, že se vzorec změnil na= #"Changed Type"{0}[MyFilter]
Když v kroku 10 použijete jako filtr excelovou tabulku, bude Power Query odkazovat na hodnotu tabulky jako na podmínku filtru. Přímý odkaz na excelovou tabulku by způsobil chybu.Vyberte Domů>, Zavřít & Načíst>Zavřít & Načíst do. Teď máte parametr dotazu s názvem FilterCellValue, který jste použili ve 12. kroku.
V dialogovém okně Importovat data vyberte Pouze vytvořit připojení a pak vyberte OK.
Otevřete dotaz, který chcete filtrovat s hodnotou v tabulce FilterCellValue (dotaz načtenou z Editor Power Query), tak, že vyberete buňku v datech a pak vyberete Upravit dotaz>. Další informace naleznete v tématu Vytvoření, načtení nebo úprava dotazu v aplikaci Excel.
Chcete-li filtrovat data, vyberte v libovolném záhlaví sloupce šipku filtru a pak vyberte příkaz filtru, třeba Filtry> textuzačínají. Zobrazí se dialogové okno Filtrovat řádky .
Do pole Hodnota zadejte libovolnou hodnotu, třeba G, a pak vyberte OK. V tomto případě je hodnota dočasným zástupným symbolem hodnoty v tabulce FilterCellValue, kterou zadáte v dalším kroku.
Pokud chcete zobrazit celý vzorec, vyberte šipku na pravé straně řádku vzorců. Tady je příklad podmínky filtru ve vzorci:
= Table.SelectRows(#"Změněný typ", each Text.StartsWith([Name], "G"))
Vyberte hodnotu filtru. Ve vzorci vyberte "G".
Pomocí M IntelliSense zadejte několik prvních písmen vytvořené tabulky FilterCellValue a pak ji vyberte ze zobrazeného seznamu.
Vyberte Domů,>Zavřít>,Zavřít & Načíst.
Výsledek
Dotaz teď použije hodnotu z excelové tabulky, kterou jste vytvořili k filtrování výsledků dotazu. Chcete-li použít novou hodnotu, upravte obsah buňky v původní tabulce aplikace Excel v kroku 1, změňte "G" na "V" a potom aktualizujte dotaz.
Řízení použití parametrických dotazů
Můžete určit, jestli jsou nebo nejsou povolené parametrické dotazy.
- V Editoru Power Query vyberte Možnosti souboru>a Nastavení>Možnosti dotazuEditor>Power Query.
- V podokně na levé straně, v části GLOBAL, vyberte Editor Power Query.
- V podokně vpravo v části Parametry zaškrtněte nebo zrušte zaškrtnutí políčka Vždy povolit parametrizaci ve zdrojích dat a transformačních dialogových oknech.