Jak používat Power Query v Excelu: Kompletní průvodce

Power Query je výkonný nástroj business intelligence v Excelu, který umožňuje importovat, čistit a transformovat data podle vašich potřeb. Pokud jste někdy strávili hodiny čištěním dat (my máme) nebo jste se potýkali se slučováním dat z různých zdrojů (my také máme), tato funkce vám usnadní život.

Nejlepší způsob, jak se naučit používat Power Query v Excelu, je provést některé běžné úkoly, jako je import dat a použití editoru Power Query k transformaci výsledných tabulek a zkombinování dat.

Tento článek vám pomůže začít tím, že ukáže jasné . Zvládnutím Power Query můžete svou práci pozvednout na další úroveň.

Jsi připraven? Pojďme to udělat.

Obsah

• Jak zjistit, zda máte přístup k Power Query
• Jak importovat data pomocí Power Query
• Jak používat Editor Power Query
  • Náhled dat
  • Vlastnosti
• Jak pracovat s více zdroji dat
• Jak připojit data pomocí Power Query
• Jak sloučit data pomocí Power Query
• 5 způsobů, jak transformovat data pomocí Power Query
  • 1. Rozšíření a zhuštění dat
  • 2. Třídění dat
  • 3. Formátování dat
  • 4. Přidávání a odebírání sloupců
  • 5. Změna datových typů
• Práce se vzorci a jazykem M
  • Formule bar
  • Psaní a úprava M kódu
• Co je syntaxe jazyka M?
• Jak obnovit datová připojení
• Jak integrovat Power Query s datovým modelem
• Jak automatizovat pomocí Power Query a VBA
• Pokročilé funkce Power Query
• Spolupráce a sdílení
• Pojďme to zabalit

Jak zjistit, zda máte přístup k Power Query

Než začnete používat Power Query, musíte se ujistit, že máte verzi Excelu, která tuto funkci obsahuje.

Zde je stručný přehled Power Query podle verzí:

• Excel 2010 a Excel 2013: k dispozici jako doplněk, který je třeba stáhnout a nainstalovat.

• Excel 2016: integrován do Excelu jako „Get & Transform“.

• Excel 2019: vylepšený výkon a funkčnost.

• Excel 2021 a Excel pro Microsoft 365: integrace s cloudovými zdroji dat.

• Excel pro Mac: k dispozici pouze od Microsoft 365 verze 16.69 a novější.

Chcete-li ověřit, zda je nástroj integrován ve vašem sešitu aplikace Excel (2016 nebo novější), přejděte na kartu Data a vyhledejte část „Získat a transformovat data“ na tomto obrázku:

Pokud máte Excel 2010 nebo 2013 v systému Windows, můžete si stáhnout , nainstalovat a povolit doplněk Power Query.

Jak importovat data pomocí Power Query

Nástroj Power Query umožňuje importovat širokou škálu zdrojů dat a připojit se k nim:

• Soubory, včetně formátů CSV, XML a JSON

• Webové adresy URL nebo koncové body API

• Databáze, včetně Access, SQL Server, Oracle a nejmodernější databáze

• Služby Azure, soubory SharePointu a kanály OData

Nejjednodušší zdroj dat pro začátek je jednoduchý soubor CSV s několika řádky dat. Vytvořte textový soubor a přidejte tato čtyři jména a příjmení oddělená čárkou:

• Joe, Bloggs

• Anne, Ryane

• Jamesi, Stewarte

• Mary, Brown

Nezapomeňte uložit soubor s příponou .CSV. Váš soubor bude vypadat takto:

Chcete-li načíst data pomocí Power Query, postupujte takto:

1. Přejděte na kartu Data.

2. Klikněte na tlačítko „Získat soubory“ na kartě Získat a transformovat data.

3. Vyberte vzorový soubor.

Možná budete muset počkat několik sekund, než nástroj natáhne data do podokna náhledu. Power Query využívá poměrně hodně strojových prostředků i na malý import, takže buďte trpěliví.

Tip: Pro zrychlení výkonu ukončete ostatní aplikace.

Power Query zobrazí okno „Import textu/CSV“ s náhledem vašich dat.

Pokud data vypadají správně, můžete pokračovat v načítání nebo transformaci dat:

• tlačítko Načíst načte data přímo do Excelu.

• tlačítko Transformovat data umožňuje upravit data před jejich načtením.

Všimli jste si, že ukázkový soubor neměl záhlaví? Pokud je načten přímo do Excelu, budou záhlaví „Sloupec 1“ a „Sloupec 2“.

Místo toho můžete data stáhnout do editoru Power Query a provést nějaké změny. Chcete-li tak učinit, klikněte na tlačítko „Transformovat data“.

Nyní se otevře Editor Power Query, který nás přivede k další části.

Jak používat Editor Power Query

V této části prozkoumáme některé základní aspekty práce s editorem Power Query.

Náhled dat

V oblasti Náhled dat můžete prohlížet importovaná data a pracovat s nimi. Můžete filtrovat řádky, upravovat datové typy sloupců a přetvářet data tak, aby vyhovovala vašim potřebám.

Až budete hotovi, Excel načte transformovaná data do listu.

Pomocí ukázkového souboru můžete upravit názvy sloupců přímo v tomto podokně.

Udělat to tak:

1. Klikněte pravým tlačítkem na záhlaví sloupce.

2. V rozbalovací nabídce klikněte na „Přejmenovat“.

3. Zadejte preferované názvy do záhlaví.

Při provádění změn se sekce „Použité kroky“ na pravé straně podokna náhledu aktualizuje, aby odrážela každou transformaci, což vám umožňuje sledovat a upravovat kroky zpracování dat podle potřeby.

Vlastnosti

Nastavení vlastností pro vaše dotazy je nezbytné pro organizaci a sledování vaší práce.

Podokno Nastavení dotazu se nachází napravo od okna Power Query.

1. Klikněte na odkaz „Všechny vlastnosti“.

2. Upravte název dotazu tak, aby byl popisný.

3. Chcete-li poskytnout další informace, přidejte popis.

Je snadné zapomenout, k čemu byly nastaveny dotazy z minulého týdne. V tomto případě byl název dotazu změněn a byl přidán popis, který udává účel dotazu.

Pokud se chcete dozvědět více o dostupných možnostech, podívejte se na toto .

Jak pracovat s více zdroji dat

Editor Power Query nabízí dvě primární metody pro kombinování dat z více zdrojů: připojení a sloučení.

Připojování spojuje tabulky svisle a skládá je na sebe. Slučování umožňuje spojovat tabulky na základě společných sloupců.

Chcete-li vidět, jak to funguje, vytvořte druhý malý soubor CSV se stejnou strukturou jako první. Tentokrát vložte do souboru dvě jména (jméno a příjmení):

• Joe, Bloggs

• Laura, Cane

Všimněte si, že jeden z těchto názvů se opakuje z předchozího souboru.

Nyní postupujte podle těchto kroků a natáhněte data do sešitu aplikace Microsoft Excel.

1. Zavřete okno Editoru Power Query a uložte změny.

2. Přidejte nový list.

3. Chcete-li importovat druhý soubor, postupujte podle kroků v předchozí části.

4. Klikněte na tlačítko Transform.

5. Přejmenujte záhlaví sloupců na „Jméno“ a „Příjmení“.

V pravém podokně listu aplikace Excel jsou zobrazeny aktuální dotazy a připojení. Nyní byste měli v podokně vidět dva dotazy.

Jak připojit data pomocí Power Query

V této části připojíte druhý ukázkový soubor CSV k prvnímu.

Chcete-li přidat data, postupujte takto:

1. Klepněte pravým tlačítkem myši na první tabulku (Jména zákazníků) v podokně Dotazy a připojení.

2. Zvolte „Upravit“ a otevřete tabulku v editoru Power Query.

3. Klikněte na Přidat dotazy v části Kombinovat.

4. Vyberte druhou tabulku z rozevíracího seznamu tabulek, které chcete připojit.

Výsledkem budou dva řádky v první tabulce navíc.

Všimněte si, že se jeden řádek opakuje – takto funguje operace připojení. Přidává data z jedné tabulky do druhé bez ohledu na to, zda data již existují.

Duplicitní data můžete odstranit v editoru Power Query pomocí těchto kroků:

1. Klikněte na záhlaví sloupce „Jméno“.

2. Podržte klávesu „Ctrl“ (nebo klávesu „Cmd“ na Macu).

3. Klikněte na záhlaví obou sloupců.

4. Klikněte na „Odebrat řádky“ na kartě Domů na pásu karet Editoru Power Query.

5. Z rozbalovací nabídky vyberte „Odstranit duplikáty“.

Jak sloučit data pomocí Power Query

Při slučování dat musíte vybrat odpovídající sloupce mezi dotazy.

Předpokládejme, že máte dva soubory oddělené čárkami, kde jeden obsahuje telefonní čísla zákazníků a druhý adresy. Tyto dva soubory vypadají takto:

Podle pokynů v předchozích částech načtěte každý soubor do vlastního dotazu napájení. Poté si můžete procvičit slučování tabulek.

V případě našich ukázkových dat by měly být jako odpovídající sloupce vybrány oba sloupce (Jméno a Příjmení). Chcete-li vybrat více sloupců, podržte klávesu Ctrl .

Následuj tyto kroky:

1. Klepněte pravým tlačítkem myši na první tabulku v podokně Dotazy a připojení.

2. Zvolte „Upravit“ a otevřete tabulku v editoru Power Query.

3. Vyberte „Sloučit dotazy“ ze skupiny „Kombinovat“.

4. Vyberte druhou tabulku v rozevíracím seznamu tabulek.

5. Klikněte na první sloupec v první tabulce.

6. Podržte klávesu Ctrl a klikněte na druhý sloupec v první tabulce.

7. Podržte klávesu Ctrl a klikněte na oba sloupce ve druhé tabulce.

8. Ponechte výchozí operátor spojení jako „Left Outer“.

9. Klikněte na „OK“ pro použití změn.

Operátor spojení „Left Outer“ zajišťuje eliminaci duplicitních dat.

V editoru Power Query uvidíte nový sloupec s názvem „Tabulka“ nebo „Vnořená tabulka“ obsahující sloučená data z druhého dotazu.

1. Klikněte na ikonu rozbalení (dvě šipky) v záhlaví tohoto sloupce.

2. Zrušte zaškrtnutí políčka „použít původní sloupec jako předponu“ (vyhnete se tak dlouhým názvům sloupců).

3. Klikněte na „OK“.

4. Klikněte na „Zavřít a načíst“.

Sloučená tabulka měla čtyři sloupce se sloučenými údaji o telefonu a adrese. Možná budete mít ještě co uklízet.

Můžete například upravit název tabulky (Merge1) a sloupec adresy na obrázku níže.

5 způsobů, jak transformovat data pomocí Power Query

Transformace dat v souboru Excelu je s Power Query snadná a umožňuje vám efektivně čistit, organizovat a manipulovat s daty.

Některé z nejběžnějších způsobů transformace dat jsou:

• Rozšiřování a zhušťování dat

• Třídění dat

• Formátování dat

• Přidávání a odebírání sloupců

• Změna datových typů

1. Rozšíření a zhuštění dat

Rozšiřování a zhušťování dat je důležité pro lepší správu vašich datových sad. Power Query umožňuje rozbalit nebo sbalit tabulky pro přehlednější zobrazení.

Předchozí část ukazuje, jak rozšířit zahrnutím dat ze souvisejících tabulek.

Můžete jej také zkrátit tak, aby zahrnoval pouze souhrnná data na základě agregace dle vašeho výběru, jako je součet, počet nebo procento.

2. Třídění dat

Chcete-li data seřadit, můžete jejich pořadí rychle změnit vzestupně nebo sestupně.

Klikněte na šipku dolů vedle názvu sloupce a vyberte „Seřadit vzestupně“ nebo „Seřadit sestupně“.

3. Formátování dat

Karta Transformace v editoru Power Query poskytuje spoustu možností.

Například rozbalovací nabídka Formát umožňuje změnit všechna data ve sloupci nebo celé tabulce na velká nebo malá písmena.

4. Přidávání a odebírání sloupců

Power Query zjednodušuje proces přidávání, odstraňování a úpravy sloupců.

Chcete-li sloupec odstranit, jednoduše klikněte pravým tlačítkem myši na záhlaví nežádoucího sloupce a vyberte „Odstranit“. Tím trvale odstraníte sloupec a všechna jeho data z tabulky.

Chcete-li přidat nový sloupec, vyberte stávající sloupec, ve kterém se má nový sloupec zobrazovat. Poté použijte funkci „Přidat sloupec z příkladů“ k vytvoření vlastního sloupce nebo k provádění výpočtů mezi dvěma nebo více sloupci.

5. Změna datových typů

Práce s konzistentními a přesnými typy dat je zásadní pro přesnou analýzu. Power Query umožňuje snadno měnit datové typy:

1. Klepněte pravým tlačítkem myši na záhlaví sloupce.

2. Vyberte „Změnit typ“.

3. Vyberte požadovaný typ dat z poskytnutého seznamu.

Změna datových typů může být zvláště užitečná při práci s procentuálními daty.

Můžete například potřebovat převést desetinná místa na procenta nebo převést textová procenta na číselná pro snadnější výpočet a srovnání.

Práce se vzorci a jazykem M

Při používání Power Query v Excelu se setkáte s jazykem M, který je srdcem vytváření pokročilých vzorců, manipulace s daty a vytváření výkonných dotazů. V této sekci se dozvíte o:

• Formula Bar

• M kód

• základní prvky syntaxe jazyka M

Formule bar

Řádek vzorců v editoru Power Query v Excelu je oblast, kam zadáte své vzorce a M kód.

Chcete-li jej použít, přejděte do editoru Power Query výběrem dotazu z podokna Dotazy a připojení. Řádek vzorců se nachází nad podoknem náhledu dat.

Psaní a úprava M kódu

Chcete-li vytvářet všestranné a výkonné dotazy v Excelu, budete muset napsat a upravit jazyk Power Query známý jako M kód. Zde je několik tipů, jak začít:

• Pomocí podokna Aplikované kroky můžete napsat kód krok za krokem, což usnadní sledování a úpravu logiky dotazu.

• Zkombinujte M kód s excelovou tabulkou, kontingenčními tabulkami a externími zdroji dat, jako je Azure a textové soubory, a vytvořte dynamické a automatizované sestavy.

• Upravte možnosti dotazu, abyste upřesnili své preference načítání dat a zlepšili výkon.

Když se seznámíte s M kódem, zjistíte, že můžete automatizovat a optimalizovat mnoho procesů, díky čemuž bude vaše analýza dat v Excelu efektivnější a výkonnější.

Chcete-li zobrazit kód za dotazem, postupujte takto:

1. Otevřete dotaz v editoru Power Query.

2. Klikněte na „Pokročilý editor“ na kartě Domů.

Toto je kód naší vzorové sloučené tabulky (vaše může vypadat jinak):

Co je syntaxe jazyka M?

Pochopení syntaxe jazyka M je zásadní pro vytváření efektivních vzorců Power Query. Mějte na paměti následující body:

• Jazyk M rozlišuje velká a malá písmena, proto buďte opatrní s velkými písmeny.

• Pro odkazování na sloupce, záznamy a seznamy použijte hranaté závorky [].

• Chcete-li odkazovat na předchozí krok v dotazu, použijte název jeho proměnné (např. #”Název kroku”).

• Výstup kroku vzorce dotazu s příkazem „in“.

Až získáte zkušenosti s , objevíte nové způsoby, jak transformovat a analyzovat svá data, díky čemuž budou sešity Excelu ještě cennější.

Podívejte se na video níže, je čas vyndat kartáč a vyčistit svá zaneřáděná data.

Jak obnovit datová připojení

Jednou z klíčových funkcí Power Query je možnost snadného obnovení připojení Power Query.

Když byly vaše zdroje dat aktualizovány a potřebujete nejnovější informace v sešitu, můžete aktualizovat datová připojení v podokně Dotazy a připojení.

Pokud nevidíte podokno na pravé straně listu, můžete jej otevřít tak, že přejdete do tabulky Data a kliknete na „Dotazy a připojení“.

Když je otevřené podokno Dotazy a připojení, obsahuje seznam všech dotazů. Následuj tyto kroky:

1. Klepněte pravým tlačítkem myši na dotaz, který chcete aktualizovat.

2. Z kontextové nabídky vyberte možnost Obnovit.

Tím se sešit aktualizuje o nejnovější data z připojených zdrojů dat.

Jak integrovat Power Query s datovým modelem

Datový model aplikace Excel je výkonná funkce, která umožňuje vytvářet nástroje business intelligence, jako jsou kontingenční tabulky, na základě datových vztahů.

Datový model je v podstatě skrytá databáze, která obsahuje importovaná data a vztahy mezi tabulkami.

Je součástí funkce Power Pivot, která je integrována v aktuálních verzích Excelu. Možná budete muset nainstalovat Power Pivot jako doplněk, abyste k němu měli přístup v předchozích verzích).

Datová připojení Power Query můžete integrovat s datovým modelem pomocí těchto kroků:

1. Načtěte data pomocí Power Query, jak je popsáno v předchozích částech.

2. Namísto načítání přímo do sešitu zvolte Načíst do z nabídky Načíst.

3. Zaškrtněte políčko Přidat tato data do datového modelu v dialogovém okně Importovat data.

Jak automatizovat pomocí Power Query a VBA

S integrací VBA (Visual Basic for Applications) můžete vytvářet vlastní datová připojení a automatizovat kroky transformace dat v Power Query.

Své stávající dovednosti VBA můžete využít k vytváření pokročilých datových modelů začleněním dat z platforem jako Microsoft Access, SharePoint a Analysis Services.

Ačkoli Power Query nemá nativní podporu pro VBA, můžete použít kód VBA k nepřímému otevírání, obnovování a manipulaci s dotazy Power Query.

To vám umožní vytvářet výkonné, automatizované datové toky a zefektivnit proces analýzy dat.

Zde je ukázkový kód, který aktualizuje dotaz napájení v aktuálním sešitu.

:

Dim wb As Workbook, pq As Object

Nastavit wb = ThisWorkbook

Nastavit pq = wb.Queries(“Jména zákazníků”)

 pq.Obnovit

Zde je kód v makru Excel VBA:

Pokročilé funkce Power Query

Excel Power Query nabízí množství pokročilých funkcí pro zjednodušení a vylepšení vašeho procesu analýzy dat. Naučíte-li se efektivně používat tyto funkce, můžete své dovednosti v oblasti analýzy dat posunout na zcela novou úroveň.

Mezi vestavěné nástroje pro analýzu dat patří:

• Podmíněné sloupce pro použití složité logiky na transformace dat.

• Funkce seskupování a agregace pro smysluplné shrnutí vašich dat.

• Operace Pivot a Unpivot pro změnu tvaru dat pro snazší analýzu.

Mnoho z těchto funkcí najdete na kartě Transformace v editoru Power Query.

Navíc můžete Power Query připojit k Business Intelligence pro pokročilejší možnosti analýzy dat a vizualizace .

Spolupráce a sdílení

Sdílení datových připojení, dotazů a transformací se členy vašeho týmu je zásadní pro efektivní spolupráci.

Sešit můžete sdílet s dotazy tak, že jej uložíte na síťovou jednotku, SharePoint nebo jiné sdílené úložiště.

Můžete také exportovat M kód za jedním dotazem pomocí těchto kroků:

1. Upravte dotaz v editoru Power Query.

2. Klikněte na „Pokročilý editor“ na kartě Domů.

3. Zkopírujte celý M kód v okně Advanced Editor.

4. Otevřete textový editor (např. Poznámkový blok) a vložte zkopírovaný M kód do textového editoru.

5. Uložte textový soubor a odešlete jej kolegovi.

Pojďme to zabalit

Díky řadě funkcí a uživatelsky přívětivému rozhraní je Power Query nezbytným nástrojem pro uživatele Excelu, kteří chtějí efektivně pracovat s externími daty.

Tato cenná funkce pomáhá zefektivnit proces vytváření sestav, čištění dat a provádění komplexních analýz vašich datových tabulek. Použitím

Power Query v konečném důsledku šetří váš čas a zvyšuje efektivitu při práci se soubory Excelu.