Power Query on sisse ehitatud Office 365, Excel 2016, Excel 2019 Windowsi versioonidesse ja on saadaval tasuta allalaadituna Exceli 2010 ja Excel 2013 Windowsi versioonides. Tööriist on loodud andmete ekstraktimiseks, teisendamiseks ja Excelisse laadimiseks allikate mitmekesisus. Parim osa: Power Query jätab teie toimingud meelde ja esitab need, kui soovite andmeid värskendada. See tähendab, et saate 1. päeval andmeid puhastada 80% tavalisest ajast ja 2. – 400. Päeva andmeid saate lihtsalt klõpsata käsul Värskenda.
Ma ütlen seda paljude uute Exceli funktsioonide kohta, kuid see on tõesti parim funktsioon Exceli jõudmiseks 20 aasta jooksul.
Ma räägin oma otseülekandeseminarides loo sellest, kuidas Power Query leiutati karguna SQL Serveri analüüsiteenuste klientidele, kes olid sunnitud Power Pivotile juurdepääsemiseks Exceli kasutama. Kuid Power Query muutus aina paremaks ja iga inimene, kes kasutab Exceli, peaks võtma aega Power Query õppimiseks.
Hankige voolupäring
Võimalik, et teil on juba Power Query. See on vahekaardi Andmed rühmas Hangi ja muuda.
Aga kui kasutate rakendust Excel 2010 või Excel 2013, minge Internetti ja otsige üles allalaaditava toite päring. Teie Power Query käsud kuvatakse lindil spetsiaalsel vahekaardil Power Query.
Andmete puhastamine esmakordselt Power Query'is
Power Query mõne suurepärase näite esitamiseks öelge, et saate allpool näidatud faili iga päev. Veerg A ei ole täidetud. Kvartalid lähevad lehe allapoole asemel risti.
Alustuseks salvestage see töövihik kõvakettale. Pange see ettearvatavasse kohta nimega, mida kasutate selle faili jaoks iga päev.
Valige Excelis valik Andmete hankimine, failist, töövihikust.
Sirvige töövihikusse. Klõpsake eelvaate paanil Leht1. Klõpsake nuppu Laadi asemel klõpsake nuppu Muuda. Nüüd näete töövihikut veidi erinevas ruudustikus - Power Query ruudustikus.
Nüüd peate parandama kõik veeru A tühjad lahtrid. Kui teeksite seda Exceli kasutajaliideses, on kohmakas käsujärjestuses Kodu, Otsi ja valige, Ava spetsiaalne, Tühjad, Võrdsed, Nool üles, Ctrl + Enter .
Valige jaotises Power Query muundamine, täitmine, allapoole.
Kõik nullväärtused asendatakse ülaltoodud väärtustega. Power Query abil kulub seitsme asemel kolm klikki.
Järgmine probleem: kvartalid lähevad allapoole, mitte mööda. Excelis saate selle parandada mitme konsolideerimisvahemiku pöördtabeli abil. See nõuab 12 sammu ja 23 + klikki.
Valige Power Query'is kaks veergu, mis pole veerandid. Avage vahekaardil Muuda rippmenüü Veerude tühistamine ja valige Muud veerud tühjendamine, nagu allpool näidatud.
Paremklõpsake äsja loodud veergu Atribuut ja nimetage see Atribuudi asemel kvartaliks. Kakskümmend plussklikki Excelis saab Power Query's viis klikki.
Nüüd, ausalt öeldes, pole iga puhastusetapp Power Query's lühem kui Excelis. Veeru eemaldamine tähendab ikkagi veeru paremklõpsamist ja valikut Kustuta veerg. Kuid ausalt öeldes pole siin lugu 1. päeva aja kokkuhoiust.
Kuid oodake: Power Query mäletab kõiki teie samme
Vaadake Power Query akna paremal küljel. Seal on loend nimega Rakendatud sammud. See on kõigi teie sammude kohene kontrolljälg. Klõpsake suvalisel hammasrattaikoonil, et muuta selles etapis oma valikuid ja lasta muudatustel astuda läbi edasised toimingud. Klõpsake suvalisel sammul, et näha, kuidas andmed enne seda sammu välja nägid.
Kui olete andmete puhastamise lõpetanud, klõpsake nuppu Sule ja laadige, nagu allpool näidatud.
Näpunäide
Kui teie andmeid on rohkem kui 1 048 576 rida, saate rippmenüü Sule ja laadimine abil laadida andmed otse Power Pivoti andmemudelisse, mis mahutab 995 miljonit rida, kui masinasse on piisavalt mälu installitud.
Mõne sekundi pärast kuvatakse teie teisendatud andmed Excelis. Vinge.
Tasuvus: puhastage andmeid homme ühe klõpsuga
Kuid jällegi pole Power Query lugu aja kokkuhoiust 1. päeval. Kui valite Power Query tagastatud andmed, kuvatakse Exceli paremal küljel paneel Päringud ja ühendused ning sellel on nupp Värskenda. (Siin on vaja nuppu Muuda, kuid kuna seda pole, peate algse päringu kuvamiseks või muutmiseks paremklõpsama algsel päringul).
1. päeval on tore andmeid puhastada. Mulle meeldib midagi uut teha. Aga kui minu juhataja näeb aruannet ja ütleb: „Ilus. Kas saate seda teha iga päev? " Ma hakkan kiiresti vihkama igavust puhastada iga päev sama andmekogumit.
Nii et andmete puhastamise 400. päeva demonstreerimiseks muutsin algset faili täielikult. Uued tooted, uued kliendid, väiksemad arvud, rohkem ridu, nagu allpool näidatud. Salvestan selle uue failiversiooni samale teele ja sama failinimega kui algne fail.
Kui avan päringu töövihiku ja klõpsan käsku Värskenda, teatab Power Query mõne sekundi pärast 68 rea asemel 92 rida.
Andmete puhastamine 2., 3., 4., 4.,…. Päeval …… Päev lõpmatus võtab nüüd kaks klikki.
See üks näide kriimustab ainult Power Query pinda. Kui veedate kaks tundi raamatu juures, on M mõeldud Ken Pulsi ja Miguel Escobari (Data) ahvile, saate teada muudest funktsioonidest, näiteks järgmistest:
- Kombineerides kõik kaustast pärinevad Exceli või CSV-failid ühte Exceli ruudustikku
- Lahtri teisendamine rakendusega Apple; Banaan; Kirss; Till; Baklazaan Excelis viieks rida
- VLOOKUPi tegemine otsingu töövihikule andmete toomisel Power Query'sse
- Ühe päringu tegemine funktsiooniks, mida saab rakendada Exceli igale reale
Power Query täieliku kirjelduse leiate artiklist M Is for (Data) Monkey, autorid Ken Puls ja Miguel Escobar. 2019. aasta lõpuks on saadaval uuesti välja antud teine väljaanne Master Your Data.
Tänud Miguel Escobarile, Rob Garciale, Mike Girvinile, Ray Hauserile ja Colin Michaelile Power Query kandidaatide esitamise eest.
