Power Query je zabudovaný do verzií Office 365, Excel 2016, Excel 2019 pre Windows a je k dispozícii na bezplatné stiahnutie vo verziách Excel 2010 a Excel 2013 pre Windows. Tento nástroj je určený na extrakciu, transformáciu a načítanie údajov do programu Excel z rôzne zdroje. Najlepšia časť: Power Query si pamätá vaše kroky a prehrá ich, keď chcete obnoviť dáta. To znamená, že údaje môžete vyčistiť 1. deň v 80% normálneho času a údaje môžete vyčistiť 2. až 400. deň jednoduchým kliknutím na Obnoviť.
Hovorím to o mnohých nových funkciách programu Excel, ale toto je naozaj najlepšia vlastnosť, ktorá sa v Exceli objaví za posledných 20 rokov.
Na svojich živých seminároch rozprávam príbeh o tom, ako bol produkt Power Query vynájdený ako spojka pre zákazníkov servera SQL Server Analysis Services, ktorí boli nútení používať Excel na prístup k doplnku Power Pivot. Power Query sa však neustále zlepšoval a každý človek, ktorý používa Excel, by si mal nájsť čas na to, aby sa Power Query naučil.
Získajte dopyt Power Query
Možno už máte Power Query. Nachádza sa v skupine Získať a transformovať na karte Údaje.
Ak sa však nachádzate v programe Excel 2010 alebo Excel 2013, choďte na internet a vyhľadajte príkaz Download Power Query. Príkazy Power Query sa zobrazia na vyhradenej karte Power Query na páse s nástrojmi.
Vyčistite údaje prvýkrát v Power Query
Ak chcete uviesť príklad niektorej z úžasných funkcií Power Query, povedzte, že si každý deň zobrazíte nižšie uvedený súbor. Stĺpec A nie je vyplnený. Štvrtiny prechádzajú namiesto dolnej časti stránky.
Ak chcete začať, uložte tento zošit na pevný disk. Vložte ho na predvídateľné miesto s menom, ktoré budete pre tento súbor používať každý deň.
V programe Excel vyberte možnosť Získať údaje, zo súboru, zo zošita.
Prejdite do zošita. Na table Náhľad kliknite na List1. Namiesto kliknutia na načítanie kliknite na Upraviť. Teraz vidíte zošit v trochu inej mriežke - mriežke Power Query.
Teraz musíte opraviť všetky prázdne bunky v stĺpci A. Ak by ste to mali urobiť v používateľskom rozhraní programu Excel, nepraktická sekvencia príkazov je Domov, Nájsť a vybrať, Prejsť na špeciálne, Prázdne miesta, Rovná sa, Šípka hore, Ctrl + Enter .
V Power Query vyberte Transformovať, Vyplniť, Dole.
Všetky hodnoty null sú nahradené hodnotou zhora. Pri Power Query to trvá tri kliknutia namiesto siedmich.
Ďalší problém: Štvrtiny idú naprieč namiesto dolu. V programe Excel to môžete vyriešiť pomocou kontingenčnej tabuľky s rozsahom viacerých konsolidácií. To si vyžaduje 12 krokov a viac ako 23 kliknutí.
V Power Query vyberte dva stĺpce, ktoré nie sú štvrťami. Otvorte rozbaľovaciu ponuku Unpivot Columns na karte Transformácia a zvoľte Unpivot Other Columns, ako je uvedené nižšie.
Pravým tlačidlom myši kliknite na novovytvorený stĺpec Atribút a namiesto Atribút ho premenujte Quarter. Dvadsať plus kliknutí v Exceli sa stane piatimi kliknutiami v Power Query.
Aby sme boli spravodliví, v Power Query nie je každý krok čistenia kratší ako v programe Excel. Odstránenie stĺpca stále znamená kliknutie pravým tlačidlom na stĺpec a výber možnosti Odstrániť stĺpec. Úprimne povedané, tento príbeh nie je o úspore času v 1. deň.
Ale počkajte: Power Query si pamätá všetky vaše kroky
Pozrite sa na pravú stranu okna Power Query. Existuje zoznam s názvom Aplikované kroky. Je to záznam okamžitého auditu všetkých vašich krokov. Kliknutím na ľubovoľnú ikonu ozubeného kolieska môžete zmeniť svoje voľby v danom kroku a vykonať zmeny v ďalších krokoch. Kliknutím na ľubovoľný krok zobrazíte pohľad na to, ako vyzerali údaje pred týmto krokom.
Po dokončení čistenia údajov kliknite na tlačidlo Zavrieť a načítať, ako je to znázornené nižšie.
Tip
Ak sú vaše údaje viac ako 1 048 576 riadkov, môžete pomocou rozbaľovacej ponuky Zavrieť a načítať načítať údaje priamo do dátového modelu Power Pivot, ktorý pojme 995 miliónov riadkov, ak máte v počítači nainštalovaný dostatok pamäte.
Za pár sekúnd sa vaše transformované údaje zobrazia v programe Excel. Úžasné.
Výplata: zajtra čisté dáta jedným kliknutím
Príbeh Power Query však opäť nie je o úspore času v 1. deň. Keď vyberiete údaje vrátené programom Power Query, na pravej strane programu Excel sa zobrazí panel Dotazy a pripojenia a na ňom tlačidlo Obnoviť. (Potrebujeme tu tlačidlo Upraviť, ale pretože tam nie je, musíte kliknúť pravým tlačidlom myši na pôvodný dopyt, aby ste ho mohli zobraziť alebo zmeniť.)
Čistenie údajov v 1. deň je zábavné. Rád robím niečo nové. Ale keď môj manažér uvidí výslednú správu a povie: „Nádhera. Môžete to robiť každý deň? “ Rýchlo som začal nenávidieť nudné čistenie rovnakého súboru dát každý deň.
Na demonštráciu 400. dňa vyčistenia údajov som teda úplne zmenil pôvodný súbor. Nové produkty, noví zákazníci, menší počet, viac riadkov, ako je uvedené nižšie. Túto novú verziu súboru ukladám na rovnakú cestu a s rovnakým názvom súboru ako pôvodný súbor.
Ak otvorím zošit dotazu a kliknem na Obnoviť, program Power Query o niekoľko sekúnd nahlási namiesto 68 riadkov 92 riadkov.
Čistenie údajov v deň 2, deň 3, deň, 4, … deň 400, … deň Infinity teraz vyžaduje dve kliknutia.
Tento jeden príklad iba poškriabe povrch Power Query. Ak s knihou strávite dve hodiny, M je pre (Data) Monkey od Kena Pulsa a Miguela Escobara, dozviete sa o ďalších funkciách, ako sú napríklad tieto:
- Kombinácia všetkých súborov Excel alebo CSV z priečinka do jednej mriežky programu Excel
- Prevod bunky na jablko, banán, čerešňa, kôpor, baklažán na päť riadkov v programe Excel
- Keď prenášate údaje do aplikácie Power Query, robíte VJEDNÁVKU do vyhľadávacieho zošita
- Vytvorenie jedného dotazu vo funkcii, ktorú je možné použiť na každý riadok v programe Excel
Kompletný popis funkcie Power Query nájdete v dokumente M Is for (Data) Monkey od Kena Pulsa a Miguela Escobara. Koncom roku 2019 bude k dispozícii retitledované druhé vydanie, Master Your Data.
Ďakujeme Miguelovi Escobarovi, Robovi Garciovi, Mikeovi Girvinovi, Rayovi Hauserovi a Colinovi Michaelovi za nomináciu programu Power Query.
