Vytváření vzorců Power Query v Excelu

Pomocí Editoru Power Query jste celou dobu vytvářely vzorce Power Query. Podívejme se, jak Power Query funguje, když se podíváme pod kapotu. Informace o tom, jak aktualizovat nebo přidávat vzorce, se dozvíte tak, že se v akci díváte na Power Query Editor.  Pomocí rozšířeného editoru můžete dokonce zarolovat vlastní vzorce.           

Power Query Editor poskytuje datový dotaz a prostředí pro tvarování Excel, které můžete použít k přetvarování dat z mnoha zdrojů dat. Pokud chcete zobrazit okno Editoru power query, naimportujte dataz externích zdrojů dat v listu Excel, vyberte buňku v datech a pak vyberte Dotaz > Upravit. Následuje souhrn hlavních součástí.

Části editoru dotazů

  1. Pás karet Editoru Power Query, který používáte k utváření dat

  2. Podokno Dotazy, které slouží k vyhledání zdrojů dat a tabulek

  3. Kontextové nabídky, které jsou praktickými zkratkami příkazů na pásu karet

  4. Náhled dat, který zobrazuje výsledky kroků použitých u dat

  5. Podokno dotaz Nastavení, které obsahuje vlastnosti a každý krok v dotazu

Na pozadí je každý krok v dotazu založený na vzorci, který je viditelný v řádku vzorců.

Příklad vzorce v editoru dotazů

Možná budete chtít upravit nebo vytvořit vzorec. Vzorce používají jazyk vzorců Power Query, který můžete použít k vytvoření jednoduchých i složitých výrazů. Další informace o syntaxi, argumentech, poznámkách, funkcích a příkladech najdete v tématu Jazyk vzorců Power Query M.

Když jako příklad použijete seznam fotbalových šampionátů, použijte Power Query k tomu, aby se nezpracovaná data, která jste našli na webu, převedou na dobře formátovanou tabulku. Podívejte se, jak se pro každý úkol vytvářejí kroky dotazu a odpovídající vzorce v podokně Dotaz Nastavení v části Použité kroky a na řádku vzorců.

Váš prohlížeč nepodporuje přehrávání videa.

Postup

  1. Pokud chcete data importovat, vyberte Data> z webu, do pole ADRESA URL zadejte "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" a pak vyberte OK.

  2. V dialogovém okně Navigátor vyberte na levé straně tabulku Výsledky [Upravit] a dole vyberte Transformovat data. Zobrazí se editor Power Query.

  3. Pokud chcete změnit výchozí název dotazu, odstraňte v podokně Dotaz Nastavení v části Vlastnosti "Výsledky [Upravit]" a potom zadejte "UEFA champs".

  4. Pokud chcete odebrat nežádoucí sloupce, vyberte první, čtvrtý a pátý sloupec a pak vyberte Domů> Odebrat sloupec > Odebrat další sloupce.

  5. Pokud chcete nežádoucí hodnoty odebrat, vyberte Sloupec1, vyberte Domů> Nahradithodnoty , do pole Hodnoty k nalezení zadejte "podrobnosti" a pak vyberte OK.

  6. Pokud chcete odebrat řádky, ve které je slovo Rok, vyberte šipku filtru ve sloupci Sloupec1,zrušte zaškrtnutí políčka vedle položky Rok a pak vyberte OK.

  7. Pokud chcete záhlaví sloupců přejmenovat, poklikejte na každé z nich a změňte sloupec1 na Rok, Sloupec4 na Výherce a Sloupec5 na Konečný výsledek.

  8. Pokud chcete dotaz uložit, vyberte Domů> Zavřít & Načíst.

Výsledek

Výsledky návodu – prvních několik řádků

Následující tabulka obsahuje souhrn každého použitého kroku a odpovídajícího vzorce.

Krok a úkol dotazu

Vzorec

Zdrojová měna

Připojení ke zdroji dat na webu

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Navigace

Vyberte tabulku, která se má připojit.

=Source{2}[Data]

Změněný typ

Změna datových typů (které Power Query dělá 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}})

Odebrání dalších sloupců

Odebrání ostatních sloupců tak, aby se zobrazovaly jenom potřebné sloupce

= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})

Nahrazená hodnota

Nahrazení hodnot k vyčištění hodnot ve vybraném sloupci

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})

Filtrované řádky

Filtrování hodnot ve sloupci

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

Přejmenované sloupce

Změněná záhlaví sloupců tak, aby byla smysluplná

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Důležité:    Buďte opatrní při úpravách kroků Zdroj, Navigacea Změněný typ, protože jsou vytvořené v Power Query a definují a   nastavují zdroj dat.

Zobrazení nebo skrytí řádku vzorců

Ve výchozím nastavení se zobrazuje řádek vzorců, ale pokud není viditelný, můžete ho znovu zobrazit.

  • Vyberte Zobrazení > rozložení > řádku vzorců.

Edit a formula in the formula bar

  1. Pokud chcete otevřít dotaz, vyhledejte dříve načtený dotaz z Power Query Editoru, vyberte buňku v datech a pak vyberte Dotaz > Upravit. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excel.

  2. V podokně Nastavení dotazu v části Použité krokyvyberte krok, který chcete upravit.

  3. Na řádku vzorců vyhledejte a změňte hodnoty parametrů a pak vyberte ikonu Enter Ikona Enter nalevo od řádku vzorců v Power Query nebo stiskněte Enter. Například změňte tento vzorec tak, aby se uchová sloupec2:

    Před: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    Za:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Vyberte ikonu Ikona Enter nalevo od řádku vzorců v Power Query nebo stisknutím klávesy Enter zobrazte nové výsledky zobrazené v náhledu dat.

  5. Pokud chcete zobrazit výsledek v Excel listu, vyberte Domů> Zavřít & Načíst.

Vytvoření vzorce v řádku vzorců

V jednoduchém příkladu vzorce převedeme textovou hodnotu na velká a velká písmena pomocí funkce Text.Proper.

  1. Pokud chcete otevřít prázdný dotaz, vyberte Excel Data> Data > z jiných zdrojů > Prázdný dotaz. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excel.

  2. Na řádku vzorců zadejte=Text.Proper("text value")a pak vyberte ikonu Enter Ikona Enter nalevo od řádku vzorců v Power Query nebo stiskněte Enter.

    Výsledky se zobrazí v náhledu dat .

  3. Pokud chcete zobrazit výsledek v Excel listu, vyberte Domů> Zavřít & Načíst.

Výsledek

Text.Proper

 Když vytvoříte vzorec, Power Query ověří syntaxi vzorce. Když ale vložíte, přeuspořádáte nebo odstraníte zprostředkující krok v dotazu, můžete dotaz potenciálně přerušit.  Vždy ověřte výsledky v náhledu dat.

Důležité:    Buďte opatrní při úpravách kroků Zdroj, Navigacea Změněný typ, protože jsou vytvořené v Power Query a definují a   nastavují zdroj dat.

Úprava vzorce pomocí dialogového okna

Tato metoda umožňuje používat dialogová okna, která se liší v závislosti na kroku. Syntaxi vzorce nemusíte znát.

  1. Pokud chcete otevřít dotaz, vyhledejte dříve načtený dotaz z Power Query Editoru, vyberte buňku v datech a pak vyberte Dotaz > Upravit. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excel.

  2. V podokně Nastavení dotazu v části Použité kroky vyberte ikonu Upravit Nastavení Ikona Nastavení kroku, který chcete upravit, nebo klikněte pravým tlačítkem myši na krok Ikona Nastavení pak vyberte Upravit Nastavení.

  3. V dialogovém okně proveďte požadované změny a pak vyberte OK.

Vložení kroku

Po dokončení kroku dotazu, který změní tvar dat, se krok dotazu přidá pod aktuální krok dotazu. ale když vložíte krok dotazu doprostřed kroků, může v dalších krocích dojít k chybě. Power Query zobrazí upozornění Vložit krok, když se pokusíte vložit nový krok a nový krok změní pole, například názvy sloupců, která se používají v libovolném z kroků, které následují po vloženém kroku.

  1. V podokně Nastavení dotazu v části Použitékroky vyberte krok, který chcete bezprostředně předcházet novému kroku a příslušnému vzorci.

  2. Vlevo od řádku vzorců Ikona Funkce ikonu Přidat krok. Můžete také kliknout pravým tlačítkem myši na krok a pak vybrat Vložit krok za.Vytvoří se nový vzorec ve formátu :

    = <nameOfTheStepToReference>, například =Production.WorkOrder.

  3. Zadejte nový vzorec pomocí formátu:

    =Class.Function(ReferenceStep[,otherparameters])

    Předpokládejme například, že máte tabulku se sloupcem Pohlaví a chcete přidat sloupec s hodnotou Ms. nebo "Pan", v závislosti na pohlaví osoby. Vzorec by byl:

    =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Příklad vzorce

Změna pořadí kroku

  • V podokně Dotazy Nastavení v části Použité krokyklikněte pravým tlačítkem myši na krok a pak vyberte Přesunout nahoru nebo Přesunout dolů.

Odstranit krok

  • Vyberte ikonu Odstranit krok vlevo od kroku nebo klikněte pravým tlačítkem myši na krok a pak vyberte Odstranit nebo Odstranit až do konce. Nalevo od řádku vzorců je k dispozici také ikona Odstranit Odstranit krok .

V tomto příkladu převedeme text ve sloupci na velká a malá písmena pomocí kombinace vzorců v rozšířeném editoru. 

Máte třeba tabulku Excel objednávky se sloupcem ProductName, který chcete převést na velká a malá písmena. 

Před:

Před

Po:

Krok 4 – výsledek

Když vytvoříte rozšířený dotaz, vytvoříte řadu kroků vzorce dotazu na základě let výrazu.  Pomocí výrazu let můžete přiřadit názvy a vypočítat hodnoty, na které pak odkazuje klauzule in, která definuje krok. Tento příklad vrátí stejný výsledek jako výsledek v části Vytvoření vzorce v řádku vzorců.

let  
    Source = Text.Proper("hello world")
in  
    Source  

Uvidíte, že každý krok vychází z předchozího kroku odkazem na krok za názvem. Připomínáme, že v jazyce vzorců Power Query se rozlišují malá a velká písmena.

Fáze 1: Otevření rozšířeného editoru

  1. V Excel vyberte Data> Získat data > jiné zdroje > prázdný dotaz. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excel.

  2. V Power Query Editoru vyberte Home> Advanced Editor, který se otevře se šablonou let výrazu.

Rozšířený editor2

Fáze 2: Definování zdroje dat

  1. Vytvořte výraz let pomocí Excel. Funkce CurrentWorkbook je následující:

    let#x1

    in
        Source

    Krok 1 – rozšířený editor

  2. Pokud chcete dotaz načíst do listu, vyberteHotovo a potom vyberte Domů > Zavřít & Načíst > Zavřít & Načíst.

Výsledek

Krok 1 – výsledek

Fáze 3: Zvýšení prvního řádku na záhlaví

  1. Pokud chcete dotaz otevřít, vyberte na listu buňku v datech a pak vyberte Dotaz > Upravit. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excel (Power Query).

  2. V Power Query Editoru vyberte Home > Advanced Editor(Rozšířený editor), který se otevře s příkazem, který jste vytvořili ve fázi 2: Definování zdroje dat.

  3. Ve výrazu let přidejte funkce #"První řádek jako záhlaví"


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

        #"First Row as Header"

  4. Pokud chcete dotaz načíst do listu, vyberteHotovo a potom vyberte Domů > Zavřít & Načíst > Zavřít & Načíst.

Výsledek

Krok 3 – výsledek

Fáze 4: Změna jednotlivých hodnot ve sloupci na velká a malá písmena

  1. Pokud chcete dotaz otevřít, vyberte na listu buňku v datech a pak vyberte Dotaz > Upravit. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excel.

  2. V Power Query Editoru vyberte Home > Advanced Editor, který se otevře s příkazem, který jste vytvořili ve fázi 3:Zvýšení prvního řádku na záhlaví .

  3. Ve výrazu let převeďte každou hodnotu sloupce ProductName na správný text pomocí funkce Table.TransformColumns s odkazem na předchozí krok vzorce dotazu "První řádek jako záhlaví", přidejte ke zdroji dat #"Velké slovo" a potom k výsledku přiřaďte #"Každé slovo velkými písmeny".

    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. Pokud chcete dotaz načíst do listu, vyberteHotovo a potom vyberte Domů > Zavřít & Načíst > Zavřít & Načíst.

Výsledek

Krok 4 – výsledek

Chování řádku vzorců můžete řídit v Power Query Editoru pro všechny sešity.

Zobrazení nebo skrytí řádku vzorců

  1. Vyberte Možnosti> a Nastavení > Dotazu.

  2. V levém podokně v části GLOBALvyberte Power Query Editor.

  3. V pravém podokně v části Rozloženívyberte nebo zrušte zaškrtnutí políčka Zobrazit řádek vzorců.

Zapnutí nebo vypnutí technologie M IntelliSense

  1. Vyberte Možnosti> a Nastavení > Dotazu .

  2. V levém podokně v části GLOBALvyberte Power Query Editor.

  3. V pravém podokně v části Vzorecvyberte nebo zrušte zaškrtnutí políčka Povolit funkci M IntelliSense v řádku vzorců, rozšířeném editoru a dialogovém okně vlastního sloupce.

Poznámka:    Změna tohoto nastavení se projeví při příštím otevření okna Editoru power query.

Viz také

Nápověda pro doplněk Power Query pro Excel

Vytvoření a vyvolání vlastní funkce

Použití seznamu Použité kroky (docs.com)

Použití vlastních funkcí (docs.com)

Vzorce M v Power Query (docs.com)

Řešení chyb (docs.com)

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é?

×