Jak používat Power Query v aplikaci Excel?
Excel Power Query se používá k prohledávání zdrojů dat, vytváření spojení se zdroji dat a následnému tvarování dat podle našeho požadavku na analýzu. Jakmile jsme provedli tvarování dat podle našich potřeb, můžeme také sdílet naše nálezy a vytvářet různé sestavy pomocí více dotazů.
Kroky
V zásadě existují 4 kroky a pořadí těchto 4 kroků v Power Query je následující:
- Připojení: Nejprve se připojíme k datům, která mohou být někde, v cloudu, ve službě nebo místně.
- Transformace: Druhým krokem by byla změna tvaru dat podle požadavků uživatele.
- Kombinovat: V tomto kroku provedeme některé transformační a agregační kroky a spojíme data z obou zdrojů, abychom vytvořili kombinovanou sestavu.
- Spravovat: Toto sloučí a připojí sloupce v dotazu s odpovídajícími sloupci v jiných dotazech v sešitu.
Excel Power Query má mnoho mimořádně výkonných funkcí.
Předpokládejme, že máme údaje o nákupu za posledních 15 let ve 180 souborech. Vedení organizace by nyní vyžadovalo konsolidaci čísel před jejich analýzou. Správa může trvat kteroukoli z následujících metod:
- Otevřeli by všechny soubory a zkopírovali je do jednoho souboru.
- Na druhou stranu mohou použít moudré řešení, kterým je použití vzorců, protože je náchylné k chybám.
Ať už si zvolí jakoukoli metodu, obsahuje spoustu manuální práce a po několika měsících se objeví nová prodejní data pro přidanou dobu. Budou muset znovu provést stejné cvičení.
Power Query jim však může pomoci tuto nudnou a opakující se práci neudělat. Pojďme pochopit tento dotaz na výkon Excel s příkladem.
Příklad
Předpokládejme, že máme textové soubory ve složce s údaji o prodeji a chceme tato data získat v našem souboru aplikace Excel.
Jak můžeme vidět na následujícím obrázku, že ve složce máme dva typy souborů, ale chceme získat data pouze textových souborů v souboru aplikace Excel.
Stejné kroky by byly:
Krok 1: Nejprve musíme získat data v Power Query, abychom mohli provést požadované změny v datech a importovat je do souboru aplikace Excel.
Po kliknutí na příkaz „Získat data“ ze skupiny „Získat a transformovat“ na kartě „Data“ zvolíme možnost „Ze složky“ v nabídce „Ze souboru“ .
Krok 2: Vyberte umístění složky procházením.
Klikněte na 'OK'
Krok 3: Otevře se dialogové okno obsahující seznam všech souborů ve vybrané složce se záhlavími sloupců jako „Obsah“, „Název“, „Přípona“, „Datum přístupu,“ „Datum změny“, „Datum vytvoření“. „Atributy“ a „Cesta ke složce“.
K dispozici jsou 3 možnosti, tj. Kombinovat , Načíst a Transformovat data .
- Kombinovat : Tato možnost slouží k přechodu na obrazovku, kde si můžeme vybrat, která data se mají kombinovat. Krok úpravy je u této možnosti přeskočen a nedává nám žádnou kontrolu nad tím, které soubory kombinovat. Funkce Combine spojí každý soubor ve složce, což může vést k chybám.
- Načíst: Tato možnost načte tabulku zobrazenou výše na obrázku do listu aplikace Excel namísto skutečných dat v souborech.
- Transformovat data: Na rozdíl od příkazu „Zkombinovat“ , použijeme-li tento příkaz, můžeme si vybrat, které soubory kombinovat, tj. Můžeme kombinovat pouze jeden typ souboru (stejnou příponu).
Stejně jako v našem případě chceme kombinovat pouze textové soubory (.txt); zvolíme příkaz „Transformovat data“ .
Na pravé straně okna můžeme vidět „Aplikované kroky“. Prozatím se provádí pouze jediný krok, kterým je převzetí podrobností o souborech ze složky.
Krok 4: Existuje sloupec s názvem „Rozšíření“, kde vidíme, že hodnoty ve sloupci jsou zapsány v obou případech, tj. Velká a malá písmena.
Musíme však převést všechny hodnoty na malá písmena, protože filtr rozlišuje mezi oběma. Aby učinily totéž, musíme vybrat sloupec a poté zvolte „písmena“ z „Format“ v nabídce Command.
Krok 5: Data budeme filtrovat pomocí sloupce „Rozšíření“ pro textové soubory.
Krok 6: Musíme nejprve zkombinovat data pro oba textové soubory pomocí prvního sloupce „Obsah“. Klikneme na ikonu umístěnou na pravé straně názvu sloupce.
Krok 7: Otevře se dialogové okno s titulkem „Kombinovat soubory“ , kde je třeba vybrat oddělovač jako „Tab“ pro textové soubory (soubory s příponou „.txt“) a můžeme zvolit základnu pro detekci datových typů. A klikněte na „OK“.
Po kliknutí na „OK“ dostaneme kombinovaná data textových souborů v okně „Power Query“ .
Podle potřeby můžeme změnit datový typ sloupců. Ve sloupci „Výnosy“ změníme datový typ na „Měna“.
Můžeme vidět kroky aplikované na data pomocí energetického dotazu na pravé straně okna.
Po provedení všech požadovaných změn v datech můžeme data načíst do listu aplikace Excel pomocí příkazu „Zavřít a načíst do“ ve skupině „Zavřít“ na kartě „Domů“ .
Musíme si vybrat, zda chceme načíst data jako tabulku nebo připojení. Poté klikněte na „OK“.
Nyní můžeme vidět data jako tabulku v listu.
A podokno Dotazy na sešit na pravé straně, které můžeme použít k úpravám, duplikování, slučování, připojování dotazů a k mnoha dalším účelům.
Excel Power Query je velmi užitečný, protože vidíme, že 601 612 řádků bylo načteno během několika minut.
Věci k zapamatování
- Power Query nemění původní zdrojová data. Namísto změny původních zdrojových dat zaznamenává každý krok, který uživatel provede při připojování nebo transformaci dat, a jakmile uživatel dokončí tvarování dat, vezme vylepšenou datovou sadu a přenese ji do sešitu.
- Power Query rozlišuje velká a malá písmena.
- Při konsolidaci souborů v zadané složce se musíme ujistit, že pomocí sloupce „Přípona“ musíme dočasné soubory (s příponou „.tmp“ a název těchto souborů začínat znakem „~“) vyloučit jako Power Query může tyto soubory také importovat.
