Ako používať Power Query v programe Excel?
Excel Power Query sa používa na vyhľadávanie zdrojov údajov, nadväzovanie spojení so zdrojmi údajov a následné tvarovanie údajov podľa našich požiadaviek na analýzu. Keď skončíme s tvarovaním údajov podľa našich potrieb, môžeme tiež zdieľať naše zistenia a vytvárať rôzne správy pomocou viacerých dotazov.
Kroky
V zásade existujú 4 kroky a poradie týchto 4 krokov v Power Query je nasledovné:
- Pripojiť: Najprv sa pripojíme k údajom, ktoré môžu byť niekde, v cloude, v službe alebo lokálne.
- Transformácia: Druhým krokom by bola zmena tvaru údajov podľa požiadaviek používateľa.
- Kombinovať: V tomto kroku vykonáme niekoľko transformačných a agregačných krokov a skombinujeme údaje z oboch zdrojov, aby sme vytvorili kombinovanú správu.
- Spravovať: Týmto sa zlúčia a pripoja stĺpce v dotaze k zodpovedajúcim stĺpcom v iných dotazoch v zošite.
Existuje mnoho super výkonných funkcií aplikácie Excel Power Query.
Predpokladajme, že máme údaje o nákupoch za posledných 15 rokov v 180 súboroch. Vedenie organizácie by teraz vyžadovalo konsolidáciu čísel pred ich analýzou. Vedenie spoločnosti môže použiť ktorúkoľvek z nasledujúcich metód:
- Otvorili by všetky súbory a skopírovali ich do jedného súboru.
- Na druhej strane môžu použiť múdre riešenie, ktorým je použitie vzorcov, pretože sú náchylné na chyby.
Nech už si zvolia akýkoľvek spôsob, obsahuje veľa manuálnej práce a po niekoľkých mesiacoch by sa na nové trvanie dostavili nové údaje o predaji. Budú musieť znova urobiť to isté cvičenie.
Power Query im však môže pomôcť, aby nerobili túto namáhavú a opakujúcu sa prácu. Poďme pochopiť tento dopyt na excelovský výkon s príkladom.
Príklad
Predpokladajme, že máme textové súbory v priečinku s údajmi o predaji a chceme tieto údaje získať v našom súbore programu Excel.
Ako vidíme na obrázku nižšie, že v priečinku máme dva typy súborov, chceme však získať údaje iba textových súborov v súbore Excel.
To isté by malo byť:
Krok 1: Najprv musíme získať údaje v Power Query, aby sme mohli vykonať požadované zmeny v údajoch, aby sme ich mohli importovať do súboru programu Excel.
To isté urobíme, keď po kliknutí na príkaz „Získať údaje“ zo skupiny „Získať a transformovať“ na karte „Údaje“ vyberieme v ponuke „Zo súboru “ možnosť „Z priečinka“ .
Krok 2: Vyberte umiestnenie priečinka prehľadaním.
Kliknite na „OK“
Krok 3: Otvorí sa dialógové okno so zoznamom všetkých súborov vo vybranom priečinku s hlavičkami stĺpcov ako „Obsah“, „Názov“, „Prípona“, „Dátum prístupu“, „Dátum zmeny“, „Dátum vytvorenia“. „Atribúty“ a „Cesta k priečinku“.
K dispozícii sú 3 možnosti, tj Kombinovať , Načítať a Transformovať údaje .
- Kombinovať : Táto možnosť slúži na prechod na obrazovku, kde si môžeme zvoliť, ktoré údaje sa majú kombinovať. Krok úpravy je pre túto možnosť preskočený a neposkytuje nám žiadnu kontrolu nad tým, ktoré súbory sa majú kombinovať. Funkcia Kombinovať zlúči každý súbor v priečinku, čo môže viesť k chybám.
- Načítať: Táto možnosť iba načíta tabuľku zobrazenú vyššie na obrázku do hárka programu Excel namiesto skutočných údajov v súboroch.
- Transformácia údajov: Ak použijeme tento príkaz, na rozdiel od príkazu „Kombinovať“ môžeme zvoliť, ktoré súbory sa majú kombinovať, tj. Môžeme kombinovať iba jeden typ súboru (rovnaká prípona).
Rovnako ako v našom prípade chceme kombinovať iba textové súbory (.txt); vyberieme príkaz „Transformovať údaje“ .
Na pravej strane okna môžeme vidieť „Aplikované kroky“. Nateraz sa robí iba jediný krok, ktorým je prevzatie podrobností súborov z priečinka.
Krok 4: Existuje stĺpec s názvom „Prípona“, kde vidíme, že hodnoty v stĺpci sa zapisujú v obidvoch prípadoch, tj. Veľké aj malé písmená.
Musíme však všetky hodnoty previesť na malé písmená, pretože filter medzi nimi rozlišuje. Aby urobili to isté, musíme vybrať stĺpec a potom zvoľte "písmená" z "Format" v ponuke Command.
Krok 5: Údaje budeme filtrovať pomocou stĺpca „Rozšírenie“ pre textové súbory.
Krok 6: Teraz musíme skombinovať údaje pre oba textové súbory pomocou prvého stĺpca „Obsah“. Klikneme na ikonu umiestnenú na pravej strane názvu stĺpca.
Krok 7: Otvorí sa dialógové okno s titulkom „Kombinovať súbory“, kde je potrebné vybrať oddeľovač ako „Tab“ pre textové súbory (súbory s príponou „.txt“) a môžeme zvoliť základňu pre detekciu dátových typov. A kliknite na „OK“.
Po kliknutí na „OK“ dostaneme kombinované údaje textových súborov v okne „Power Query“ .
Podľa potreby môžeme zmeniť typ údajov stĺpcov. V stĺpci „Príjmy“ zmeníme typ údajov na „Mena“.
Kroky aplikované na údaje môžeme vidieť pomocou dotazu na napájanie na pravej strane okna.
Po vykonaní všetkých požadovaných zmien v údajoch môžeme údaje načítať do hárka programu Excel pomocou príkazu „Zavrieť a načítať do“ v skupine „Zavrieť“ na karte „Domov“ .
Musíme si zvoliť, či chceme dáta načítať ako tabuľku alebo ako pripojenie. Potom kliknite na „OK“.
Teraz môžeme údaje vidieť ako tabuľku v pracovnom hárku.
A tabla 'Dotazy k zošitu' na pravej strane, ktorú môžeme použiť na úpravy, duplikovanie, zlúčenie, pripojenie dotazov a na mnohé ďalšie účely.
Excel Power Query je veľmi užitočný, pretože vidíme, že 601 612 riadkov bolo načítaných v priebehu niekoľkých minút.
Na čo treba pamätať
- Power Query nezmení pôvodné zdrojové údaje. Namiesto zmeny pôvodných zdrojových údajov zaznamenáva každý krok, ktorý používateľ urobí pri pripájaní alebo transformácii údajov, a keď používateľ dokončí tvarovanie údajov, vezme rafinovanú množinu údajov a prenesie ju do zošita.
- V Power Query sa rozlišujú malé a veľké písmená.
- Pri konsolidácii súborov v zadanom priečinku sa musíme ubezpečiť, že pomocou stĺpca „Extension“ musíme vylúčiť dočasné súbory (s príponou „.tmp“ a názov týchto súborov začína znakom „~“) ako Power Query môže importovať aj tieto súbory.
