Parametrické dotazy s ich použitím v jazykoch SQL alebo Microsoft Query už možno dobre poznáte. Parametre Power Query však majú kľúčové rozdiely:
- Parametre je možné použiť v každom kroku dotazu. Okrem toho, že parametre fungujú ako filter údajov, možno ich použiť aj na určenie takých vecí, ako je cesta k súboru alebo názov servera.
- Parametre nezobrazujú výzvu na zadanie vstupu. Namiesto toho môžete ich hodnotu rýchlo zmeniť pomocou doplnku Power Query. Hodnoty môžete dokonca uložiť a načítať aj z buniek v programe Excel.
- Parametre sa uložia v jednoduchom parametrickom dotaze, ale sú nezávislé od dátových dotazov, v ktorých sa používajú. Po vytvorení môžete do dotazov podľa potreby pridať parameter.
Poznámka Ak chcete vytvoriť parametrické dotazy iným spôsobom, pozrite si tému Vytvorenie parametrického dotazu v programe Microsoft Query.
Vytvorenie parametra
Pomocou parametra môžete automaticky zmeniť hodnotu v dotaze a vyhnúť sa úprave dotazu pri každej zmene hodnoty. Zmeníte len hodnotu parametra. Po vytvorení parametra sa uloží do špeciálneho parametrického dotazu, ktorý môžete pohodlne zmeniť priamo v Exceli.
Vyberte položkuZískať údaje>> ziných zdrojov>Spustenie Editora Power Query.
V Editor Power Query vyberte položky Domov>Spravovať parametre > Nové parametre.
V dialógovom okne Správa parametrov vyberte položku Nový.
Podľa potreby nastavte nasledovné:
Názov Mala by odrážať funkciu parametra, ale mala by byť čo najkratšia. Popis Môže obsahovať akékoľvek podrobnosti, ktoré používateľom pomôžu správne používať parameter. Povinné Použite jeden z nasledovných postupov:
Ľubovoľná hodnota Do parametrického dotazu je možné zadať ľubovoľnú hodnotu ľubovoľného typu údajov.
Zoznam hodnôt Hodnoty môžete obmedziť na konkrétny zoznam ich zadaním do malej mriežky. Tiež musíte nižšie vybrať predvolenú hodnotu a aktuálnu hodnotu .
Dotaz Vyberte dotaz zoznamu, ktorý pripomína štruktúrovaný stĺpec zoznamu oddelený čiarkami a uzavretý v zložených zátvorkách.
Pole Stav problému môže mať napríklad tri hodnoty: {"Nové", "Prebieha", "Zatvorené"}. Dotaz na zoznam musíte vopred vytvoriť otvorením Rozšírený editor (vyberte položku Rozšírený editorDomov>), odstránením šablóny kódu, zadaním zoznamu hodnôt vo formáte zoznamu dotazov a následným výberom položky Hotovo.
Po vytvorení parametra sa dotaz na zoznam zobrazí v hodnotách parametra.Typ Táto možnosť určuje typ údajov parametra. Navrhované hodnoty V prípade potreby môžete pridať zoznam hodnôt alebo zadať dotaz na poskytnutie návrhov vstupu. Predvolená hodnota Toto nastavenie sa zobrazí len vtedy, ak je položka Navrhované hodnotynastavená na možnosť Zoznam hodnôt a určuje, ktorá položka zoznamu je predvolená. V takom prípade je potrebné vybrať predvolenú možnosť. Aktuálna hodnota Ak je parameter prázdny, dotaz nemusí vrátiť žiadne výsledky v závislosti od toho, kde ho použijete. Ak je vybratá možnosť Povinné , možnosť Aktuálna hodnota nemôže byť prázdna. Ak chcete vytvoriť parameter, vyberte tlačidlo OK.
Zmena zdroja údajov pomocou parametra
Tu je spôsob spravovania zmien umiestnení zdrojov údajov, ktorý pomáha zabrániť chybám obnovenia. Ak napríklad predpokladáte podobnú schému a zdroj údajov, vytvorte parameter, ktorý umožní jednoducho zmeniť zdroj údajov a zabrániť chybám pri obnovení údajov. Niekedy sa zmení server, databáza, priečinok, názov súboru alebo umiestnenie. Môže sa stať, že správca databázy príležitostne vymení server, každý mesiac sa presunie množstvo CSV súborov do iného priečinka alebo potrebujete jednoducho prepínať medzi vývojovým/testovacím/výrobným prostredím.
Krok 1: Vytvorenie parametrického dotazu
V nasledujúcom príklade máte niekoľko CSV súborov, ktoré importujete pomocou operácie importovania priečinka (Select Data>Get>Data From FilesFrom>Folder) z priečinka C:\DataFilesCSV1. Niekedy sa však niekedy ako miesto na umiestnenie súborov použije iný priečinok, C:\DataFilesCSV2. Parameter v dotaze môžete použiť ako náhradnú hodnotu pre iný priečinok.
Vyberte položku Domov>Spravovať parametre>,Nový parameter.
V dialógovom okne Správa parametrov zadajte nasledovné informácie:
Názov CSVFileDrop Popis Alternatívne umiestnenie pustenia súboru Povinné Áno Typ Text Navrhované hodnoty Ľubovoľná hodnota Aktuálna hodnota C:\DataFilesCSV1 Vyberte tlačidlo OK.
Krok 2: Pridanie parametra do dotazu na údaje
- Ak chcete nastaviť názov priečinka ako parameter, v časti Nastavenia dotazu v časti Kroky dotazu vyberte položku Zdroj a potom položku Upraviť nastavenia.
- Uistite sa, že možnosť Cesta k súboru je nastavená na hodnotu Parameter, a potom z rozbaľovacieho zoznamu vyberte parameter, ktorý ste práve vytvorili.
- Vyberte tlačidlo OK.
Krok 3: Aktualizácia hodnoty parametra
Umiestnenie priečinka sa zmenilo, takže teraz môžete jednoducho aktualizovať parametrický dotaz.
- Vyberte položku Pripojenia údajov>& karte Dotazy> dotazov, kliknite pravým tlačidlom myši na parametrický dotaz a potom vyberte položku Upraviť.
- Do poľa Aktuálna hodnota zadajte nové umiestnenie, napríklad C:\DataFilesCSV2.
- Vyberte položky Domov,>Zavrieť & Načítať.
- Výsledky potvrdíte pridaním nových údajov do zdroja údajov a obnovením údajového dotazu pomocou aktualizovaného parametra (SelectData >Refresh All).
Použitie parametra na filtrovanie údajov
Niekedy môžete potrebovať jednoduchý spôsob zmeny filtra dotazu s cieľom získať rôzne výsledky bez toho, aby ste museli upravovať dotaz alebo vytvárať mierne odlišné kópie rovnakého dotazu. V tomto príklade zmeníme dátum, aby sme mohli pohodlne zmeniť filter údajov.
Ak chcete otvoriť dotaz, vyhľadajte dotaz, ktorý bol predtým načítaný z Editor Power Query, vyberte bunku v údajoch a potom vyberte položku Upraviť dotaz>. Ďalšie informácie nájdete v téme Vytvorenie, načítanie alebo úprava dotazu v Exceli.
V ľubovoľnej hlavičke stĺpca vyberte šípku filtra, čím sa údaje filtrujú, a potom vyberte príkaz filtra, napríklad Filtre>dátumu a času Po. Zobrazí sa dialógové okno Filtrovať riadky .
Vyberte tlačidlo naľavo od poľa Hodnota a vykonajte jeden z týchto krokov:
- Ak chcete použiť existujúci parameter, vyberte položku Parameter a potom v zozname, ktorý sa zobrazí vpravo, vyberte požadovaný parameter.
- Ak chcete použiť nový parameter, vyberte položku Nový parameter a potom vytvorte parameter.
Do poľa Aktuálna hodnota zadajte nový dátum a potom vyberte položku Domov>Zavrieť & Načítať.
Výsledky potvrdíte pridaním nových údajov do zdroja údajov a obnovením údajového dotazu pomocou aktualizovaného parametra (SelectData >Refresh All). Ak chcete napríklad zobraziť nové výsledky, zmeňte hodnotu filtra na iný dátum.
Do poľa Aktuálna hodnota zadajte nový dátum.
Vyberte položky Domov,>Zavrieť & Načítať.
Výsledky potvrdíte pridaním nových údajov do zdroja údajov a obnovením údajového dotazu pomocou aktualizovaného parametra (SelectData >Refresh All).
Použitie hodnoty bunky na filtrovanie údajov
V tomto príklade sa hodnota v parametri dotazu načíta z bunky v zošite. Nemusíte meniť parametrický dotaz, stačí aktualizovať hodnotu bunky. Chcete napríklad filtrovať stĺpec podľa prvého písmena, ale jednoducho zmeniť hodnotu od A po Z.
V hárku v zošite, v ktorom je načítaný dotaz, ktorý chcete filtrovať, vytvorte excelovú tabuľku s dvoma bunkami: hlavičkou a hodnotou.
Môj filter G Vyberte bunku v excelovej tabuľke a potom vyberte položku Získať>údaje>z tabuľky alebo rozsahu. Zobrazí sa Editor Power Query.
V poli názvov na table Nastavenia dotazu na pravej strane zmeňte názov dotazu tak, aby bol zmysluplnejší, napríklad FilterCellValue.
Ak chcete preniesť hodnotu z tabuľky a nie do samotnej tabuľky, kliknite pravým tlačidlom myši na hodnotu v ukážke údajov a potom vyberte položku Prejsť na detaily.
Všimnite si, že vzorec sa zmenil na= #"Changed Type"{0}[MyFilter]
Keď v kroku 10 použijete ako filter excelovú tabuľku, Power Query zaodkazuje na hodnotu tabuľky ako na podmienku filtra. Priamy odkaz na excelovú tabuľku by spôsobil chybu.Vyberte položky Domov,>Zavrieť & Načítať,>Zavrieť & Načítať do. Teraz máte parameter dotazu s názvom FilterCellValue, ktorý ste použili v kroku 12.
V dialógovom okne Import údajov vyberte položku Iba vytvoriť pripojenie a potom vyberte položku OK.
Otvorte dotaz, ktorý chcete filtrovať s hodnotou v tabuľke FilterCellValue, ktorá bola predtým načítaná z Editor Power Query, výberom bunky v údajoch a následným výberom položky Upraviť dotaz>. Ďalšie informácie nájdete v téme Vytvorenie, načítanie alebo úprava dotazu v Exceli.
V ľubovoľnej hlavičke stĺpca vyberte šípku filtra, čím sa údaje filtrujú, a potom vyberte príkaz filtra, napríklad Textové filtre>začínajú na. Zobrazí sa dialógové okno Filtrovať riadky .
Do poľa Hodnota zadajte ľubovoľnú hodnotu, napríklad G, a potom vyberte tlačidlo OK. V tomto prípade je hodnota dočasným zástupným objektom hodnoty v tabuľke FilterCellValue, ktorú zadáte v ďalšom kroku.
Vyberte šípku na pravej strane riadka vzorcov a zobrazte celý vzorec. Tu je príklad podmienky filtra vo vzorci:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Vyberte hodnotu filtra. Vo vzorci vyberte G.
Pomocou funkcie M Intellisense zadajte niekoľko prvých písmen tabuľky FilterCellValue, ktorú ste vytvorili, a potom ju vyberte v zobrazenom zozname.
Vyberte položky Domov,>Zavrieť>, Zavrieť & Načítať.
Výsledok
Dotaz teraz používa hodnotu vo vytvorenej excelovej tabuľke na filtrovanie výsledkov dotazu. Ak chcete použiť novú hodnotu, upravte obsah buniek v pôvodnej excelovej tabuľke v kroku 1, zmeňte hodnotu G na V a potom obnovte dotaz.
Ovládanie používania parametrických dotazov
Môžete určiť, či sú parametrické dotazy povolené alebo nie.
- V Editor Power Query vyberte položky Možnosti a>nastavenia> súboru Možnosti >dotazuEditorPower Query.
- Na table vľavo v časti GLOBÁLNE vyberte položku Editor Power Query.
- Na table vpravo v časti Parametre začiarknite alebo zrušte začiarknutie políčka Vždy povoliť parametrizáciu v zdroji údajov a dialógových oknách transformácie.