Vytvorenie vzorcov Power Query v Exceli

Editor Power Query poskytuje v Exceli dotazy na údaje a tvarovanie, pomocou ktorých môžete zmeniť tvar údajov z mnohých zdrojov údajov. Ak chcete zobraziť okno editora Power Query, importujteúdaje z externých zdrojov údajov v excelovom hárku, vyberte bunku v údajoch a potom vyberte položku Dotaz > Upraviť. Nižšie je uvedené zhrnutie hlavných súčastí.

1. Pás s nástrojmi editora Power Query, ktorý sa používa na tvarovanie údajov

2. Tabla Dotazy, ktorú používate na vyhľadávanie zdrojov údajov a tabuliek

3. Kontextové ponuky, ktoré sú praktické skratky k príkazom na páse s nástrojmi

4. Ukážka údajov, ktorá zobrazuje výsledky krokov, ktoré sa použili na údaje

5. Tabla Nastavenia dotazu, ktorá obsahuje vlastnosti a jednotlivé kroky dotazu

Každý krok v dotaze je na pozadí založený na vzorci, ktorý je viditeľný v riadku vzorcov.

Niekedy možno budete chcieť upraviť alebo vytvoriť vzorec. Vzorce používajú jazyk vzorcov Power Query, ktorý môžete použiť na zostavenie jednoduchých aj zložitých výrazov. Ďalšie informácie o syntaxi, argumentoch, poznámkach, funkciách a príkladoch nájdete v téme Jazyk vzorcov M Doplnku Power Query.

Na table Nastavenia dotazu v časti Použité kroky sa pre každúúlohu vytvoria kroky dotazu a príslušné vzorce. Nižšie je uvedené zobrazenie niektorých údajov dotazu a podrobný príklad vzťahu medzi krokmi a vzorcami v tomto dotaze.

Krok dotazu a úloha	Vzorec
Source (Zdroj) Pripojenie k webovému zdroju údajov	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navigation (Navigácia) Výber tabuľky na pripojenie	=Source{2}[Data]
Changed Type (Zmenený typ) Zmena typov údajov (ktoré Power Query automaticky robí)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
Odstránené ostatné stĺpce Odstránenie ostatných stĺpcov, aby sa zobrazovali iba požadované stĺpce	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Nahradená hodnota Nahradenie hodnôt na vyčistenie hodnôt vo vybratom stĺpci	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Filtrované riadky Filtrovanie hodnôt v stĺpci	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Premenované stĺpce Zmena hlavičiek stĺpcov na zmysluplné	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Dôležité    Postup Zdroj,Navigácia aZmenený typ vykonajte opatrne, pretože ich vytvára Power Query na definovanie a nastavenie zdroja   údajov.

Zobrazenie alebo skrytie riadku vzorcov

V predvolenom nastavení sa zobrazuje riadok vzorcov, ale ak nie je viditeľný, môžete ho znova zobraziť.

• Vyberte položku > Zobrazenie > vzorcov.

Edit a formula in the formula bar

1. Ak chcete otvoriť dotaz, vyhľadajte dotaz, ktorý bol predtým načítaný z Editora 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. Na table Nastavenia dotazu vyberte v časti Použitékroky krok, ktorý chcete upraviť.

3. V riadku vzorcov vyhľadajte a zmeňte hodnoty parametra a potom vyberte ikonu klávesu Enter alebo stlačte kláves Enter. Zmeňte napríklad tento vzorec tak, aby sa zároveň ponechal Stĺpec2:
   
   Pred: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Za:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Výberom ikony údajov alebo stlačením klávesu Enter zobrazte nové výsledky v ukážke údajov.

5. Ak chcete zobraziť výsledok v excelovom hárku, vyberte položky Domov > Zavrieť & Načítať.

Vytvorenie vzorca v riadku vzorcov

Ako príklad jednoduchého vzorca skúsme konvertovať textovú hodnotu do riadneho formátu pomocou funkcie Text.Proper.

1. Ak chcete otvoriť prázdny dotaz, v Exceli vyberte položky Údaje> Získať údaje > Z iných zdrojov > Prázdny dotaz. Ďalšie informácie nájdete v téme Vytvorenie, načítanie alebo úprava dotazu v Exceli.

2. Do riadku vzorcov zadajte=Text.Proper("text value")a potom vyberte ikonu Zadať alebo stlačte kláves Enter.
   
   Výsledky sa zobrazia v ukážke údajov.

3. Ak chcete zobraziť výsledok v excelovom hárku, vyberte položky Domov > Zavrieť & Načítať.

Výsledok:

 Po vytvorení vzorca Power Query overí syntax vzorca. Ak však v dotaze vložíte, znova zoradenie alebo odstránite medzikrok, môže to spôsobiť prerušenie dotazu.  Výsledky vždy overte v ukážke údajov.

Dôležité    Postup Zdroj,Navigácia aZmenený typ vykonajte opatrne, pretože ich vytvára Power Query na definovanie a nastavenie zdroja   údajov.

Úprava vzorca pomocou dialógového okna

Týmto spôsobom sa používajú dialógové okná, ktoré sa líšia v závislosti od kroku. Nepotrebujete poznať syntax vzorca.

1. Ak chcete otvoriť dotaz, vyhľadajte dotaz, ktorý bol predtým načítaný z Editora 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. Na table Nastavenia dotazu v časti Použité kroky vyberte ikonu Upraviť nastavenia kroku, ktorý chcete upraviť, alebo kliknite pravým tlačidlom myši na krok a potom vyberte položku Upraviť nastavenia.

3. V dialógovom okne vykonajte požadované zmeny a potom vyberte tlačidlo OK.

Vloženie kroku

Po dokončení kroku dotazu, pomocou ktorý sa mení tvar údajov, sa pod aktuálny krok dotazu pridá krok dotazu. Ak však vložíte krok dotazu do stredu týchto krokov, v nasledujúcich krokoch sa môže vyskytnúť chyba. Keď sa pokúsite vložiť nový krok, Power Query zobrazí upozornenie Vložiť krok a nový krok zmení polia, ako sú napríklad názvy stĺpcov, ktoré sa používajú v ľubovoľnom z krokov, ktoré nasledujú po vložení kroku.

1. Na table Nastavenia dotazu v časti Použitékroky vyberte krok, ktorý má okamžite predchádzať novému kroku a jeho zodpovedajúcemu vzorcu.

2. Vyberte ikonu Pridať naľavo od riadku vzorcov. Môžete tiež kliknúť pravým tlačidlom myši na krok a potom vybrať položku Vložiť krok za.Vytvorí sa nový vzorec vo formáte :
   
   = <nameOfTheStepToReference>, napríklad =Production.WorkOrder.

3. Zadajte nový vzorec pomocou formátu:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Predpokladajme napríklad, že máte tabuľku so stĺpcom Pohlavie a chcete pridať stĺpec s hodnotou Ms. alebo "Mr." v závislosti od pohlavia osoby. Vzorec by bol:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Zmeniť poradie kroku

• Na table Nastavenia dotazov v časti Použité kroky kliknitepravým tlačidlom myši na krok a potom vyberte položku Posunúť nahor aleboPosunúť nadol.

Odstránenie kroku

• Vyberte ikonu naľavo od kroku alebo kliknite pravým tlačidlom myši na krok a potom vyberte položku Odstrániť alebo Odstrániť do konca. Ikona odstrániť je k dispozícii aj naľavo od riadku vzorcov.

V tomto príklade skúsme konvertovať text v stĺpci do riadneho formátu pomocou kombinácie vzorcov v rozšírenom editore. 

Máte napríklad excelové tabuľky s názvom Objednávky so stĺpcom Názov Produktu, ktorý chcete konvertovať do riadneho formátu. 

Pred:

Po:

Pri vytváraní rozšíreného dotazu vytvoríte rad krokov vzorca dotazu na základe výrazu let.  Výraz let sa používa na priradenie názvov a vypočítanie hodnôt, na ktoré potom odkazuje klauzula in, ktorá definuje krok. V tomto príklade sa vráti rovnaký výsledok ako v časti Vytvorenie vzorca v riadku vzorcov.

let  
    Source = Text.Proper("hello world")
in  
    Source  

Uvidíte, že každý krok vychádza z predchádzajúceho kroku a odkazuje na krok jeho názvom. Pri pripomenutí sa v jazyku vzorcov Power Query rozlišujú malé a veľké písmená.

Fáza 1: Otvorenie rozšíreného editora

1. V Exceli vyberte položky Údaje> Získať > z iných >prázdny dotaz. Ďalšie informácie nájdete v téme Vytvorenie, načítanie alebo úprava dotazu v Exceli.

2. V editore Power Query vyberte položku Domov> rozšírený editor, ktorý sa otvorí so šablónou nechať výrazu.

Fáza 2: Definovanie zdroja údajov

1. Vytvorte výraz let pomocou funkcie Excel.CurrentWorkbook takto:
   
   let#x1
   
   in
       Source
   
   

2. Ak chcete dotaz načítať do hárka, vyberte položku Hotovo a potom vyberte položky Domov> Zavrieť & načítať > Zavrieť & Načítať.

Výsledok:

Fáza 3: Zvýšenie výšky prvého riadka na hlavičky

1. Ak chcete otvoriť dotaz, v hárku 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 (Power Query).

2. V editore Power Query vyberte položku Domov > Rozšírený editor, ktorý sa otvorí s príkazom, ktorý ste vytvorili vo fáze 2: Definovanie zdroja údajov.

3. Vo výraze let pridajte #"Prvý riadok ako hlavička" a Table.PromoteHeaders (funkcia Table.PromoteHeaders) takto:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
   
       #"First Row as Header"

4. Ak chcete dotaz načítať do hárka, vyberte položku Hotovo a potom vyberte položky Domov> Zavrieť & načítať > Zavrieť & Načítať.

Výsledok:

Fáza 4: Zmena jednotlivých hodnôt v stĺpci na riadny prípad

1. Ak chcete otvoriť dotaz, v hárku 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. V editore Power Query vyberte položku Domov > rozšírený editor, ktorý sa otvorí s príkazom vytvoreným vo fáze 3: Zvýšenieprvého riadka na hlavičky .

3. Vo výraze let konvertujte každú hodnotu stĺpca Názov ProductName na riadny text pomocou funkcie Table.TransformColumns a odkazujte na predchádzajúci krok vzorca dotazu Prvý riadok ako hlavička, pridajte k zdroju údajov slovo #"Každé slovo veľkými písmenami" a potom k výsledku priraďte reťazec #"Každé slovo veľkými písmenami".
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source),
       #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
       #"Capitalized Each Word"

4. Ak chcete dotaz načítať do hárka, vyberte položku Hotovo a potom vyberte položky Domov> Zavrieť & načítať > Zavrieť & Načítať.

Výsledok:

Správanie riadku vzorcov v editore Power Query môžete ovládať vo všetkých zošitoch.

Zobrazenie alebo skrytie riadku vzorcov

1. Vyberte položky> Možnosti a >Možnosti dotazu.

2. Na ľavej table v časti GLOBÁLNE vybertepoložku Power Query Editor.

3. Na pravej table v časti Rozloženie začiarknitealebo zrušte začiarknutie položky Zobraziť riadok vzorcov.

Zapnutie alebo vypnutie funkcie M Intellisense

1. Vyberte položky> Možnosti a >Možnosti dotazu.

2. Na ľavej table v časti GLOBÁLNE vybertepoložku Power Query Editor.

3. Na pravej table v časti Vzoreczačiarknite alebo zrušte začiarknutie políčka Povoliť funkciu M Intellisense v riadku vzorcov, v rozšírenom editore a v dialógovom okne vlastného stĺpca.

Poznámka    Zmena tohto nastavenia sa prejaví pri ďalšom otvorení okna editora Power Query.