Power Query je vgrajen v različice sistema Office 365 za Office 365, Excel 2016, Excel 2019 in je na voljo kot brezplačen prenos v različicah sistema Windows 2010 za Excel 2010 in Excel 2013. Orodje je zasnovano za pridobivanje, pretvorbo in nalaganje podatkov v Excel iz različnih virov. Najboljši del: Power Query si zapomni vaše korake in jih bo predvajal, ko želite osvežiti podatke. To pomeni, da lahko podatke na 1. dan očistite v 80% običajnega časa, podatke na 2. do 400. dan pa lahko očistite s preprostim klikom Osveži.
To govorim o številnih novih Excelovih funkcijah, vendar je to res najboljša lastnost, ki jo je Excel dosegel v 20 letih.
V svojih seminarjih v živo pripovedujem o tem, kako so Power Query izumili kot berglo za stranke SQL Server Analysis Services, ki so bile prisiljene uporabiti Excel za dostop do Power Pivota. Toda Power Query je postajal vedno boljši in vsak, ki uporablja Excel, bi si moral vzeti čas za učenje Power Queryja.
Pridobite Power Query
Morda že imate Power Query. Nahaja se v skupini Get & Transform na zavihku Data.
Če pa uporabljate Excel 2010 ali Excel 2013, pojdite na internet in poiščite Download Power Query. Ukazi za Power Query bodo prikazani na zavihku Power Query na traku.
Prvič očistite podatke v Power Query
Če želite predstaviti primer izjemne možnosti Power Query, recimo, da spodaj prikazano datoteko dobite vsak dan. Stolpec A ni izpolnjen. Četrtine namesto po strani gredo čez.
Za začetek shranite delovni zvezek na trdi disk. Postavite ga na predvidljivo mesto z imenom, ki ga boste za to datoteko uporabljali vsak dan.
V Excelu izberite Pridobi podatke, Iz datoteke, Iz delovnega zvezka.
Poiščite delovni zvezek. V podoknu za predogled kliknite Sheet1. Namesto da kliknete Naloži, kliknite Uredi. Zdaj delovni zvezek vidite v nekoliko drugačni mreži - mreži Power Query.
Zdaj morate popraviti vse prazne celice v stolpcu A. Če bi to storili v Excelovem uporabniškem vmesniku, je okorno zaporedje ukazov Domov, Najdi in izberi, Pojdi na posebno, Prazne, Enake, Puščica gor, Ctrl + Enter .
V Power Query izberite Transform, Fill, Down.
Vse ničelne vrednosti se nadomestijo z vrednostmi od zgoraj. Z Power Query traja tri klike namesto sedem.
Naslednja težava: četrtine gredo čez, namesto navzdol. V Excelu lahko to popravite z vrtilno tabelo z več konsolidacijskimi območji. To zahteva 12 korakov in 23+ klikov.
V Power Query izberite dva stolpca, ki nista četrtini. Odprite spustni meni Unpivot Stolpci na zavihku Transform in izberite Unpivot Other Stolumns, kot je prikazano spodaj.
Z desno miškino tipko kliknite novo ustvarjeni stolpec Atribut in ga preimenujte v Četrtina namesto v Atribut. Dvaindvajset klikov v Excelu postane pet klikov v programu Power Query.
Zdaj po pravici povedano ni vsak korak čiščenja krajši v Power Query kot v Excelu. Odstranjevanje stolpca še vedno pomeni desni klik stolpca in izbiro Odstrani stolpec. Toda če sem iskren, tukaj ne gre za prihranek časa na 1. dan.
Toda počakajte: Power Query si zapomni vse vaše korake
Poglejte na desno stran okna Power Query. Obstaja seznam z naslovom Uporabljeni koraki. To je takojšnja revizijska sled vseh vaših korakov. Kliknite katero koli ikono zobnika, če želite spremeniti izbiro v tem koraku in spremeniti kaskadne spremembe skozi prihodnje korake. Kliknite kateri koli korak, da si ogledate, kako so bili podatki videti pred tem korakom.
Ko končate s čiščenjem podatkov, kliknite Zapri in naloži, kot je prikazano spodaj.
Nasvet
Če imajo vaši podatki več kot 1.048.576 vrstic, lahko s spustnim menijem Zapri in naloži podatke neposredno v podatkovni model Power Pivot, ki lahko sprejme 995 milijonov vrstic, če je v napravi nameščenega dovolj pomnilnika.
Čez nekaj sekund se bodo vaši preoblikovani podatki pojavili v Excelu. Super
Izplačilo: jutri očistite podatke z enim klikom
Toda spet pri zgodbi Power Query ne gre za prihranek časa na 1. dan. Ko izberete podatke, ki jih vrne Power Query, se na desni strani Excela prikaže plošča Queries & Connections in na njej je gumb Refresh. (Tukaj potrebujemo gumb Uredi, ker pa ga ni, morate z desno miškino tipko klikniti izvirno poizvedbo, če si želite ogledati ali spremeniti prvotno poizvedbo).
Zabavno je čiščenje podatkov 1. dan. Rad počnem kaj novega. Ko pa moj upravitelj vidi nastalo poročilo in reče »Lepo. Ali lahko to počnete vsak dan? « Hitro sovražim dolgočasno čiščenje istega nabora podatkov vsak dan.
Za prikaz dneva 400 čiščenja podatkov sem popolnoma spremenil prvotno datoteko. Novi izdelki, nove stranke, manjše število, več vrstic, kot je prikazano spodaj. Novo različico datoteke shranim na isto pot in z istim imenom kot izvirna datoteka.
Če odprem delovni zvezek za poizvedbe in kliknem Osveži, Power Query v nekaj sekundah poroča 92 vrstic namesto 68 vrstic.
Čiščenje podatkov na dan 2, dan 3, dan, 4,… dan 400,… Day Infinity zdaj traja dva klika.
Ta primer samo opraska površino Power Query. Če z knjigo preživite dve uri, M je za (Data) Monkey, avtorjev Kena Pulsa in Miguela Escobarja, boste spoznali še druge funkcije, na primer te:
- Združevanje vseh datotek Excel ali CSV iz mape v eno Excelovo mrežo
- Pretvorba celice z jabolkom; banana; češnja; koper; jajčevec v pet vrstic v Excelu
- Izvajate VLOOKUP v iskalni delovni zvezek, ko vnašate podatke v Power Query
- Izdelava ene same poizvedbe v funkcijo, ki jo je mogoče uporabiti za vsako vrstico v Excelu
Za popoln opis Power Query si oglejte M Is for (Data) Monkey avtorjev Kena Pulsa in Miguela Escobarja. Do konca leta 2019 bo na voljo obnovljena druga izdaja Master Your Data.
Zahvaljujemo se Miguelu Escobarju, Robu Garcii, Miku Girvinu, Rayu Hauserju in Colinu Michaelu za nominacijo Power Query.
