Power Query on sisäänrakennettu Office 365: n, Excel 2016: n, Excel 2019: n Windows-versioihin, ja se on saatavana ilmaiseksi ladattuna Excel 2010: n ja Excel 2013: n Windows-versioissa. Työkalu on suunniteltu purkamaan, muuntamaan ja lataamaan tietoja Exceliin erilaisia lähteitä. Parasta: Power Query muistaa vaiheesi ja toistaa ne, kun haluat päivittää tiedot. Tämä tarkoittaa, että voit puhdistaa ensimmäisen päivän tiedot 80 prosentissa normaalista ajasta, ja voit puhdistaa tiedot päivistä 2-400 napsauttamalla Päivitä.
Sanon tämän monista uusista Excel-ominaisuuksista, mutta tämä on todellakin paras ominaisuus saavuttaa Excel 20 vuoteen.
Kerron suorissa seminaareissani tarinan siitä, kuinka Power Query keksittiin kainalona SQL Server Analysis Services -asiakkaille, jotka joutuivat käyttämään Exceliä Power Pivotiin pääsemiseksi. Mutta Power Query jatkoi parantumistaan, ja jokaisen Exceliä käyttävän henkilön tulisi käyttää aikaa Power Queryn oppimiseen.
Hanki virtakysely
Sinulla saattaa olla jo Power Query. Se on Tiedot-välilehden Hae ja muunna -ryhmässä.
Mutta jos olet Excel 2010: ssä tai Excel 2013: ssa, siirry Internetiin ja etsi Download Power Query. Power Query -komennot näkyvät erillisessä Power Query -välilehdessä valintanauhassa.
Puhdista tiedot ensimmäistä kertaa Power Queryssä
Voit antaa esimerkin Power Queryn mahtavuudesta sanomalla, että saat alla olevan tiedoston joka päivä. Saraketta A ei ole täytetty. Neljännekset kulkevat sivun sijaan.
Aloita tallentamalla työkirja kiintolevylle. Laita se ennustettavaan paikkaan nimellä, jota käytät kyseiseen tiedostoon joka päivä.
Valitse Excelissä Hae tiedot, tiedostosta, työkirjasta.
Selaa työkirjaan. Napsauta Esikatselu-ruudussa Taulukko1. Napsauta Lataa-painikkeen sijasta Muokkaa. Työkirja näkyy nyt hieman erilaisessa ruudukossa - Power Query -ruudukossa.
Nyt sinun on korjattava kaikki tyhjät solut sarakkeeseen A. Jos teit tämän Excel-käyttöliittymässä, raskas komentosarja on Koti, Etsi ja valitse, Siirry erityiseen, Tyhjät, Yhtä suuri, Nuoli ylös, Ctrl + Enter .
Valitse Power Query -kohdassa Muunna, Täytä, Alas.
Kaikki nolla-arvot korvataan ylhäältä tulevalla arvolla. Power Query -toiminnolla se kestää kolme napsautusta seitsemän sijasta.
Seuraava ongelma: Neljännes on menossa poikki eikä alaspäin. Excelissä voit korjata tämän Multiple Consolidation Range -pivot-taulukolla. Tämä vaatii 12 askelta ja yli 23 napsautusta.
Valitse Power Query -sarakkeessa kaksi saraketta, jotka eivät ole neljännekset. Avaa Muuta-välilehden avattava sarakkeiden purku ja valitse Poista muiden sarakkeiden kääntö alla olevan kuvan mukaisesti.
Napsauta hiiren kakkospainikkeella äskettäin luotua Määrite-saraketta ja nimeä se uudelleen Neliö Määritteen sijasta. Kaksikymmentä plus napsautusta Excelissä tulee viisi napsautusta Power Queryssä.
Ollakseni oikeudenmukainen, kaikki puhdistusvaiheet eivät ole lyhyempiä Power Queryssä kuin Excelissä. Sarakkeen poistaminen tarkoittaa edelleen sarakkeen napsauttamista hiiren kakkospainikkeella ja valitsemalla Poista sarake. Mutta ollakseni rehellinen, tässä tarina ei koske ensimmäisen päivän ajansäästöjä.
Mutta odota: Power Query muistaa kaikki vaiheesi
Katso Power Query -ikkunan oikealta puolelta. On luettelo nimeltä Sovelletut vaiheet. Se on välitön tarkastuspolku kaikista vaiheistasi. Napsauttamalla mitä tahansa rataskuvaketta voit muuttaa valintojasi tässä vaiheessa ja saada muutokset vaiheittain läpi tulevat vaiheet. Napsauttamalla mitä tahansa vaihetta näet, miltä tiedot näyttivät ennen tätä vaihetta.
Kun olet puhdistanut tiedot, napsauta Sulje ja lataa alla olevan kuvan mukaisesti.
Kärki
Jos tietosi ovat yli 1 048 576 riviä, voit käyttää Sulje ja lataa -valikkoa ladataksesi tiedot suoraan Power Pivot -tietomalliin, johon mahtuu 995 miljoonaa riviä, jos koneeseen on asennettu tarpeeksi muistia.
Muutamassa sekunnissa muunnetut tiedot näkyvät Excelissä. Mahtava.
Voitto: puhdista tiedot huomenna yhdellä napsautuksella
Mutta jälleen kerran, Power Query -tarina ei koske ensimmäisen päivän ajansäästöjä. Kun valitset Power Queryn palauttamat tiedot, Excelin oikealle puolelle ilmestyy Kyselyt ja yhteydet -paneeli, ja siinä on Päivitä-painike. (Tarvitsemme Muokkaa-painikkeen, mutta koska sitä ei ole, sinun on napsautettava hiiren kakkospainikkeella alkuperäistä kyselyä nähdäksesi tai muuttaaksesi alkuperäistä kyselyä.)
On hauskaa puhdistaa tietoja 1. päivänä. Rakastan tehdä jotain uutta. Mutta kun esimieheni näkee tuloksena olevan raportin ja sanoo “Kaunis. Voitko tehdä tämän joka päivä? ” Vihaan nopeasti vihaa puhdistaa sama tietojoukko päivittäin.
Joten osoittaakseni tietojen puhdistuksen 400 päivän, olen muuttanut alkuperäisen tiedoston kokonaan. Uusia tuotteita, uusia asiakkaita, pienempi määrä, enemmän rivejä, kuten alla on esitetty. Tallennan tämän uuden version tiedostosta samalla polulla ja samalla tiedostonimellä kuin alkuperäinen tiedosto.
Jos avaan kyselyn työkirjan ja napsautan Päivitä, muutamassa sekunnissa Power Query raportoi 92 riviä 68 rivin sijaan.
Päivän 2, päivän 3, päivän 4,… päivän 400,… tietojen puhdistaminen vaatii nyt kaksi napsautusta.
Tämä yksi esimerkki vain naarmuttaa Power Queryn pintaa. Jos vietät kaksi tuntia kirjan kanssa, M on Ken Pulsin ja Miguel Escobarin (Data) -apinalle, opit muista ominaisuuksista, kuten näistä:
- Yhdistämällä kaikki kansiossa olevat Excel- tai CSV-tiedostot yhdeksi Excel-ruudukoksi
- Solun muuntaminen Applella; banaani; kirsikka; tilli; munakoiso viiteen riviin Excelissä
- Tehdään VLOOKUP hakutyökirjaan, kun tuot tietoja Power Queryyn
- Yhden kyselyn tekeminen toiminnoksi, jota voidaan käyttää jokaisella Excel-rivillä
Täydellisen kuvauksen Power Querystä saat M Is for (Data) -apinalle Ken Pulsilta ja Miguel Escobarilta. Vuoden 2019 loppuun mennessä uusi versio Master Data on saatavilla.
Kiitos Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser ja Colin Michael Power Queryn ehdokkaista.
