Vytváření vzorců Power Query v Excelu

Vytvoření jednoduchého vzorce

Jako příklad jednoduchého vzorce převedeme textovou hodnotu na formát s velkými počátečními písmeny slov pomocí vzorce Text.Proper() .

1. Na pásu karet na kartě POWER QUERY zvolte Z jiných zdrojů > Prázdný dotaz.

   

2. V řádku Editoru vzorců napište = Text.Proper("text value") a stiskněte klávesu Enter nebo zvolte ikonu .

3. Power Query zobrazí výsledky v podokně výsledků vzorců.

4. Pokud chcete výsledky zobrazit v excelovém sešitu, zvolte Zavřít a načíst.

Výsledek bude v sešitu vypadat takhle:

V Editoru dotazů se dají vytvářet i složitější vzorce dotazů.

Vytvoření složitějšího vzorce

Jako příklad složitějšího vzorce převedeme text ve sloupci na formát s velkými počátečními písmeny slov pomocí kombinace vzorců. Pomocí jazyka vzorců Power Query můžete zkombinovat několik vzorců do kroků dotazu, které budou jako výsledek vracet sadu dat. Výsledek se dá importovat do excelového sešitu.

Předpokládejme například, že máte excelovou tabulku s názvy produktů, které chcete převést na formát s velkými počátečními písmeny slov.

Původní tabulka vypadá takhle:

A výsledná tabulka by měla vypadat takhle:

Pojďme si projít jednotlivé kroky vytvoření vzorce dotazu, které změní původní tabulku tak, aby hodnoty ve sloupci ProductName měly velká počáteční písmena slov.

Příklad složitějšího dotazu pomocí Rozšířeného editoru

V Rozšířeném editoru vytvoříme kroky vzorce dotazu pro vyčištění původní tabulky. Vytvořením jednotlivých kroků vzorce dotazu ukážeme, jak se vytvářejí složitější dotazy. Kompletní kroky pro vytvoření vzorce dotazu jsou uvedené níže. Při vytváření složitějšího dotazu postupujte takto:

• Vytvořte řadu kroků vzorce dotazu, které začínají příkazem let. Pamatujte na to, že jazyk vzorců Power Query rozlišuje velká a malá písmena.

• Každý krok vzorce dotazu vychází z předchozího kroku – odkazuje na jeho název.

• K výstupu kroku vzorce dotazu slouží příkaz in. Obecně platí, že poslední krok dotazu slouží k vrácení konečné sady dat.

Krok 1 – otevření Rozšířeného editoru

1. Na pásu karet na kartě POWER QUERY zvolte Z jiných zdrojů > Prázdný dotaz.

2. V Editoru dotazů zvolte Rozšířený editor.

   

3. Zobrazí se Rozšířený Editor.

   

Krok 2 – definování původního zdroje

V Rozšířeném editoru:

1. Použijte příkaz let Source = Excel.CurrentWorkbook(). Tím se excelová tabulka použije jako zdroj dat. Další informace o vzorci Excel.CurrentWorkbook() najdete v tématu Excel.CurrentWorkbook.

2. Výsledku příkazu in přiřaďte Source.

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

3. Váš složitější dotaz bude v Rozšířeném editoru vypadat takhle:

   

4. Pokud chcete výsledky zobrazit v listu:

   1. Klikněte na Hotovo.

   2. Na pásu karet Editoru dotazů klikněte na Zavřít a načíst.

Výsledek bude v sešitu vypadat takhle:

Krok 3 – zvýšení úrovně prvního řádku na záhlaví

Dřív než hodnoty ve sloupci ProductName převedete na text s velkými počátečními písmeny slov, zvyšte úroveň prvního řádku na záhlaví sloupců. Uděláte to v Rozšířeném editoru:

1. Přidejte ke krokům vzorce dotazu vzorec #"First Row as Header" = Table.PromoteHeaders() a odkažte se na Source jako zdroj dat. Další informace o vzorci Table.PromoteHeaders() najdete v tématu Table.PromoteHeaders.

2. Výsledku příkazu in přiřaďte #"First Row as Header".

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

Výsledek bude v sešitu vypadat takhle:

Krok 4 – změna každé hodnoty ve sloupci na formát s velkými počátečními písmeny slov

Abyste převedli každou hodnotu ve sloupci ProductName na text s velkými počátečními písmeny slov, použijete Table.TransformColumns() a odkážete se na krok vzorce dotazu "First Row as Header“. Uděláte to v Rozšířeném editoru:

1. Přidejte ke krokům vzorce dotazu vzorec #"Capitalized Each Word" = Table.TransformColumns() a odkažte se na #"First Row as Header" jako zdroj dat. Další informace o vzorci Table.TransformColumns() najdete v tématu Table.TransformColumns.

2. Výsledku příkazu in přiřaďte #"Capitalized Each Word".

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"

V konečném výsledku bude každá hodnota ve sloupci ProductName změněná na formát s velkými počátečními písmeny slov a bude v listu vypadat takhle:

Pomocí jazyka vzorců Power Query můžete vytvářet jednoduché i složitější datové dotazy pro zjišťování, kombinování a upřesňování dat. Další informace o Power Query najdete v Nápovědě pro Microsoft Power Query pro Excel.