Power Query ugrađen je u Windows verzije sustava Office 365, Excel 2016, Excel 2019 i dostupan je kao besplatno preuzimanje u Windows verzijama programa Excel 2010 i Excel 2013. Alat je dizajniran za izdvajanje, pretvaranje i učitavanje podataka u Excel iz raznolikost izvora. Najbolji dio: Power Query pamti vaše korake i reproducirat će ih kad želite osvježiti podatke. To znači da podatke na 1. dan možete očistiti u 80% uobičajenog vremena, a podatke na 2. do 400. dan možete jednostavnim klikom na Refresh.
Kažem ovo o mnogim novim značajkama programa Excel, ali ovo je zaista najbolja značajka za postizanje Excela u posljednjih 20 godina.
Na svojim seminarima uživo pričam o tome kako je Power Query izmišljen kao štaka za kupce usluga SQL Server Analysis Services koji su bili prisiljeni koristiti Excel kako bi pristupili Power Pivotu. No Power Query je postajao sve bolji i svaka osoba koja koristi Excel trebala bi odvojiti vrijeme da nauči Power Query.
Dohvatite Power Query
Možda već imate Power Query. Nalazi se u grupi Dohvati i transformiraj na kartici Podaci.
Ali ako koristite Excel 2010 ili Excel 2013, idite na Internet i potražite Download Power Query. Naredbe Power Query pojavit će se na posebnoj kartici Power Query na vrpci.
Očistite podatke prvi put u Power Queryju
Da biste dobili primjer neke nevjerojatnosti Power Queryja, recite da svaki dan dobivate dolje prikazanu datoteku. Stupac A nije popunjen. Četvrtine idu preko stranice umjesto na stranicu.
Za početak spremite tu radnu knjigu na tvrdi disk. Stavite ga na predvidivo mjesto s imenom koje ćete svakodnevno koristiti za tu datoteku.
U Excelu odaberite Dohvati podatke, Iz datoteke, Iz radne knjige.
Dođite do radne knjige. U oknu Preview kliknite Sheet1. Umjesto da kliknete Učitaj, kliknite Uredi. Sada radnu knjigu vidite u nešto drugačijoj mreži - rešetki Power Query.
Sada trebate popraviti sve prazne ćelije u stupcu A. Ako biste to učinili u Excelovom korisničkom sučelju, nezgrapan je redoslijed naredbi Početna, Pronađi i odaberi, Idi na posebno, Praznine, Jednako, Strelica gore, Ctrl + Enter .
U Power Queryu odaberite Transform, Fill, Down.
Sve null vrijednosti zamjenjuju se vrijednostima odozgo. Uz Power Query potrebna su tri klika umjesto sedam.
Sljedeći problem: četvrtine idu umjesto prema dolje. U Excelu to možete popraviti pomoću zaokretne tablice višestrukog raspona konsolidacije. To zahtijeva 12 koraka i 23+ klikova.
U programu Power Query odaberite dva stupca koja nisu četvrtine. Otvorite padajući izbornik Unpivot Columns na kartici Transform i odaberite Unpivot Other Columns, kao što je prikazano dolje.
Desnom tipkom miša kliknite novostvoreni stupac Atribut i preimenujte ga u Četvrtina umjesto Atribut. Dvadeset i više klikova u programu Excel postaje pet klikova u programu Power Query.
Da budemo pošteni, nije svaki korak čišćenja kraći u Power Queryju nego u Excelu. Uklanjanje stupca i dalje znači desni klik na stupac i odabir Ukloni stupac. Ali da budem iskren, ovdje se ne radi o uštedi vremena na 1. dan.
Ali pričekajte: Power Query pamti sve vaše korake
Pogledajte desnu stranu prozora Power Query. Postoji popis pod nazivom Primijenjeni koraci. To je trenutni revizijski trag svih vaših koraka. Kliknite bilo koju ikonu zupčanika da biste promijenili svoje odabire u tom koraku i prešli na kaskadne promjene kroz buduće korake. Kliknite bilo koji korak da biste vidjeli kako su podaci izgledali prije tog koraka.
Kad završite s čišćenjem podataka, kliknite Zatvori i učitaj kao što je prikazano u nastavku.
Savjet
Ako su vaši podaci veći od 1.048.576 redaka, pomoću padajućeg izbornika Zatvori i učitaj podatke možete učitati izravno u model podataka Power Pivot, koji može primiti 995 milijuna redaka ako imate dovoljno memorije instalirane na uređaju.
Za nekoliko sekundi vaši će se transformirani podaci pojaviti u Excelu. Super.
Isplata: očistite podatke sutra jednim klikom
Ali opet, priča o Power Queryju ne odnosi se na uštedu vremena 1. dana. Kada odaberete podatke koje vraća Power Query, s desne strane Excela pojavit će se ploča Queries & Connections, a na njoj se nalazi gumb Refresh (Osvježi). (Ovdje nam je potreban gumb Uredi, ali budući da ga nema, morate kliknuti izvorni upit desnom tipkom miša da biste ga pregledali ili unijeli promjene).
Zabavno je čistiti podatke 1. dana. Volim raditi nešto novo. Ali kad moj menadžer vidi rezultirajući izvještaj i kaže „Lijepo. Možete li to raditi svaki dan? " Brzo počinjem mrziti zamaranje čišćenja istog skupa podataka svaki dan.
Dakle, da bih demonstrirao 400. dan čišćenja podataka, u potpunosti sam promijenio izvornu datoteku. Novi proizvodi, novi kupci, manji broj, više redaka, kao što je prikazano u nastavku. Spremam ovu novu verziju datoteke na isti put i s istim imenom kao izvorna datoteka.
Ako otvorim radnu knjigu upita i kliknem Osvježi, za nekoliko sekundi Power Query prijavljuje 92 retka umjesto 68 redaka.
Čišćenje podataka na dan 2, dan 3, dan, 4, … dan 400, … dan beskonačnosti sada traje dva klika.
Ovaj jedan primjer samo ogrebe površinu Power Queryja. Ako dva sata provedete s knjigom, M je za (Data) Monkey Kena Pulsa i Miguela Escobara, naučit ćete i o drugim značajkama, poput ovih:
- Kombiniranje svih Excel ili CSV datoteka iz mape u jednu Excel mrežu
- Pretvaranje ćelije s jabukom; banana; trešnja; kopar; patlidžan u pet redaka u programu Excel
- Uvođenjem VLOOKUP-a u knjigu pretraživanja dok unosite podatke u Power Query
- Izrada jednog upita u funkciju koja se može primijeniti na svaki redak u programu Excel
Za cjelovit opis Power Queryja pogledajte M Is for (Data) Monkey Kena Pulsa i Miguela Escobara. Krajem 2019. godine bit će dostupno drugo izdanje, Master Your Data (Ovladajte svojim podacima).
Zahvaljujući Miguelu Escobaru, Robu Garciji, Mikeu Girvinu, Rayu Hauseru i Colinu Michaelu na nominiranju Power Queryja.
