Power Query: Kyselyoppaan johdanto ja termit

Power Query: Kyselyoppaan johdanto ja termit
<- Kyselyoppaan sisällysluettelo
Seuraava kyselyoppaan juttu ->

Tämä on suomalaisille Excelin tai Power BI Desktopin käyttäjille tarkoitettu ohjejuttusarja, jossa opastetaan tuomaan, muokkaamaan ja muuntamaan Exceliin tai Power BI Desktopiin tuotavaa dataa. Välinettä kutsutaan eri nimillä ohjelmasta ja versiosta riippuen, mutta toiminnallisuudet ovat kuitenkin samat. Oppaassa on kuvattu eri ohjelmien ja versioiden väliset erot. [Edit 10/2016: Väline kehittyy joten tämä teksti on tarkistettu ja korvattu lokakuussa 2016.]

Ohjelma ja versio  Nimi
Excel 2010 Power Query
Excel 2013 Power Query
Excel 2016 Query Editor (Kyselyeditori)
Power BI Desktop Query Editor (Kyselyeditori)

Tässä kyselyoppaassa välinettä kutsutaan sekä Power Queryksi että kyselyeditoriksi, sillä oppaan kirjoittaminen on aloitettu jo ennen Excel 2016 -version ja Power BI Desktopin julkistusta.

Taustaa

Exceliin voi tuoda tietoja monilla eri komennoilla ja tekniikoilla sekä useista eri tietolähteistä (tekstitiedostot, tietokannat, web, jne.). Excel 2010-versiosta lähtien tuonti kannattaa suorittaa ilmaisen Power Query kyselyeditorin avulla. Kyselyeditori soveltuu tiedon tuontiin erityisesti silloin, kun tietoja pitää päivittää ja tuonti tehdään toistuvasti ja siihen liittyy tiedon muokkausta, siivoamista, muuntamista, pilkkomista ja kääntämistä.

Excelin lisäksi dataa voi mallintaa ja havainnollistaa kesällä 2015 julkistetulla ilmaisella Power BI Desktop -ohjelmalla. Power BI Desktopissa on sisäänrakennettuna Excelin Power Query kyseyeditoria vastaava toiminnallisuus.

 

Miksi?

Power Query julkistettiin maaliskuussa 2014 ja siitä on kirjoitettu runsaasti englanninkielisiä artikkeleita ja ohjeita, joten eivätkö ne riitä?

Seuraavassa muutamia syitä HExcelligent-juttusarjaan sen lisäksi että haluan vilpittömästi edistää ja helpottaa jokaisen suomalaisen Excel ja Power BI -käyttäjän arkea ja toivon, että tämä juttusarja tulee lisäämään välineen käytön osaamista!

  • Lähes kaikki kansainvälisistä kirjoittajista ovat taustaltaan SQL-guruja, jotka kirjoittavat artikkeleita kaltaisilleen SQL- ja BI-asiantuntijoille. Itse tapaan koulutuksissa viikoittain tavallisia Excel-käyttäjiä, joiden tiedän kamppailevan erilaisten tiedon tuontiongelmien kanssa ja näen miten paljon aikaa hukataan manuaalisiin kopiointi- ja muokkaustoimenpiteisiin. Tiedän, että kyselyeditori on väline, joka tuo merkittävää hyötyä ja ajansäästöä projektipäälliköille, tuotepäälliköille, taloushallinnon erilaisissa tehtävissä työskenteleville henkilöille, HR-kontrollereille, insinööreille ja ihan jokaiselle Exceliin tietoja kopioivalle ja niistä raportteja laativille. Kyselyeditorin avulla voi esimerkiksi muodostaa väliotsikoilla varustetusta aineistosta muutamalla komennolla luettelon, jonka jokaisella rivillä toistetaan otsikkotietoja:Power Query Fill Down
  • Kansainvälisistä artikkeleista puuttuu monesti meille suomalaisille oleellinen näkökulma, kuten kansallisuusasetukset (desimaalierottimet, päiväysten muodot) tai eri kielisten Excel-versioiden kirjavuus jopa organisaation sisällä ja kieliversioiden vaikutus kyselyihin.
  • Kansainvälisissä artikkeleissa oletetaan usein, että lukijat käyttävät uusinta Excel 2016 -versiota. Suomessa on edelleen runsaasti jopa 2010-versiota käyttäviä organisaatioita.
  • Suomessa julkistetaan runsaasti kiinnostavaa avointa dataa, jonka muokkaamiseen, siivoamiseen ja muuntamiseen kyselyeditori tarjoaa erinomaisia työkaluja. Haluan kertoa oppaan esimerkeissä meitä suomalaisia kiinnostavien aineistojen käsittelystä ja edistää osaltani avoimen datan hyödyntämistä ruohonjuuritasolla.
  • Excelin Power Querystä ja Power BI Desktopista tulee edelleen kuukausittain uusi päivittynyt versio, joten monet kansainvälisistä artikkeleista sisältävät vanhentunutta tietoa. Artikkeleiden ja ohjeiden soveltaminen on hankalaa henkilöille, jotka eivät ole aktiivisesti seuranneet tuotteen kehitystä ja jotka ehkä jopa haluavat käyttää suomenkielistä versiota.

Hieman ”hyvä tietää” taustatietoja

Power Query Excelissä

Power Query on ilmainen ja sen voi ladata Microsoftin sivuilta Excel 2010 ja 2013 -versioihin. Excel 2016 -versiossa se on sisäänrakennettuna eikä enää esiinny Power Query -nimisenä vaan on upotettu Data (Tiedot) -välilehdelle Get & Transform (Hae & Muunna) -painikeryhmään.

Power Query esiintyy joissakin vanhoissa artikkeleissa nimellä Excel Data Explorer, mikä oli tuotteen epävirallisen esiversion nimi vuoden 2013 alkupuolella. Heinäkuussa 2013 vielä beta-versiovaiheessa ollessaan tuotteen nimeksi vahvistui Power Query ja ensimmäinen virallinen ”oikeaan” käyttöön tarkoitettu Power Query julkistettiin maaliskuussa 2014.

Kun luet netistä Power Query -ohjeita, komennot eivät välttämättä ole samalla välilehdellä kuin omassa versiossasi. Microsoft on lisännyt valintanauhaan välilehtiä ohjelman kehittyessä ja komentoja on samalla siirretty välilehdeltä toiselle. Power Querystä ja Power BI Desktopista julkistetaan edelleen kuukausittain uusia versioita.

Power BI Desktop -ohjelman kyselyt

Microsoft julkisti heinäkuussa 2015 uuden version Power BI -raportointipilvipalvelusta. Palveluun liittyy myös ilmainen Power BI Desktop -ohjelma, jonka voi ladata Microsoftin sivuilta ja joka sisältää käytännössä Excelistä tutut elementit, kuten Power Queryn, Power Pivotin ja Power View -kaavioita muistuttavat vuorovaikutteiset kaaviot. Power Query ei esiinny Power BI Desktopissa Power Query -nimisenä, mutta toiminnallisuudet ovat samat, joten tämä opas sopii myös Power BI Desktop -kyselyiden opiskeluun.

Power Queryn toimintaperiaate

Power Query (tai Power BI Desktopin kyselyeditori) on hyvin helppokäyttöinen apuohjelma, jonka avulla voi tuoda Exceliin (tai Power BI Desktopiin) tietoja ja suorittaa tiedoille monipuolisia muokkaus- ja muunnosoperaatioita. Tiedon tuonnin aikana suoritetut toimenpiteet tallentuvat vaiheina (step) eli tiedon tuonti muistuttaa makrojen nauhoitusta. Lopputuloksena on kuitenkin huomattavasti helpommin muokattava ja ylläpidettävä kokonaisuus kuin perinteiset VBA-makrokoodit.

Jos et ole koskaan nauhoittanut yhtään makroa, älä peljästy. Sinun ei ehkä koskaan tarvitsekaan. Kyselyeditorin oppiminen ei edellytä minkäänlaista makro-osaamista. Olen automatisoinut viimeisen vuoden aikana lukuisien asiakkaiden raportointiin liittyviä tuontioperaatioita Power Queryllä muutamissa kymmenissä minuuteissa, kun vastaavien VBA-makrojen koodaukseen testauksineen olisi saattanut mennä tunteja. Lisäksi Power Query suorittaa muunnokset huomattavan paljon nopeammin kuin vastaavat VBA-makrot. Power Queryn peruskäyttö ei vaadi minkäänlaista ohjelmointitaitoa, joskin Power Queryssä on myös olemassa oma M-kielensä, jota koodarit voivat käyttää kyselyissä.

Kun tiedon tuontioperaatiot on toteutettu ja kysely tallennettu Excel-työkirjaan (tai Power BI Desktop -tiedostoon), kyselyn voi päivittää ja saada uudet ajan tasalla olevat tiedot työkirjaan muokattuina ja muunnettuina.

Tiedon tuonnin ja muokkauksen lisäksi Power Queryllä voi etsiä ja ladata tietoja esimerkiksi Wikipediasta tai netistä (esim. valuuttakurssit, väestömäärät, jne.)

ETL ja SSIS?

Power Query -artikkeleissa mainitaan usein kirjainyhdistelmät ETL ja SSIS, joten on hyvä tietää mitä ne tarkoittavat.

ETL tulee sanoista Extract (= poimia), Transform (= muuntaa) ja Load (= ladata) ja Power Query on väline, jolla voi suorittaa ETL-operaatioita. Perinteisesti tällaisia välineitä on ollut vain eri valmistajien Business Intelligence -järjestelmissä (kuten Cognos, SAS, SQL, jne.).

Jokaisessa Business Intelligence -järjestelmässä on omat ETL-välineensä. SSIS on Microsoftin SQL Server -palvelimella käytettävä ETL-väline. SSIS tulee sanoista SQL Server Integration Services.

<- Kyselyoppaan sisällysluettelo
Seuraava kyselyoppaan juttu ->

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s