Vytváření vzorců Power Query v Excelu

Poznámka: Tento článek svou práci splnil a brzy zmizí. Abychom předešli strastem s hláškou „Stránka nebyla nalezena“, odebíráme odkazy, o kterých víme. Pokud jste na tuto stránku vytvořili nějaké odkazy, odeberte je prosím. Společně tak zajistíme lepší orientaci na webu.

Poznámka:  Doplněk Power Query se v Excelu 2016 jmenuje Načíst a transformovat. Informace v tomto článku se týkají obou verzí. Další podrobnosti najdete v tématu Funkce Načíst a transformovat v Excelu 2016.

K vytvoření vzorců Power Query v Excelu můžete použít řádek vzorců Editorudotazů nebo Rozšířený editor. Editor dotazů je nástroj, který je součástí Power Query, který umožňuje vytvářet datové dotazy a vzorce v Power Query. Jazyk používaný k vytvoření těchto vzorců je jazyk vzorců Power Query. Existuje mnoho vzorců Power Query, které můžete použít ke zjišťování, kombinování a upřesnění dat. Další informace o celé oblasti vzorců Power Query najdete v tématu Kategorie vzorců Power Query.

Vytvoříme nejdřív jednoduchý a potom složitější vzorec.

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.

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

  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:

Text.Proper

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.

Poznámka: Tohle téma je úvodem ke složitějším vzorcům Power Query. Jestli se chcete o vzorcích Power Query dozvědět víc, podívejte se na téma Další informace o vzorcích Power Query.

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:

Před změnou

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

Po změně

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.

    Rozšířený editor

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

    Rozšířený editor2

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:

    Rozšířený editor3
  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.

Krok 1 – rozšířený editor

Výsledek bude v sešitu vypadat takhle:

Krok 1 – výsledek

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 3 – výsledek

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:

Krok 4 – výsledek

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.

Potřebujete další pomoc?

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

×