Tulen taatusti kirjoittamaan paljon Power Query –aiheisia juttuja, sillä se on parasta mitä Exceliin on Power Pivotin lisäksi viime vuosina kehitetty. Vaikka väline on vasta Preview-vaiheessa, olen jo säästänyt paljon sekä omaa aikaani että asiakkaan rahaa työkalun avulla. Tämä tiedon haku-, tuonti-, muokkaus- ja muuntamisväline on monille vieras, joten aloitetaanpa yleiskatsauksella. Käytän tässä artikkelissa paljon kuvia, jotta pääset jyvälle vaikkei sinulla olisi mahdollisuutta asentaa esiversiota koneellesi.

[Edit 01/2015
Tämä juttu näyttää saavan edelleen runsaasti vierailuita, joten info: Power Queryyn on tullut tämän jutun jälkeen runsaasti päivityksiä ja uudistuksia, joten käyttöliittymän ja komentojen sijainti on osittain muuttunut. Power Queryyn kannattaa tutustua alkuvuoden 2015 aikana täydentyvän Power Query Opas Excel-käyttäjälle ohjeiden avulla.]

Mitä meillä oli ennen?

Aikana ennen Power Queryä Exceliin saattoi tuoda tietoja erilaisilla Data (Tiedot) –välilehden tuontikomennoilla lukuisista erilaisista tietolähteistä sekä PowerPivotin avulla versiosta 2010 lähtien. Voit lukea perinteisistä tuontitekniikoista MikroPC-lehteen kirjoittamistani artikkeleista: Datan tuonti Exceliin 1 052013 (pdf) ja Datan tuonti Exceliin 2 + tiedon analysointi 082013 (pdf). Ensimmäisen artikkelin vertailussa mainittu Excel Data Explorer muuttui kesällä 2013 Power Queryksi samalla kun Power Pivotin nimeen ilmestyi välilyönti.

Perinteiset tuontitoiminnot ovat tarjonneet melko vähän (jos mitään) tiedon etsimiseen, muokkaamiseen, muuntamiseen, yhdistämiseen (merge ja append), siivoamiseen, kääntämiseen, puuttuvien tietojen täyttämiseen …  joten suuri osa tuodun datan jälkikäsittelystä on tehty aiemmin joko manuaalisesti, erilaisilla funktioilla (jos sopiva on ollut saatavilla) sekä VBA-makrojen avulla. Jo vuosikausia suurin osa VBA-makrokurssilaisistani on hakenut VBA:sta apua juuri raportointiin liittyvään aineistojen muokkaamiseen.

Mitä meillä on nyt?

Meillä on viimeinkin Excelissä ETL-työkalu! Extract – Transform – Load. Sen lisäksi että Power Query tarjoaa kaikkea edellä mainittua (ja vielä enemmän) ja syrjäyttää monet VBA:lla toteutetut muokkausmakrot, se tarjoaa myös runsaammin erilaisia tietolähteitä. Voit esimerkiksi ladata facebook-postauksia, sähköposteja tai kalenterimerkintöjä (Microsoft Exchange), kokonaisen kansiollisen tekstitiedostoja (From Folder), HTML-taulukoita (From Web), SharePoint-luetteloita, Wikipedia-hakujen tuloksia (Online Search), Active Directory –dataa, Hadoop big dataa jne.

Miten otan käyttöön?

Esiversiosta tulee uusia parannettuja versioita kuukausittain ja sen voi asentaa Excel 2010 ja 2013 -versioihin. Löydät Power Queryn lataussivulta tarkemmat tekniset vaatimukset ja kuten lataussivun kieliluettelosta huomaat, Power Query löytyy myös suomenkielisenä. Kun olet asentanut Power Queryn, se näkyy Excelissä omana välilehtenään.

Jos Power Query ei näy asennuksen jälkeen valintanauhassa:

1. Valitse File > Options > Add-Ins (Tiedosto > Asetukset > Apuohjelmat)
2. Valitse ikkunan alaosasta COM Add-Ins (COM-apuohjelmat) ja Go (Siirry).
3. Rastita Microsoft Power Query Preview for Excel.

Muistathan, että kyseessä on esiversio, joten bugeja voi edelleen esiintyä.

Eka kokeilu ja välineeseen tutustuminen

Silmäilläänpä Power Queryn työskentely-ympäristöä, perustoimintoja ja ideaa yksinkertaisen tekstitiedoston avulla, jonka voit myös ladata itsellesi. Tiedostossa on tuoteryhmittäin ja tuotteittain vuoden 2014 myyntibudjetit. Kunkin tuotteen ja tuoteryhmän summarivit alkavat *-symboleilla ja sarakkeet on erotettu toisistaan pilkuilla, desimaalierottimena on piste. Tiedostosta pitää siivota summarivit pois, vaihtaa pilkut pisteiksi sekä kääntää sarakkeissa olevat kuukausittaiset myyntiluvut pystysuuntaisesti riveille, jotta aineistosta voi laskea helpommin erilaisia yhteenvetoja pivot-taulukoiden avulla.

Kyselyn luonti

Kun valitset Power Query -välilehdeltä From File > From Text ja ladattavan tekstitiedoston, saat esiin Query Editorin eli kyselyn muokkausikkunan.

Editorin (1) esikatselussa näkyy osa tietolähteen riveistä. Erilaiset muunnos- ja muokkaustoimenpiteet suoritetaan joko (2) valintanauhan, (3) pikavalikoiden tai (4) esikatselutaulukon ylänurkan painikkeella. Tietolähteestä riippuen sarakeotsikoissa saattaa suodatusnuolten lisäksi näkyä myös pieniä symboleita, joiden avulla voit esimerkiksi porautua tietoihin, laajentaa taulukoiden sarakkeita esiin tai näyttää yhteenvetotuloksia. Oikealla näkyy (5) Query Settings –palkki, johon jokainen toimenpide tulee näkyviin (6) omana vaiheena. Voit selailla vaiheita, nimetä vaiheet kuvaavammiksi tai poistaa turhia vaiheita. Jokainen suorittamasi vaihe tallentuu funktiona ja kunkin vaiheen funktio näkyy editorin (7) kaavarivillä. Jos kaavarivi tai Query Settings -palkki eivät ole esillä, saat ne esiin View-välilehdeltä. Asetuspaneelin (8) oikeasta alakulmasta valitset haluatko kyselysi lopputuloksen Exceliin ja/tai Power Pivotin tietomalliin (data model), jos käytössäsi on Excel 2013. Kun kyselysi on valmis, suoritat sen valitsemalla (9) Apply & Close.

Ja sitten myyntibudjettilukujen kimppuun

1. Määritä ensimmäisen rivin tiedot otsikoiksi valitsemalla yläkulmasta tai valintanauhasta Use first row as headers.
2. Suodata tähdellä merkityt väli- ja kokonaissummarivit pois. Jotta saat täydellisen suodatusluettelon esiin, käytä ensin suodatusluettelon alareunassa olevaa Load more –vaihtoehtoa.
   (Jos tämä kysely päivitetään ja jos on mahdollista, että seuraavan kerran aineistossa on muitakin kuin kolmea yllä mainittua summariviä, käytä Does Not Contain…  * –tyyppistä suodatusta, joka suodattaa pois kaikki mahdolliset *-merkkejä sisältävät rivit.)
3. Muuta desimaalipisteet pilkuiksi valitsemalla kaikki numeeriset sarakkeet (maalaaminen ei onnistu, joten käytä Ctrl tai Shift-näppäimiä) ja määrittämällä pikavalikon avulla sarakkeiden tietotyyppi sekä missä muodossa luvut ovat alkuperäisessä aineistossa (tässä tapauksessa asetukset ovat desimaalierottimesta johtuen yhdysvaltalaiset). Tämän jälkeen luvut ymmärretään luvuiksi myös suomalaisilla kansallisuusasetuksilla varustetussa työasemassa.
   
4. Seuraavaksi tehdään taikoja eli käännetään sarakkeet riveiksi. Valitse uudelleen kaikki vuoden 2014 sarakkeet ja valitse pikavalikosta Unpivot-komento.
   Ja vot! Olemme saaneet aikaan aineiston, jossa on vain yksi arvosarake ja josta on huomattavasti helpompi laskea erilaisia yhteenvetotuloksia esimerkiksi pivot-taulukoissa! Tässä vaiheessa asetuspalkissa näkyy jo useita vaiheita, joita napsauttamalla saat esiin kyseisen vaiheen lopputuloksen. Voit muokata vaiheita muokkaamalla funktioita kaavarivillä (huomaa kirjainkoot funktioissa!) tai joidenkin vaiheiden kohdalla näkyvien rattaiden avulla. Kuljettamalla hiirtä vaiheiden päällä, saat esiin myös vaiheen poistopainikkeen.
5. Tehdään vielä muutama pieni muutos. Jaetaan Attribute-sarake kuukaudeksi ja vuodeksi …
6. … ja vaihdetaan muodostuneiden sarakkeiden otsikot:
7. Vaiheita on loppujen lopuksi 9 ja halutessasi saat esiin kyselysi kaikki vaiheet yhtenä scriptinä valitsemalla View-välilehdeltä Advanced Editor.Kuten olet huomannut, pystyt luomaan kyselyitä ilman että osaat kirjoittaa Power Queryn kaavakieltä (Power Query Formula Language, jota kutsutaan epävirallisesti myös M-kieleksi). Kielitaitoa tarvitset siinä vaiheessa, kun sinun pitää luoda monimutkaisempia laskennallisia sarakkeita tai tehdä esimerkiksi parametroituja kyselyitä. Huomaa, että kieli on case-sensitiivinen eli komennot pitää kirjoittaa juuri oikean kokoisilla kirjaimilla. Voit ladata kieleen liittyviä dokumentteja täältä.
8. Jätetään tässä vaiheessa kieli sikseen ja suoritetaan lopulta kysely Query Editorin Home-välilehden Apply & Close –painikkeella. Tarkista vielä ennen suoritusta, että olet määrittänyt asetuspaneelin alaosasta latauskohteeksi laskentataulukon.

Työkirjan kyselyiden käsittely

Jos lataat kyselyn tuloksen Excelin laskentataulukkoon ja valitset yhden solun tulosaineiston sisältä, Excelin valintanauhan taulukkotyökaluissa näytetään normaalista poiketen Query-välilehti. Välilehdellä on sekä kyselyn muokkaus (Edit) että päivityspainike (Refresh) lukuisien muiden toimintojen lisäksi.

Jos lataat tiedot Power Pivotin tietomalliin, löydät samat komennot Workbook Queries -palkin avulla, jonka saat komennolla Power Query > Workbook. Palkissa näytetään kaikki työkirjan kyselyt.

Suutarin huomautus: Viimeistään tässä vaiheessa fiksu kyselijä valitsee yllä olevasta valikosta Edit ja kirjoittaa Query Settings -ikkunassa kyselylle kuvaavamman nimen sekä lyhyen kuvauksen siitä millaisia toimenpiteitä kysely suorittaa.

Lisähuomio Excel 2010 -version käyttäjille: Excel 2010:ssä Query Settings -paneelista puuttuu Load to Data Model. Jos haluat ladata tiedot Power Pivotiin Excel-taulukon sijaan, toimi seuraavasti. 1) Ota rasti pois kohdasta Load to Worksheet ja suorita kysely. 2) Siirry Power Pivotiin ja valitse Design-välilehdeltä Existing Connections. Vieritä luettelon alaosasta esiin Workbook Connections, tuplanapsauta listalta löytyvää Power Query -kyselyä ja suorita ohjattu toiminto loppuun.