Pomocí Editor Power Query jste celou dobu vytvářeli Power Query vzorce. Podívejme se, jak Power Query funguje, když se podíváme pod pokličku. Informace o tom, jak aktualizovat nebo přidat vzorce, se dozvíte jenom tak, že se díváte na Editor Power Query v akci.  Pomocí Rozšířený editor můžete dokonce rolovat vlastní vzorce.           

Editor Power Query poskytuje dotaz na data a prostředí pro formování pro Excel, které můžete použít k přetváření dat z mnoha zdrojů dat. Pokud chcete zobrazit okno Editor Power Query, importujte data z externích zdrojů datv excelovém listu, vyberte buňku v datech a pak vyberte Dotaz > Upravit. Následuje souhrn hlavních komponent.

Části editoru dotazů

  1. Pás karet Editor Power Query, který používáte k tvarování dat

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

  3. Místní nabídky, které jsou praktickými zástupci příkazů na pásu karet

  4. Náhled dat zobrazující výsledky kroků použitých u dat

  5. Podokno Nastavení dotazu se seznamem vlastností a jednotlivých kroků dotazu

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

Příklad vzorce v editoru dotazů

Může se stát, že budete chtít vzorec upravit nebo vytvořit. Vzorce používají Power Query jazyk vzorců, který můžete použít k vytvář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 Power Query jazyk vzorců M.

Pokud použijete jako příklad seznam fotbalových mistrovství, použijete Power Query k tomu, abyste mohli vzít nezpracovaná data, která jste našli na webu, a převést je na dobře formátovanou tabulku. V podokně Nastavení dotazů v části Použitý postup a na řádku vzorců můžete sledovat, jak se pro jednotlivé úlohy vytvářejí kroky dotazu a odpovídající vzorce.

Váš prohlížeč nepodporuje video. Nainstalujte si Microsoft Silverlight, Adobe Flash Player nebo Internet Explorer 9.

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 Results [Edit] (Výsledky [Upravit] a pak dole vyberte Transformovat data ). Zobrazí se editor Power Query.

  3. Pokud chcete změnit výchozí název dotazu, v podokně Nastavení dotazu v části Vlastnosti odstraňte "Results [Edit]" (Výsledky [Upravit]) a pak zadejte "LOCKER champs".

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

  5. Pokud chcete odebrat nežádoucí hodnoty, vyberte Sloupec1, vyberte Domů > Nahradit hodnoty, do pole Nalezené hodnoty zadejte "podrobnosti" a pak vyberte OK.

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

  7. Pokud chcete záhlaví sloupců přejmenovat, poklikejte na ně a změňte Sloupec1 na Rok, Sloupec4 na Vítěz a Sloupec5 na Konečné skóre.

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

Výsledek

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

Následující tabulka obsahuje souhrn jednotlivých použitých kroků a odpovídající vzorec.

Krok a úkol dotazu

Vzorec

Zdroj

Připojení k webovému zdroji dat

= 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ů (což 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}})

Odebrané ostatní sloupce

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

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

Nahrazená hodnota

Nahrazení hodnot pro 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ěna záhlaví sloupců tak, aby byla smysluplná

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

Důležité:    Při úpravách kroků Zdroj, Navigace  a Změněný typ buďte opatrní, protože jsou vytvořené Power Query k definování a nastavení zdroje dat.

Zobrazení nebo skrytí řádku vzorců

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

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

Edit vzorec v řádku vzorců

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

  2. V podokně Nastavení dotazů v části Použitý postup vyberte 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 Klávesu Enter. Změňte například tento vzorec tak, aby se zachoval také sloupec Column2:Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Výběrem ikony Enter Ikona Enter nalevo od řádku vzorců v Power Query nebo stisknutím klávesy Enter zobrazte nové výsledky v náhledu dat.

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

Vytvoření vzorce v řádku vzorců

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

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

  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 excelovém listu, vyberte Domů > Zavřít & Načíst.

Výsledek

Text.Proper

 Když vytvoříte vzorec, Power Query ověří syntaxi vzorce. Když ale vložíte, změníte pořadí nebo odstraníte mezikrok v dotazu, můžete dotaz potenciálně přerušit.  Vždy ověřte výsledky v náhledu dat.

Důležité:    Při úpravách kroků Zdroj, Navigace  a Změněný typ buďte opatrní, protože jsou vytvořené Power Query k definování a nastavení zdroje dat.

Úprava vzorce pomocí dialogového okna

Tato metoda používá dialogová okna, která se liší v závislosti na kroku. Nemusíte znát syntaxi vzorce.

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

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

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

Vložení kroku

Po dokončení kroku dotazu, který mění tvar dat, se pod aktuální krok dotazu přidá. Když ale vložíte krok dotazu doprostřed kroků, může v následující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žijí v některém z kroků, které následují po vloženého kroku.

  1. V podokně Nastavení dotazů v části Použitý postup vyberte krok, který má bezprostředně předcházet novému kroku a odpovídajícímu vzorci.

  2. Vyberte ikonu Přidat krok Ikona Funkce vlevo od řádku vzorců. Případně klikněte pravým tlačítkem na krok a pak vyberte Vložit krok za. Vytvoří se nový vzorec ve formátu := <nameOfTheStepToReference>, například =Production.WorkOrder.

  3. Zadejte nový vzorec ve formátu:=Class.Function(ReferenceStep[,otherparameters]) Předpokládejme například, že máte tabulku se sloupcem Gender 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ě Nastavení dotazů v části Použitý postup klikněte pravým tlačítkem na krok a pak vyberte Přesunout nahoru nebo Přesunout dolů.

Odstranit krok

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

V tomto příkladu převedeme text ve sloupci na velká písmena pomocí kombinace vzorců v Rozšířený editor. 

Máte například excelovou tabulku s názvem Objednávky se sloupcem ProductName, který chcete převést na velká písmena. 

Před:

Před

Po:

Krok 4 – výsledek

Při vytváření rozšířeného dotazu vytvoříte řadu kroků vzorce dotazu na základě výrazu let. Výraz let slouží k přiřazení názvů a výpočtu hodnot, na které pak odkazuje klauzule in , která definuje krok. Tento příklad vrátí stejný výsledek jako 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 a odkazuje na krok po názvu. Připomínáme, že jazyk vzorců Power Query rozlišuje velká a malá písmena.

Fáze 1: Otevřete Rozšířený editor

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

  2. V Editor Power Query vyberte Domů > Rozšířený editor, která se otevře se šablonou výrazu let.

Rozšířený editor2

Fáze 2: Definování zdroje dat

  1. Pomocí funkce Excel.CurrentWorkbook vytvořte výraz let následujícím způsobem:let    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]in      Source#x4

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

Výsledek

Krok 1 – výsledek

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

  1. Dotaz otevřete tak, že na listu vyberete buňku v datech a pak vyberete Dotaz > Upravit. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excelu (Power Query).

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

  3. Do výrazu let přidejte #"First Row as Header" a funkci Table.PromoteHeaders následujícím způsobem:let      Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],    #x4#"First Row as Header" = Table.PromoteHeaders(Source)#x3

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

Výsledek

Krok 3 – výsledek

Fáze 4: Změna každé hodnoty ve sloupci na velikost písmen

  1. Dotaz otevřete tak, že na listu vyberete buňku v datech a pak vyberete Dotaz > Upravit. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excelu.

  2. V Editor Power Query vyberte Domů > Rozšířený editor, která se otevře s příkazem, který jste vytvořili ve fázi 3: Zvýšení úrovně 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 First Row as Header, přidáním souboru #"Capitalized Each Word" do zdroje dat a následným přiřazením #"Capitalized Each Word" k výsledku 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"

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

Výsledek

Krok 4 – výsledek

Můžete řídit chování řádku vzorců ve Editor Power Query pro všechny sešity.

Zobrazení nebo skrytí řádku vzorců

  1. Vyberte Možnosti a nastavení> souboru > Možnosti dotazu.

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

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

Zapnutí nebo vypnutí M IntelliSense

  1. Vyberte Možnosti a nastavení> soubor > Možnosti dotazu .

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

  3. V pravém podokně v části Vzorec vyberte nebo zrušte zaškrtnutí políčka Povolit 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 Editor 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ý postup (docs.com)

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

vzorce Power Query M (docs.com)

Zpracování chyb (docs.com)

Potřebujete další pomoc?

Chcete další možnosti?

Prozkoumejte výhody předplatného, projděte si školicí kurzy, zjistěte, jak zabezpečit své zařízení a mnohem více.

Komunity vám pomohou klást otázky a odpovídat na ně, poskytovat zpětnou vazbu a vyslechnout odborníky s bohatými znalostmi.