Vytvorenie vzorcov Power Query v Exceli

Vytvorenie jednoduchého vzorca

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

1. Na karte POWER QUERY na páse s nástrojmi vyberte položky Z iných zdrojov > Prázdny dotaz.

   

2. Do riadku vzorcov editora dotazov zadajte = Text.Proper("text value") a stlačte kláves Enter alebo vyberte ikonu Enter.

3. Power Query zobrazí výsledky na table výsledkov vzorcov.

4. Ak chcete zobraziť výsledok v excelovom hárku, vyberte možnosť Zavrieť a načítať.

Výsledok v hárku bude vyzerať takto:

V editore dotazov môžete vytvoriť aj vzorce rozšírených dotazov.

Vytvorenie rozšíreného vzorca

Ako príklad rozšíreného vzorca skúsme konvertovať text v stĺpci do riadneho formátu pomocou kombinácie vzorcov. Ak chcete do krokov dotazu, ktorého výsledkom je množina údajov, skombinovať viacero vzorcov, môžete použiť jazyk vzorcov Power Query. Výsledok je možné importovať do excelového hárka.

Povedzme, že máte excelovú tabuľku s názvami produktov, ktoré chcete konvertovať do riadneho formátu.

Pôvodná tabuľka vyzerá takto:

Výsledná tabuľka by mala vyzerať takto:

Prejdime kroky vzorca dotazu a zmeňme pôvodnú tabuľku tak, aby boli hodnoty v stĺpci NázovProduktu v riadnom formáte.

Ukážka rozšíreného dotazu s použitím rozšíreného editora

Ak chcete vyčistiť pôvodnú tabuľku, vytvorte s použitím rozšíreného editora kroky vzorca dotazu. Skúsme vytvoriť jednotlivé kroky vzorca dotazu a ukážme si, ako vytvoriť rozšírený dotaz. Nižšie nájdete kompletný zoznam krokov vzorca dotazu. Pri vytváraní rozšíreného dotazu použite tento postup:

• Vytvorte sériu krokov vzorca dotazu, ktorých názov sa začína príkazom let. Jazyk vzorcov Power Query rozlišuje malé a veľké písmená.

• Každý krok vzorca dotazu nadväzuje na predchádzajúci krok a odkazuje na krok jeho názvom.

• Vytvorte krok vzorca dotazu pomocou príkazu in. Spravidla sa ako posledný krok dotazu používa posledný výsledok množiny údajov s príkazom in.

Krok 1 – Otvorenie rozšíreného editora

1. Na karte POWER QUERY na páse s nástrojmi vyberte položky Z iných zdrojov > Prázdny dotaz.

2. V editore dotazov vyberte položku Rozšírený editor.

   

3. Zobrazí sa rozšírený editor.

   

Krok 2 – Definovanie pôvodného zdroja

V rozšírenom editore:

1. Použite príkaz let, ktorý priradí vzorec Zdroj = Excel.CurrentWorkbook(). Potom sa ako zdroj údajov použije excelová tabuľka. Ďalšie informácie o vzorci Excel.CurrentWorkbook() nájdete v téme Excel.CurrentWorkbook.

2. Priraďte príkaz Zdroj k výsledku príkazu in.

   let Source =
   Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in Source

3. Rozšírený dotaz bude v rozšírenom editore vyzerať takto.

   

4. Ak chcete zobraziť výsledky v hárku:

   1. Kliknite na položku Hotovo.

   2. Na páse s nástrojmi editora dotazov kliknite na položku Zavrieť a načítať.

Výsledok v hárku bude vyzerať takto:

Krok 3: Zvýšenie úrovne prvého riadka na hlavičky

Ak chcete hodnoty v stĺpci NázovProduktu konvertovať na riadny text, najprv musíte zvýšiť úroveň prvého riadka na hlavičky stĺpcov. To môžete spraviť v rozšírenom editore:

1. Pridajte vzorec #"Prvý riadok ako hlavička" = Table.PromoteHeaders() do krokov vzorca dotazu a odkážte na Zdroj ako na zdroj údajov. Ďalšie informácie o vzorci Table.PromoteHeaders() nájdete v téme Table.PromoteHeaders.

2. Priraďte #"Prvý riadok ako hlavička" k výsledku príkazu in.

   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source)
   in
       #"First Row as Header"

Výsledok v hárku bude vyzerať takto:

Krok 4 – Zmena jednotlivých hodnôt v stĺpci do riadneho formátu

Ak chcete skonvertovať každú hodnotu stĺpca NázovProduktu na riadny text, použite vzorec Table.TransformColumns() a odkážte na krok vzorca dotazu Prvý riadok ako hlavička. To môžete spraviť v rozšírenom editore:

1. Pridajte vzorec #"Každé slovo veľkými písmenami" = Table.TransformColumns() do krokov vzorca dotazu a odkážte na položku #"Prvý riadok ako hlavička" ako na zdroj údajov. Ďalšie informácie o vzorci Table.TransformColumns() nájdete v téme Table.TransformColumns.

2. Priraďte položku #"Každé slovo veľkými písmenami" k výsledku príkazu in.

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"

Konečný výsledok zmení jednotlivé hodnoty v stĺpci NázovProduktu na riadny formát a v hárku to bude vyzerať takto:

Pomocou jazyka vzorcov Power Query môžete vytvoriť jednoduché až rozšírené dotazy na údaje, ktoré umožňujú zisťovanie, kombinovanie a spresňovanie údajov. Ďalšie informácie o doplnku Power Query nájdete v téme Pomocník doplnku Microsoft Power Query for Excel.