Vytvorenie vzorcov Power Query v Exceli

Editor Power Query poskytuje dotaz na údaje a možnosti tvarovania pre Excel, ktoré môžete použiť na zmenu tvarovania údajov z mnohých zdrojov údajov. Ak chcete zobraziť okno Editor Power Query, importujte údaje z externých zdrojov údajovv excelovom hárku, vyberte bunku v údajoch a potom vyberte položku Dotaz > Upraviť. Nasleduje súhrn hlavných súčastí.

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

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

3. Kontextové ponuky, ktoré sú vhodnými klávesovými skratkami pre príkazy na páse s nástrojmi

4. Ukážka údajov, ktorá zobrazuje výsledky krokov použitých na údaje

5. Tabla Nastavenia dotazu so zoznamom vlastností a jednotlivými krokmi v dotaze

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

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

Používa zoznam futbalových šampionátov ako príklad, použite Power Query vziať nespracované údaje, ktoré ste našli na webovej lokalite a premeniť ich na dobre formátovanú tabuľku. Pozrite si, ako sa vytvárajú kroky dotazu a zodpovedajúce vzorce pre každú úlohu na table Nastavenia dotazu v časti Použité kroky a v riadku vzorcov.

Postup

1. Ak chcete importovať údaje, vyberte položku Údaje > Z webu, do poľa URL adresy zadajte "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" a potom vyberte tlačidlo OK.

2. V dialógovom okne Navigátor vyberte tabuľku Výsledky [Upraviť] na ľavej strane a potom v dolnej časti vyberte položku Transformovať údaje . Zobrazí sa editor Power Query.

3. Ak chcete zmeniť predvolený názov dotazu, na table Nastavenia dotazu v časti Vlastnosti odstráňte položku Výsledky [Upraviť] a potom zadajte výraz "Šampióni UEFA".

4. Ak chcete odstrániť nechcené stĺpce, vyberte prvý, štvrtý a piaty stĺpec a potom vyberte položku Domov > Odstrániť stĺpec > Odstrániť ostatné stĺpce.

5. Ak chcete odstrániť nežiaduce hodnoty, vyberte položku Stĺpec1, vyberte položku Domov > Nahradiť hodnoty, do poľa Hodnoty na vyhľadanie zadajte "podrobnosti" a potom vyberte tlačidlo OK.

6. Ak chcete odstrániť riadky so slovom "Year" (Rok), vyberte šípku filtra v stĺpci Stĺpec1, zrušte začiarknutie políčka vedľa položky Year (Rok) a potom vyberte tlačidlo OK.

7. Ak chcete premenovať hlavičky stĺpcov, dvakrát kliknite na každú z nich a potom zmeňte stĺpec "Column1" na "Year", "Column4" na "Winner" (Víťaz) a "Column5" (Stĺpec5) na "Final Score" (Konečné skóre).

8. Ak chcete dotaz uložiť, vyberte položku Domov > Zavrieť & Načítať.

Výsledok

Nasledujúca tabuľka je súhrn každého použitého kroku a príslušného vzorca.

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 vykonáva automaticky)	= 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 Zmenené hlavičky stĺpcov tak, aby boli zmysluplné	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Dôležité    Pri úpravách krokov Zdroj, Navigácia a Zmenený typ buďte opatrní,  pretože sú vytvorené Power Query na definovanie a nastavenie zdroja údajov.

Zobrazenie alebo skrytie riadka vzorcov

Riadok vzorcov sa predvolene zobrazuje, ale ak nie je viditeľný, môžete ho znova zobraziť.

• Vyberte položku Zobraziť > rozloženie > riadok vzorcov.

Edita formula in the formula bar

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. Na table Nastavenia dotazu vyberte v časti Použité kroky krok, ktorý chcete upraviť.

3. V riadku vzorcov vyhľadajte a zmeňte hodnoty parametrov a potom vyberte ikonu Enter alebo stlačte kláves Enter. Zmeňte napríklad tento vzorec tak, aby sa zachoval aj stĺpec 2:
   
   Pred: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Po:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Vyberte ikonu Enter alebo stlačením klávesu Enter zobrazte nové výsledky zobrazené v ukážke údajov.

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

Vytvorenie vzorca v riadku vzorcov

V príklade jednoduchého vzorca skonvertujme textovú hodnotu na riadne veľké a malé písmená pomocou funkcie Text.Proper.

1. Ak chcete otvoriť prázdny dotaz, v Exceli vyberte položku Ú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 riadka vzorcov zadajte=Text.Proper("text value")a potom vyberte ikonu Enter 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žku Domov > Zavrieť & Načítať.

Výsledok:

 Pri vytváraní vzorca Power Query overí syntax vzorca. Pri vkladaní, zmene poradia alebo odstránení medzikroku v dotaze však môže dôjsť k porušeniu dotazu.  Vždy overte výsledky v ukážke údajov.

Dôležité    Pri úpravách krokov Zdroj, Navigácia a Zmenený typ buďte opatrní,  pretože sú vytvorené Power Query na definovanie a nastavenie zdroja údajov.

Úprava vzorca pomocou dialógového okna

Táto metóda používa dialógové okná, ktoré sa líšia v závislosti od kroku. Syntax vzorca nemusíte poznať.

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. 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 zmeny a potom vyberte tlačidlo OK.

Vloženie kroku

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

1. Na table Nastavenia dotazu v časti Použité kroky vyberte krok, ktorý má byť bezprostredne pred novým krokom a jeho príslušným vzorcom.

2. Vyberte ikonu Pridať krok naľavo od riadka vzorcov. Prípadne kliknite pravým tlačidlom myši na krok a potom vyberte 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 "Pani". alebo "Pán", v závislosti od pohlavia osoby. Vzorec by bol:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Zmena poradia kroku

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

Odstránenie kroku

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

V tomto príklade konvertujme text v stĺpci na riadne veľké a malé písmená pomocou kombinácie vzorcov v Rozšírený editor. 

Máte napríklad excelovú tabuľku s názvom Objednávky so stĺpcom NázovProduktu, ktorý chcete konvertovať na správny prípad. 

Pred:

Po:

Keď vytvoríte rozšírený dotaz, vytvoríte rad krokov vzorca dotazu na základe výrazu let. Výraz let sa používa na priradenie názvov a výpočet hodnôt, na ktoré potom odkazuje klauzula In , ktorá definuje krok. V tomto príklade sa vráti rovnaký výsledok ako výsledok 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 tak, že odkazuje na krok podľa názvu. Pripomíname, že v jazyku vzorcov Power Query sa rozlišuje malé a veľké písmená.

Fáza 1: Otvorenie Rozšírený editor

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

2. V Editor Power Query vyberte položku Domov > Rozšírený editor, ktorá sa otvorí so šablónou výrazu let.

Fáza 2: Definovanie zdroja údajov

1. Vytvorte výraz let pomocou funkcie Excel.CurrentWorkbook takto:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

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

Výsledok:

Fáza 3: Zvýšenie úrovne 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 Editor 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čku" a funkciu Table.PromoteHeaders takto:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #x4#"First Row as Header" = Table.PromoteHeaders(Source)#x3
   

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

Výsledok:

Fáza 4: Zmena jednotlivých hodnôt v stĺpci na riadne veľké a malé písmená

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 Editor Power Query vyberte položku Domov > Rozšírený editor, ktorá sa otvorí s príkazom vytvoreným vo fáze 3: Zvýšenie úrovne prvého riadka na hlavičky.

3. Vo výraze let konvertujte každú hodnotu stĺpca NázovProduktu na správny text pomocou funkcie Table.TransformColumns, odkazujúc na predchádzajúci krok vzorca dotazu Prvý riadok ako hlavička, pridaním hodnoty #"Každý Word veľkými písmenami" do zdroja údajov a následným priradením hodnoty #"Každý Word veľkými písmenami" k výsledku.
   
   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žku Domov > Zavrieť & Načítať > Zavrieť & Načítať.

Výsledok:

Správanie riadka vzorcov v Editor Power Query môžete ovládať pre všetky zošity.

Zobrazenie alebo skrytie riadka vzorcov

1. Vyberte položku Možnosti a nastavenia> súboru > možnosti dotazu.

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

3. Na pravej table v časti Rozloženie vyberte alebo zrušte začiarknutie políčka Zobraziť riadok vzorcov.

Zapnutie alebo vypnutie M Intellisense

1. Vyberte položky Možnosti a nastavenia> súboru > možnosti dotazu.

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

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

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