Power Query: 2021 Konečné Začátečníky Průvodce

Ahoj, vítejte na začátečníky průvodce k Moci Dotaz UpSlide.
Přemýšleli jste někdy, co je to Napájecí dotaz? Jak to funguje? Pokud ano, jste na správném místě!
Pauline, náš odborník na dotazy z oblasti financí 3.1, nám poskytla všechny své nejlepší tipy a rady, jak napsat tento článek.
zde se naučíte základy softwaru a dokonce si trochu procvičíte! Jste připraveni?

pokud nemáte čas si ji přečíst, stáhněte si verzi PDF a užijte si ji později!

co je to Power Query? Kdy ji mám použít?

Power Query je doplněk aplikace Excel, který umožňuje uživatelům importovat, čistit a konsolidovat data. Můžete použít Power Query, když:

• Zpracování velkého množství dat (tam, kde Excel je omezena na jeden milion řádků)
• Import dat z různých zdrojů (např. Excel, CSV, SharePoint, SQL, Salesforce)
• Manipulace, čištění a spojující několik tabulek, souborů nebo dokonce složek!

Power Query je uživatelsky přívětivý a výkonný nástroj i pro začátečníky, nemusíte kódování zkušenosti, aby mohli začít. Navíc nemění původní zdroj dat. Výsledek je jako snímek přetvořených dat přinesených do Excelu. Co je na tom tak skvělého? Protože zdrojová data nejsou přímo importována ,neztěžuje váš soubor Excel (tj. nezpomaluje váš počítač!).

abychom to shrnuli, Power Query je nástroj pro analýzu dat, který usnadňuje a zefektivňuje procesy ETL (Extract-Transform-Load).

jaký je rozdíl mezi Power Query a Power Pivot?

Power Pivot je často spojován s Power Query, protože se jedná o doplněk Microsoft Office, který usnadňuje analýzu dat. Je zajímavé, že tyto dva nástroje mohou pracovat společně! Power Pivot je prostě dalším krokem transformace dat: umožňuje uživatelům vytvářet datové modely z tabulek dříve vyčištěných pomocí Power Query.

zatímco podobné, Power Pivot je více-tak se používá pro analýzu dat, výpočty související s časem a generování odkazů. Jednoduše řečeno:

• Power Query umožňuje, aby se automaticky konsolidovat a zpracovat soubory (tj. standardizace procesů podávání zpráv nebo sloučení několika souborů se stejným formátem).
• Power Pivot umožňuje analyzovat databáze několika milionů řádků extrakcí souhrnné informace podle pravidel stanovených vaše podnikání.

jaký je rozdíl mezi Power Query a Power BI?

základní technologie dotazu Power Query je také zahrnuta v Power BI.

rozhraní desktopového softwaru Power BI vám umožňuje těžit ze tří technologií (Power Query, Power Pivot a Power View). S Power BI, můžete dělat všechny kroky analýzy dat řetězce od získávání dat k vytvoření vizualizací na interaktivní dashboard, který můžete sdílet a analyzovat.

> Přečtěte si náš článek o tom, jak exportovat Power BI pro Excel

Proč je Power Query skvělá alternativa k Excel VBA?

Power Query můžete snadno nahradit VBA (Visual Basic for Applications), jak to umožňuje:

• Proces tabulek jednoduše kliknutím na tlačítka. Žádné kódování dovednosti potřebné!
• vizualizujte své operace krok za krokem bez spuštění jediného makra.
• Snadno upravte pořadí kroků různých kroků přetažením! Také můžete přidat a odstranit nepotřebné kroky jednoduchým kliknutím.

(viz část 2-Section3: jak začít s Power Query?

nicméně Power Query nemůže nahradit VBA, pokud jde o interakci s rozhraním aplikace Excel, automatizaci procesů, generování nových souborů, odesílání e-mailů atd.

je Microsoft Power Query zdarma? Existuje napájecí dotaz pro Mac?

Ano. Power Query je bezplatné rozšíření aplikace Excel. To je další hlavní výhoda doplňku.
(Přečtěte si další otázku a zjistěte, jak ji můžete získat na ploše aplikace Excel)

Power Query pro Mac? Ano, velké zprávy: Editor dotazů je nyní kompatibilní s aplikací Excel pro Mac! Integrace je však v současné době poměrně omezená, protože můžete změnit pouze cestu k souboru a obnovit stávající dotazy.

Část 2: Začínáme s Power Query!

jak stáhnout Power Query?

Power Query je k dispozici pro Excel od roku 2010.

zde je návod, jak přidat výkonový dotaz podle verze aplikace Excel*:

• dobrá zpráva, Nemusíte stahovat Power Query pro Excel 2019, 2016 nebo v Office 365. Najdete ji na pásu karet „Data“ pod tlačítkem „Get & Transform“.
• pro Excel 2013 a 2010 musíte stáhnout doplněk Power Query. Objeví se na nové vyhrazené kartě s názvem „Power Query“ (v aplikaci Excel 2010 musíte mít profesionální Plus s licencí Software Assurance).

*zde je návod, jak zkontrolovat verzi aplikace Excel:

přejděte do souboru > účet > o aplikaci Excel. Poté se objeví okno. Přečtěte si první dvě čísla v závorce. V níže uvedeném příkladu je verze aplikace Excel 2016.

dozvědět Se více, zde je další podrobný článek o tom, jak nainstalovat a aktivovat Power Query v Excelu.

Jak mohu aktivovat Power Query v aplikaci Excel?

v závislosti na verzi aplikace Excel (viz výše otázka: Kde je Power Query v aplikaci Excel?) stačí kliknout na tlačítko s názvem „získat data“ v části „Získat & transformovat“ na kartě „Data“ pro Excel 2016 a dále. Pro verze aplikace Excel před 2013 zahrnuty, jednoduše přejděte na pásu karet Power Query.

Vezměte prosím na vědomí, že když vstoupíte do rozhraní Power Query, již nebudete moci komunikovat s aplikací Excel. Pokud se potřebujete vrátit do Excelu,budete muset zavřít Editor Power Query.

jak funguje Power Query?

v aplikaci Excel máte přístup k následujícím funkcím pouze po kliknutí na „dotazy & připojení“:

data ribbon: hledání zdrojů dat (páska „Power Query“ pro Excel 2010 a 2013).

dotazy & podokno připojení: Chcete-li se rychle podívat na všechna provedená připojení (dotazy).

Jakmile jste zahájili Power Query, se dostanete na Power Query Editor, který se skládá ze 4 odlišných prvků:

Hlavní stuha: umožňuje vytvářet dotazy od nuly díky své tlačítek (pokud jste obeznámeni s M jazyce, můžete také přístup k Pokročilé Editor vyladit ručně).

dotazy: Chcete-li sledovat všechna připojení ke zdrojům dat a proudům.

náhled tabulky dat: Chcete-li vizualizovat výsledek dat po konkrétním kroku.

nastavení dotazu (použité kroky): procházet všemi kroky, které jste provedli. Power Query automaticky zaznamenává všechny provedené transformace.

• V oddíle 4, dozvíte se, jak Power Query funguje v podstatě tím, že transformace dat tabulky.
• v sekci 5 je to ještě zajímavější, protože zjistíte, jak postupovat s daty z různých zdrojů a sloučit dotazy.

jak začít s Power Query?

Představte si následující situaci:

jste finanční kontrolor technologické firmy prodávající počítače a chytré telefony. V současné době působíte ve 3 zemích: Francii, Velké Británii a Číně. Každý měsíc sestavujete konsolidovanou zprávu pro svého manažera s KPI, jako jsou celkové příjmy, příjmy podle země nebo příjmy podle produktu. Udělat to tak, každý manažer země-Dennis, Douglas, A Karen-vám pošle soubor.

problém: každý soubor uvádí prodej ve vlastním označení země. Je příliš časově náročné převést je na eura a poté je konsolidovat do závěrečné zprávy.

Co kdybyste mohli automatizovat proces a vypočítat vaše zprávy během několika minut pouze pomocí Power Query?

nebylo by to úžasné? No, S Power Query, můžete! Začněme přípravou první tabulky 🙂

nejprve potřebujete data. Stáhněte si náš příklad Power Query a uložte jej na místní server.

poté postupujte podle těchto jednoduchých kroků:

• Rozbalte soubor a uložte jej na místní server.
• otevřete nový sešit v aplikaci Excel a přejděte na kartu Data > Data > Ze souboru > Ze Sešitu, jak je uvedeno níže.

(pokud máte verzi aplikace Excel 2013 nebo 2010, stačí jít do kartě Power Query)

• Jít na cestu, kam jste uložili rozbalené soubory, které jste právě stáhli a vyberte soubor aplikace Excel s názvem „Směnné Kurzy“.

• klikněte na transformovat Data.

možná jste si také všimli, že některé použité kroky se již objevily v podokně Nastavení dotazu. Ty se provádějí automaticky.

nyní si trochu procvičíme, počínaje čištěním datové tabulky:

• odstraňte horní 3 řádky, přejděte na Odstranit řádky > odstraňte horní řádky a zadejte „3“. Okamžitě uvidíte transformaci v náhledu datové tabulky i v použitých krocích (nastavení dotazu).

• Zbavte se prázdných řádků: odstraňte řádky > odstraňte prázdné řádky (jak je uvedeno níže).

• Podporovat vaše první řádek jako záhlaví: klikněte na tlačítko „Použít První Řádek jako Záhlaví“. Jak jste si možná všimli, Power Query automaticky přidal krok ke změně typu některých dat (hodnoty v prvním sloupci jsou nyní formátovány jako data).

• Odstranit prázdné sloupce: klikněte na tlačítko „Vybrat sloupce“ a zrušte zaškrtnutí políčka sloupce 7, 8, 9 a 10.

vedeš si dobře! Další tři kroky a budete připraveni!

• rozbalte sloupce: Vyberte „Směnný Kurz“ sloupec, pak v Transformaci záložka, přejděte na Unpivot sloupce > Unpivot Další sloupce (můžete si také vybrat dalších pět sloupců a vyberte možnost „Unpivot sloupce“).

• přejmenujte sloupce: klikněte na buňky záhlaví, do první zadejte „datum“, do druhé“ měnu „a do třetí“ sazbu“.

• použít filtr: stejně jako v aplikaci Excel klikněte na rozevírací nabídku Sloupce „datum“ a vyberte „Dec-18“.

• na kartě Domů klikněte na Zavřít a načíst a načtěte finální tabulku do nového listu.

výborně! Vytvořili jste svou první datovou tabulku.

Pojďme dál s Power Query

(Re)představte si následující situaci:

Díky Power Query dovednosti, nyní jste SENIOR finanční controller technologie firmy. Nyní musíte konsolidovat údaje z prosince 2018, které vám správci zemí zaslali.

problém: každý soubor uvádí prodej ve vlastním označení země. Je časově náročné je převést na eura, ještě více je pak konsolidovat do závěrečné zprávy.

co kdybyste mohli proces automatizovat a vypočítat zprávu během několika minut pouze pomocí Power Query?

Teď uvidíme, jak se konsolidovat různé zdroje dat a sloučit je s tabulkou jsme připravili v předchozí části.

za Prvé, pojďme importovat celou složku včetně tří různých zdrojových souborů:

• Pokud jste stále v Power Query přejít na Nový zdroj > Soubor > Složka.

(Pokud se zpět na Excel, přejděte na Získat Data > Ze Složky)

• Vyberte složku „Sales_201812“, který jste dříve stáhli a uložili. Poté klikněte na OK.

mělo by se objevit nové okno.

poté je třeba sloučit dotazy:

• v rozevírací nabídce (v dolní části nového okna) vyberte možnost „kombinovat & upravit“.

• Vyberte příklad tabulky „Dec-18“ (prosím, všimněte si, že všechny listy musí být pojmenovány stejným způsobem).

• můžete zkontrolovat v prvním sloupci, že všechny zdroje jsou zahrnuty (klikněte na rozbalovací menu), a potom klepněte na tlačítko OK.

nyní jste v editoru Power Query.

• přejděte na kartu transformace > nahradit hodnoty.

• nyní nahraďte “ € “ za „EUR“.

• Přejděte na kartu Domů > Sloučit Dotazy.

objeví se nové okno:

1. vyberte sloupec „měna“.
2. v rozbalovací nabídce vyberte soubor „směnné kurzy“.
3. vyberte sloupec“ měna“.
4. klikněte na OK.

• Nyní, musíte-li směnný kurz zobrazit data, která jste právě citováno:
  1. Vyberte „Směnný Kurz“ sloupci.
  2. klikněte na tlačítko Rozbalit.
  3. zkontrolujte pouze „rychlost“.
  4. zrušit zaškrtnutí “ použít originál…“.
  5. klikněte na OK.

gratuluji! Právě jste načetli jiný zdroj dat, sloučili dotazy a získali odpovídající směnný kurz. Žádné další VLOOKUP nebo INDEX + zápas!

pro dokončení vytvoříme sloupec „Jednotková cena EUR“, který převede všechny ceny na eura:

• přejděte na kartu Přidat sloupec > vlastní sloupec.

• objeví se nové okno. Napište vzorec ceny v eurech:
  1. pojmenujte ji „Jednotková cena EUR“.
  2. dvakrát klikněte na „Jednotková cena místní měna“ (nebo ji vyberte a poté klikněte na Vložit).
  3. Typ „/“.
  4. Poklepejte na „rychlost“.
  5. klikněte na OK.

• jakmile se sloupec objeví, klikněte na jeho symbol typu (těsně před názvem) a vyberte „desetinné číslo“.

Musíme udělat to samé pro generování „Příjmy“ sloupec:

• Přejít na Přidat Sloupce na kartě > Vlastní Sloupce.
• objeví se nové okno. Zadejte vzorec příjmů:
  1. pojmenujte jej „příjmy“.
  2. dvakrát klikněte na „Jednotková cena EUR“ (nebo ji vyberte a poté klikněte na Vložit).
  3. Typ „*“.
  4. dvakrát klikněte na „množství“.
  5. klikněte na OK.

• Znovu, změnit typ sloupce „Desetinné Číslo“.

Chcete-li dokončit, exportujte ji do nové datové tabulky v aplikaci Excel!

• přejít na kartu Domů > Zavřít & načíst > Zavřít & načíst.

Nyní můžete plně využívat funkce aplikace Excel, analyzovat data, aby kontingenční tabulky, atd.

Páni! Pokud jste to zvládli až do konce, Gratulujeme! Nyní víte, jak funguje Power Query.

nyní je řada na vás!

chci vám to předat: rozuměli jste všem výše uvedeným otázkám ohledně Power Query?
stáhli jste si naše výukové soubory a postupujte podle výše uvedených pokynů a vyzkoušejte je sami?
dejte nám vědět tím, že zanecháte rychlý komentář níže právě teď!

Chcete-li se dozvědět více o napájení dotazu: