Vytvorenie vzorcov Power Query v Exceli

Práve pomocou editora Power Query vytvárate všetky vzorce Power Query. Pozrime sa, ako Power Query funguje, pohľadom pod kuchyne. Informácie o aktualizácii alebo pridávaní vzorcov môžete získať sledovaním editora Power Query v akcii.  Pomocou rozšíreného editora môžete dokonca použiť aj vlastné vzorce.     

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í.

Súčasti editora dotazov

  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.

Vzorový vzorec Editora dotazov

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.

Výsledky postupu – niekoľko prvých riadkov

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 Ikona Zadať naľavo od riadku vzorcov v doplnku Power Query 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 Ikona Zadať naľavo od riadku vzorcov v doplnku Power Query ú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ť Ikona Zadať naľavo od riadku vzorcov v doplnku Power Query 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:

Výber animácie, ktorá sa má spustiť

 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 Ikona 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ť Ikona Funkcia 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.")

Príklad vzorca

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 Odstránenie kroku 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ť Odstránenie kroku 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:

Vývojový diagram s červenými spojovacími bodmi.

Po:

Krok 4 – Výsledok

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.

Zostava týkajúca sa zamestnancov v zobrazení Ukážka pred tlačou

Fáza 2: Definovanie zdroja údajov

  1. Vytvorte výraz let pomocou funkcie Excel.CurrentWorkbook takto:

    let#x1

    in
        Source

    Dialógové okno Lupa

  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:

Matematický symbol

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:

Krok 3 – 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:

Krok 4 – 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.

Pozrite tiež

Pomocník doplnku Power Query for Excel

Použitie zoznamu Použité kroky (v docs.com)

Používanie vlastných funkcií (docs.com)

Vzorce M power query (docs.com)

Riešenie chýb (docs.com)

Potrebujete ďalšiu pomoc?

Rozšírte svoje zručnosti práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×