Vytvorenie parametrového dotazu (Power Query)

Parameter môžete použiť na automatickú zmenu hodnoty v dotaze a vyhnúť sa úprave dotazu zakaždým, keď chcete zmeniť hodnotu. Jednoducho zmeníte hodnotu parametra. Po vytvorení sa parameter uloží do špeciálneho parametrového dotazu, ktorý môžete pohodlne zmeniť priamo z Excelu.

1. Vyberte položku Údaje > získať údaje > iné zdroje > spustiť Editor Power Query.

2. V Editor Power Query vyberte položku Domov > Spravovať parametre > nové parametre.

3. V dialógovom okne Správa parametra vyberte položku Nové.

4. Podľa potreby nastavte nasledovné:

   Názov	Mala by odrážať funkciu parametra, ale mala by byť čo najkratšia.
   Popis	Môže obsahovať všetky podrobnosti, ktoré ľuďom pomôžu správne používať parameter.
   Povinné	Vykonajte niektorý z týchto krokov: Ľubovoľnú hodnotu Môžete zadať ľubovoľnú hodnotu ľubovoľného typu údajov v parametrický dotaz. Zoznam hodnôt    Hodnoty môžete obmedziť na konkrétny zoznam tak, že ich zadáte do malej mriežky. Nižšie je potrebné vybrať aj predvolenú hodnotu a aktuálnu hodnotu . Dotaz Vyberte dotaz zoznamu, ktorý pripomína štruktúrovaný stĺpec Zoznam oddelený čiarkami a uzavretý v zložených zátvorkách. Pole Stavu problémov môže mať napríklad tri hodnoty: {"New", "Ongoing", "Closed"}. Dotaz na zoznam je potrebné vytvoriť vopred otvorením Rozšírený editor (vyberte položku Domov > Rozšírený editor), 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 dokončení vytvárania parametra sa dotaz na zoznam zobrazí v hodnotách parametrov.
   Typ	Táto možnosť určuje typ údajov parametra.
   Navrhované hodnoty	V prípade potreby pridajte zoznam hodnôt alebo zadajte dotaz, ktorý bude poskytovať návrhy pre vstup.
   Predvolená hodnota	Táto možnosť sa zobrazí len vtedy, ak je položka Navrhované hodnoty nastavená na možnosť Zoznam hodnôt a určuje, ktorá položka zoznamu je predvolená. V tomto prípade musíte vybrať predvolenú hodnotu.
   Aktuálna hodnota	V závislosti od toho, kde parameter použijete, ak je tento parameter prázdny, dotaz nemusí vrátiť žiadne výsledky. Ak je vybratá možnosť Povinné , aktuálna hodnota nemôže byť prázdna.

5. Ak chcete vytvoriť parameter, vyberte tlačidlo OK.

Tu je spôsob, ako spravovať zmeny umiestnení zdroja údajov a zabrániť chybám obnovenia. Za predpokladu podobnej schémy a zdroja údajov vytvorte napríklad parameter na jednoduchú zmenu zdroja údajov a zabráňte chybám obnovenia údajov. Niekedy sa zmení server, databáza, priečinok, názov súboru alebo umiestnenie. Správca databázy možno občas vymení server, mesačný pokles CSV súborov prejde do iného priečinka alebo budete musieť jednoducho prepínať medzi vývojovým/testovacím/produkčným prostredím.

Krok 1: Vytvorenie parametrických dotazov

V nasledujúcom príklade máte niekoľko CSV súborov, ktoré importujete pomocou operácie importovania priečinka (vyberte údaje > získať údaje > zo súborov > z priečinka) z priečinka C:\DataFilesCSV1. Niekedy sa však ako umiestnenie na zrušenie súborov používa iný priečinok C:\DataFilesCSV2. Parameter v dotaze môžete použiť ako náhradnú hodnotu pre iný priečinok.

1. Vyberte položku Domov > Spravovať parametre > nový parameter.

2. Do dialógového okna Správa parametra zadajte nasledujúce informácie:

   Názov	CSVFileDrop
   Popis	Alternatívne umiestnenie vloženia súboru
   Povinné	Áno
   Typ	Text
   Navrhované hodnoty	Ľubovoľná hodnota
   Aktuálna hodnota	C:\DataFilesCSV1

3. Vyberte položku OK.

Krok 2: Pridanie parametra do dotazu na údaje

1. Ak chcete názov priečinka nastaviť ako parameter, v časti Kroky dotazu v časti Kroky dotazu vyberte položku Zdroj a potom vyberte položku Upraviť nastavenia.

2. Skontrolujte, či je cesta k súboru nastavená na parameter, a potom v rozbaľovacom zozname vyberte parameter, ktorý ste práve vytvorili.

3. Vyberte položku OK.

Krok 3: Aktualizácia hodnoty parametra

Umiestnenie priečinka sa práve zmenilo, takže teraz môžete jednoducho aktualizovať parametrický dotaz.

1. Vyberte položku Údajové > pripojenia & dotazy > dotazy , kliknite pravým tlačidlom myši na parametrický dotaz a potom vyberte položku Upraviť.

2. Do poľa Aktuálna hodnota zadajte nové umiestnenie, napríklad C:\DataFilesCSV2.

3. Vyberte položku Domov > Zavrieť & načítať.

4. Ak chcete potvrdiť výsledky, pridajte do zdroja údajov nové údaje a potom obnovte dotaz na údaje aktualizovaným parametrom (Vyberte údaje > Obnoviť všetko).

Niekedy možno budete chcieť jednoduchý spôsob, ako zmeniť filter dotazu tak, aby sa získali rôzne výsledky bez úpravy dotazu alebo vytvorenia mierne odlišných kópií toho istého dotazu. V tomto príklade zmeníme dátum a pohodlne zmeníme filter údajov.

1. 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 Dotaz > Upraviť. Ďalšie informácie nájdete v téme Vytvorenie, načítanie alebo úprava dotazu v Exceli.

2. Vyberte šípku filtra v ľubovoľnej hlavičke stĺpca a filtrujte údaje a potom vyberte príkaz filtra, napríklad Filtre dátumu a času > Za. Zobrazí sa dialógové okno Filtrovanie riadkov .
   
   

3. Vyberte tlačidlo naľavo od poľa Hodnota a potom vykonajte niektorý z týchto krokov:

   • Ak chcete použiť existujúci parameter, vyberte parameter a potom zo zoznamu, ktorý sa zobrazí na pravej strane, vyberte požadovaný parameter.

   • Ak chcete použiť nový parameter, vyberte položku Nový parameter a potom vytvorte parameter.

4. Do poľa Aktuálna hodnota zadajte nový dátum a potom vyberte položku Domov > Zavrieť & Načítať.

5. Ak chcete potvrdiť výsledky, pridajte do zdroja údajov nové údaje a potom obnovte dotaz na údaje aktualizovaným parametrom (Vyberte údaje > Obnoviť všetko). Zmeňte napríklad hodnotu filtra na iný dátum, aby sa zobrazili nové výsledky.

6. Do poľa Aktuálna hodnota zadajte nový dátum.

7. Vyberte položku Domov > Zavrieť & načítať.

8. Ak chcete potvrdiť výsledky, pridajte do zdroja údajov nové údaje a potom obnovte dotaz na údaje aktualizovaným parametrom (Vyberte údaje > Obnoviť všetko).

V tomto príklade sa hodnota v parametri dotazu prečí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 na ľubovoľné písmeno od A po Z.

1. V hárku v zošite, v ktorom sa načíta dotaz, ktorý chcete filtrovať, vytvorte excelovú tabuľku s dvoma bunkami: hlavičkou a hodnotou.
    

   Môj filter
   G

2. Vyberte bunku v excelovej tabuľke a potom vyberte položku Údaje > Získať údaje > z tabuľky alebo rozsahu. Zobrazí sa Editor Power Query.

3. V poli Názov na table Nastavenia dotazu na pravej strane zmeňte názov dotazu tak, aby bol zmysluplnejší, napríklad FilterCellValue. 

4. Ak chcete odovzdať hodnotu v tabuľke, a nie samotnú tabuľku, 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]

   Ak použijete excelovú tabuľku ako filter v kroku 10, Power Query odkazuje na hodnotu tabuľky ako na podmienku filtra. Priamy odkaz na excelovú tabuľku by spôsobil chybu.

5. Vyberte položku Domov > Zavrieť & Načítať > Zavrieť & Načítať do. Teraz máte parameter dotazu s názvom FilterCellValue, ktorý používate v kroku 12.

6. V dialógovom okne Import údajov vyberte možnosť Vytvoriť iba pripojenie a potom vyberte tlačidlo OK.

7. Výberom bunky v údajoch a výberom položky Dotaz > Upraviť otvorte dotaz, ktorý chcete filtrovať, s hodnotou v tabuľke FilterCellValue, ktorá bola predtým načítaná z Editor Power Query. Ďalšie informácie nájdete v téme Vytvorenie, načítanie alebo úprava dotazu v Exceli.

8. Vyberte šípku filtra v hlavičke ľubovoľného stĺpca, čím sa údaje vyfiltrujú, a potom vyberte príkaz filtra, napríklad Textové filtre > Začína na. Zobrazí sa dialógové okno Filtrovanie riadkov . 

9. 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 symbolom pre hodnotu v tabuľke FilterCellValue, ktorú zadáte v ďalšom kroku.

10. Ak chcete zobraziť celý vzorec, vyberte šípku na pravej strane riadka vzorcov. Tu je príklad podmienky filtra vo vzorci:
    
    = Table.SelectRows(#"Zmenený typ", každý text.StartsWith([Názov], "G"))

11. Vyberte hodnotu filtra. Vo vzorci vyberte "G".

12. Pomocou funkcie M Intellisense zadajte niekoľko prvých písmen vytvorenej tabuľky FilterCellValue a potom ju vyberte zo zobrazeného zoznamu.

13. Vyberte položku Domov > Zavrieť > Zavrieť & Načítať.

Výsledok

Dotaz teraz používa hodnotu v excelovej tabuľke, ktorú ste vytvorili na filtrovanie výsledkov dotazu. Ak chcete použiť novú hodnotu, upravte obsah bunky v pôvodnej excelovej tabuľke v kroku 1, zmeňte hodnotu G na V a potom obnovte dotaz.

Môžete určiť, či sú parametrické dotazy povolené alebo nie.

1. V Editor Power Query vyberte Editor Power Query > možnosti> Súbor – možnosti a nastavenia>možnosti dotazu.

2. Na table vľavo vyberte v časti GLOBALpoložku Editor Power Query.

3. Na table vpravo v časti Parametre vyberte alebo zrušte začiarknutie políčka Vždy povoliť parametrizáciu v zdroji údajov a v dialógových oknách transformácie.