Luuletko tuntevasi Excelin? Osa 1: Power Query

Vaikka Excel itsessään on sen hyvin osaavalle käyttäjälle mielettömän tehokas taulukkolaskentaväline, sen Power BI -lisäosat ovat hieman kuin olisi käännetty Excelistä nupit kaakkoon ja volat täysille. Power BI for Excel on tuonut Exceliin tietojen päivitettävyyden, paremman analysointi- ja laskentatehokkuuden ja interaktiiviset datavisualisointikyvykkyydet. Power Query tarjoaa saumattoman kokemuksen datan löytämiselle, datan muokkaamiselle ja sen rikastamiselle sekä sen jakamisen tietotyöläisille, BI-ammattilaisille ja muille käyttäjille. 

Monien mielikuva Excelistä lienee sama, joka minullakin oli vielä jokin aika sitten – sillä voisi tehdä vaikka mitä ja siitä saisi varmasti irti paljon enemmän, jos vaan jaksaisi opetella. Kuitenkaan motivaatiota varsinaiseen opetteluun en löytänyt, sillä mahdollisuudet pitkäjänteisempään raportointiin ja analyysityöhön tuntuivat rajallisilta, sillä kovalla työllä tehdyt laskukaavatkin olivat kertakäyttöisiä ja jonkun muun laatimat Excel-pohjat tuntuivat olevan niin hiuskarvan varassa pystyssä, että jos vähänkään johonkin koski, niin ERROR ERROR ERROR!

No, erroreista en lupaa pääsevänne kokonaan eroon, mutta uskallan sanoa kuitenkin, että nämä käsitykset kannattaa jättää romukoppaan! Esimerkiksi Power Pivot pystyy käsittelemään miljoonia rivejä dataa, ja vielä kaatumatta, eli Excel on tätä nykyä varteenotettava raportointityökalu isoillekin datamäärille. Excelin PowerBI:n raporttien rakentaminen menee yksinkertaistettuna seuraavasti:

1. Power Queryllä haetaan ja muokataan liiketoimintadataa
2. ladataan se Power Pivotiin analysoitavaksi, lisätään siihen mukautettuja sarakkeita ja arvoja ja lasketaan KPI:t
3. Power View :llä tehdään interaktiiviset visualisoinnit.

Tämän jälkeen raportit toki kannattaa jakaa PowerBI -sivustoilla ja saada raportoinnista kaikki hyöty irti. Tässä blogissa esittelen teille Power Queryn.

Power Query

Power Query on Excelin lisäosa, joka yksinkertaistaa tietojen etsintää, käyttöä ja yhteistyötä. Yksi luonnehdinta voisi olla ”Excelin mini-ETL” (extract-transform-load) eli työkalu, jolla voit hakea erilaisista lähteistä tietoa, muokata ja suodattaa tiedon haluamaasi muotoon ja ladata sen tämän jälkeen joko Excel-taulukkoon tai suoraan Power Pivotin tietomalliin. Käytännössä siis teet teet kyselyn eli yhdistät ensin Power Queryn haluamaasi tietolähteeseen, esim. SQL Serverin tai Oraclen tietokantaan, suodatat ja muokkaat sen sisältämää tietoa omiin tarpeisiisi, ja vasta tämän jälkeen Excel lataa sen työkirjaan.

Tuettuja tietolähteitä ovat mm. erilaiset On Premise relaatiotietokannat, Azure, verkkosivut, OData-syötteet ja HDFS. Kyselyitä voi yhdistää toisiin kyselyihin ja yhdistää tietoa myös useista lähteistä. Kyselyitä voi myös tallentaa myöhempää käyttöä varten. Niitä voi päivittää, jolloin kysely hakee ajantasaisen tiedon lähteestä. Voit myös putsata ja rikastaa dataa Power Queryn välineillä tai käyttää erityistarpeisiin myös Power Queryn M-koodikieltä. Dataa voi siis hakea erilaisista tietokannoista (tarkempi luettelo blogin lopussa), mutta myös voit myös noutaa verkkosivuilla tai muissa tiedostoissa ja kansioissa olevaa tietoa suoraan Exceliin.

Näin yhdistät eri tiedostoissa olevaa tietoa samaan tiedostoon ilman copy-paste-kikkailuita ja voit tuoda dataa myös useista tiedostoista kerralla. Tietoa voi myös etsiä suoraan haku suoraan julkisista lähteistä (Online search). Julkiset lähteet kattavat tällä hetkellä tosin vain tietyt Wikipedian, Data.govin ja Azure Marketplacen osa-alueet ja keskittyvät lähinnä U.S.A.:han.

Myös Suomessa on toki paljon avointa dataa, jota voidaan hakea muilla Power Queryn keinoilla (haku verkkosivuilta tai OData syötteet jne.), vaikka niitä ei Power Queryn Online Searchin avulla löydykään. Julkisten hakujen hakupalkista löytyvät myös organisaation omat sisäiset datat, mikäli ne on tuotu Power Queryn haun piiriin  

Yhteistyömahdollisuudet Power BI -lisenssin myötä

Ok, Power Queryllä on nyt tuotu tieto Exceliin, mutta mitä sitten? Datan saaminen sopivassa muodossa Exceliin on toki vasta ensimmäinen askel, tiedon löytyminen datamassasta ja sen raportoiminen vaatii toki jatkotoimenpiteitä joko muilla Power BI -työkaluilla – Power Pivotilla ja Power Viewillä – tai ”perus-Excelin” puolella. Aiemmin mainitut yhteistyömahdollisuudet eivät myöskään vielä tulleet oikein esille, ne tulevatkin käyttöön vasta Power BI -tilauksen myötä. Omasta mielestä ne ovatkin tietotyöläisen arjen kannalta hienoimmat ominaisuudet:

• Kyselyiden ajastettu automaattinen päivitys. Kyselyille voi asettaa automaattisen päivitysaikataulun tapahtuvaksi esimerkiksi kerran päivässä tai kerran tunnissa.
  • Kyselyt voi toki päivittää ilman lisenssiäkin kätevästi, mutta vaatii kuitenkin napin painamisen verran käsityötä ja sen erikseen muistamista.
• Kyselyn voi myös tallentaa ja jakaa muita käyttäjiä varten. Power Query tallentaa kyselyssä tehdyt vaiheet – käyttäjä tekee omilla tunnuksillaan ja käyttöoikeuksillaan kyselyn, hän näkee miten kysely on tehty ja voi tehdä itse omiin tarpeisiinsa tarvittavat muutokset tai muokata kyselyä eteenpäin.
  • Raporttien tekijät välttyvät päällekkäiseltä työltä voidessaan käyttää uudelleen myös muiden tekemiä kyselyitä ja muokata niitä eteenpäin omiin tarpeisiinsa.
  • Valmiiden kyselyiden käyttäminen myös tuo tietokantojen kanssa vähemmän työskenteleville tietotyöläisille tiedon lähemmäksi ja helpommin lähestyttäväksi, jolloin myös kynnys käyttää niitä laskee. Kyselyitä voidaan myös sertifioida ja antaa niille kirjallinen kuvaus, jolloin käyttäjä tietää, että kyselyyn voi luottaa.
  • Kyselyn tallentaminen Data Catalogiin tallentaa
    • kyselyn nimen, kuvauksen, kyselyn käyttämän tietolähteen sijainnin, tiedon serfitioinnista, tiedon ketkä käyttäjät voivat löytää ja käyttää kyselyä, kyselyn dokumentoinnin sijainnin, näytteen datasta, mikäli näin halutaan.
    • Datan muokkauksessa tehdyt vaiheet, jolloin kyselyä käyttävä henkilö näkee mitkä transformaatiot tiedolle tehdään ennen latausta Exceliin
    • Kyselyn jakaminen EI siis tallenna itse dataa
• Organisaation tietolähteiden tuominen haun piiriin
  • Mahdollista julkistaa organisaation sisäistä On Premise -dataa löytyväksi suoraan Online-hausta
  • Käyttäjien ei tarvitse tietää, missä tietokanta sijaitsee voidakseen saada sen käyttöön. Tarvittavat käyttöoikeudet tietoon riittävät. Voit tarkastella analytiikkaa kyselyiden käytöstä – näet mitä kyselyitä käytetään paljon – ja voit hallita tallennettuja kyselyitä, mm. sertifioida tai muuttaa kyselyä.

Power Queryn tukemat tietolähteet

• Tietokannat: SQL Server, Access, SQL Server Analysis Services, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata
• Azure: Azure SQL tietokanta, Azure HDInsight, Azure Blob Storage, Azure Table Storage, Azure Marketplace
• Muut: SharePoint-lista, OData-syöte, Dynamics CRM Online, Hadoop (HDFS), Active Directory, Microsoft Exchange Server, Facebook, SAP BusinessObjects BI Universe, Salesforce Objects ja Salesforce Reports, ODBC
• Verkkosivut: Hae URLin perustella verkkosivulla oleva tieto suoraan Exceliin
• Tiedostot (Excel-tiedosto, CSV, XML, tekstitiedosto)
• Haku suoraan julkisista lähteistä