Power Query: Käyttöliittymä ja kyselyn luonti

Power Query: Käyttöliittymä ja kyselyn luonti
Kyselyoppaan sisällysluettelo
Edellinen kyselyoppaan juttu Seuraava kyselyoppaan juttu

Kun tuot tietoja Exceliin Power Queryllä, kyselyiden luonti aloitetaan Excel 2010 ja 2013 -versioissa Power Query -välilehdeltä ja Excel 2016 -versiossa Data (Tiedot) -välilehden Get & Transform (Hae  & Muunna) -ryhmästä. Lopputuloksen voi ladata joko Excelin taulukkoon ja/tai Power Pivotin tietomalliin.

Jos tuot tietoja Power BI Desktopiin, aloitat kyselyn komennolla Home > Get Data.

[Edit 10/2016: Väline kehittyy joten tämä teksti on tarkistettu ja korvattu lokakuussa 2016.]

Tämän jutun aiheet

Power Query välilehti Excel 2010 ja 2013 -versioissa
Tuontitoiminnallisuudet Excel 2016 -versiossa
Tuontitoiminnallisuudet Power BI Desktopissa
Kyselyn luonti

Kyselyn aloitus
Kyselyeditorissa työskentely
Kyselyn vaiheet
Tietotyypit
Kyselyn tuloksen lataaminen Excelissä
Kyselyn päivittäminen ja muokkaaminen Excelissä
Kyselyn tuloksen lataaminen Power BI Desktopissa sekä sen muokkaus ja päivitys

Power Query välilehti Excel 2010 ja 2013 -versioissa

Power Query välilehden toiminnot voi karkeasti ryhmitellä seuraaviin kokonaisuuksiin. Power Querystä julkistetaan kuukausittain uusi versio, joten käyttämäsi versio voi poiketa artikkelin kuvista.

Tiedon tuonti

ribbon tuonti

Online Search (Online-haku) etsii hakusanoilla tietoja muutamista yhdysvaltalaisista/kansainvälisistä tietolähteistä (mm. englanninkielisestä Wikipediasta, http://www.wordbank.org, data.gov) sekä Power BI for Office 365 -ympäristössä organisaation omista tietolähteistä. [Edit 10/2015: Uusimmissa Power Query -versioissa ei enää ole Online Search -toimintoa.]

Muut Get External Data (Hae ulkoiset tiedot) -ryhmän toiminnot noutavat tietoja lukuisista erilaisista tietolähteistä.

From Table (Taulukosta) komennolla tehdään kysely samassa työkirjassa olevasta taulukoksi muunnetusta alueesta. Tämän avulla voidaan tuottaa esimerkiksi eri muodossa olevia osajoukkoja samaan työkirjaan tai Power Pivotiin.

 Työkirjan kyselyiden käsittely

ribbon kyselyt

Show Pane (Näytä ruutu) tuo esiin tai piilottaa paneelin, jossa näytetään kaikki työkirjan kyselyt. Uusimmissa versioissa on myös painike, joka käynnistää kyselyeditorin (Launch Editor).

Merge (Yhdistä) -vaihtoehdolla yhdistetään kahden olemassa olevan kyselyn tiedot yhdeksi uudeksi kyselyksi. Merge edellyttää, että molemmissa kyselyissä on yhdistävä tietosarake, kuten tuotetunnus tai jokin id-numero. Yksinkertaistettuna merge-toiminnon voi ajatella tekevän VLOOKUP (PHAKU) -funktion kaltaisen yhdistämisen, mutta se on monipuolisempi, sillä mergen avulla voi yhdistellä helposti myös useampien sarakkeiden perusteella.

Append (Liitä loppuun) puolestaan yhdistää samankaltaisten kyselyiden tiedot peräkkäin yhdeksi pidemmäksi listaksi. Kyselyiden ei edes tarvitse olla täysin samanlaisia, eli lopputuloksessa voi olla enemmän sarakkeita kuin alkuperäisissä.

Asetukset, pilviympäristö ja ohjeet

ribbon asetukset ja kirjautuminen

Loput välilehden toiminnot liittyvät työkirjan ja ylipäätään erilaisten tietolähteiden asetuksiin sekä ohjeisiin. Mikäli organisaatiossa on käytössä Power BI for Office 365 -pilvipalvelu, tarjolla on siihen liittyviä toimintoja.

Tuontitoiminnallisuudet Excel 2016 -versiossa

Excel 2016 -versiossa tuontikomennot on koottu Data (Tiedot) -välilehdelle yhdeksi ryhmäksi. New Query -painikkeella sekä aloitetaan kyselyn luonti, aloitetaan useiden kyselyiden yhdistely (combine queries) tai määritetään sekä tietolähteiden asetukset että Query-toimintojen asetukset. Lisäksi Data-välilehdeltä voi avata kyselypaneelin (Show Queries) sekä ryhtyä tekemään kyselyä taulukoksi muunnetusta alueesta (From Table).

2016 data-välilehti

Tuontitoiminnallisuudet Power BI Desktopissa

Power BI Desktopissa tuontitoiminnot on koottu Home -välilehdelle External Data -ryhmään.

Power BI tuontitoiminnallisuudet

Kyselyn luonti

Kyselyn aloitus

Seuraavan esimerkin avulla kuvataan kyselyn luonnin perusidea. Esimerkissä noudetaan lista Suomen kunnista Wikipedian sivulta https://fi.wikipedia.org/wiki/Luettelo_Suomen_kunnista, mutta samat perusperiaatteet pätevät moniin muihin tietolähteisiin. Toki tietolähdekohtaisia eroja on ja juttusarjassa tullaan esittelemään tarkemmin myös muita vaihtoehtoja.

  1. Valitse
    Excel 2010/2013 -versiossa Power Query > From Web (Verkosta) tai
    Excel 2016 -versiossa Data > New Query > From Other Sources > From Web tai
    Power BI Desktopissa Home > Get Data > Web.
  2. Kopioi ikkunaan URL: https://fi.wikipedia.org/wiki/Luettelo_Suomen_kunnista
  3. Jos sinulta kysytään kirjautumistietoja, valitse Anonymous ja Connect.anonymous
  4. Koska sivulla on useita erilaisia HTML-taulukoita, avautuu Navigator (Siirtymistoiminto) -ikkuna, josta sinun pitää valita haluamasi taulukko.
  5. Saat esiin esikatselun kustakin taulukosta valitsemalla niitä vuorollaan.
  6. Aloita tietojen muokkaaminen Edit (Muokkaa) -komennolla.
    Navigator-ikkuna
  7. Query Editor (kyselyeditori) käynnistyy.

Huomaa Power BI Desktopissa: Power BI Desktopin pääikkuna sekä juuri avautunut kyselyeditori-ikkuna muistuttavat toisiaan hyvin paljon, joten joskus voi olla hankala havaita kummassa ikkunassa milloinkin olet. Kokeile siirtymällä ikkunasta toiseen ja huomioi ikkunoiden erot. Sinun pitää ladata kyselyn tulos ja poistua kyselyeditorista takaisin pääikkunaan, kun ryhdyt tekemään visualisointeja.

Joidenkin nettisivujen sisältö tuotetaan ohjelmallisesti siten, että kyselyeditori ei pysty lukemaa tietoja, vaikka tiedot näyttävätkin taulukolta. Tiettyjen kaupallisten sivujen sisällön lataaminen on myös tarkoituksellisesti haluttu estää.

Kyselyeditorissa työskentely

Kysely ja erilaiset aineiston muokkaus- ja muunnosoperaatiot tehdään kyselyeditori-ikkunassa joko valintanauhan eri välilehdiltä tai sarakeotsikoiden päältä avautuvista pikavalikoista tai suodatusnuolten avulla. Kuva on Excelin Power Querystä ja poikkeaa väritykseltään Power BI Desktopin kyselyeditorista.kayttoliittyma

(A) Vasemmalla näkyvässä Queries-paneelissa näytetään kaikki tiedoston kyselyt ja paneelin avulla voi siirtyä kyselystä toiseen sekä suorittaa kyselyille pikavalikoiden avulla erilaisia operaatioita.

(B) Editorin ”esikatselussa” näytetään vain muutamia satoja rivejä. Joskus tämä esikatselu muodostuu vanhasta välimuistissa olevasta datasta, jos olet muokkaamassa aiemmin luotua kyselyä. Jos epäilet, että esikatselu ei näytä sinulle ajan tasalla olevaa tietoa, voit päivittää esikatselun Refresh Preview -painikkeella.

(C) Jos haluat tarkastella tietyn solun sisällä olevaa merkkijonoa tarkemmin, saat alaosaan tarkasteluruudun valitsemalla solun. Voit kulkea tarkasteluruudussa nuolinäppäimillä, jos sinun pitää esimerkiksi tutkia onko merkkijonon alussa tai lopussa turhia välilyöntejä tai laskea merkkijonon merkkien lukumäärää.

(D) Oikealla Query Settings (Kyselyasetukset) paneelissa

(1) annetaan kyselylle nimi sekä kirjoitetaan lyhyt kuvaus (All Properties, Kaikki ominaisuudet)

(2) tarkastellaan, muokataan ja poistetaan kyselyn vaiheita (steps).

(E) Paneelista valitun vaiheen (step) funktio näkyy kaavarivillä, jos olet tuonut kaavarivin esiin komennolla View > Formula bar (Näytä > Kaavarivi). Suosittelen ottamaan kaavarivin esiin, siitä on jatkossa hyötyä.

(F) Excelissä valmis muokattu aineisto ladataan joko Exceliin ja/tai Power Pivotiin Close & Load (Sulje ja lataa) -painikkeen vaihtoehdoilla. Joitakin kyselyitä ei ladata sellaisenaan lainkaan, vaan kyselyä on tarkoitus käyttää toisessa kyselyssä esimerkiksi tietojen yhdistämiseen. Tästä syystä painikkeen takaa löytyy myös vaihtoehto, jolla kysely ei lataudu, vaan luodaan pelkkä yhteys. Power BI Desktopissa kyselyn tulos ladataan Close & Apply -painikkeella.

Ikävä ominaisuus Excelissä: Kun Power Query Editor on avoinna, et pääse siirtymään siihen Excel-työkirjaan, johon olet luomassa kyselyä. Jos haluat viereilla työkirjassa, editori pitää sulkea. Power BI Desktopissa pystyt siirtymään pääikkunan ja kyselyeditorin välillä vapaasti.

Kyselyn vaiheet

Kun ryhdyt tekemään aineistolle muokkauksia, ne tallentuvat askelina (step). Editori ikään kuin nauhoittaa suorittamiasi toimenpiteitä.

  1. Kirjoita kyselylle nimeksi Kunnat oikealla olevaan paneeliin ja tuo kaavarivi esiin komennolla View > Formula bar (Näytä > Kaavarivi), jos se ei vielä ole esillä.
  2. Kun olet aloittanut kyselyn ja päässyt editoriin, Microsoftin tuotteille tyypilliseen tapaan editori on saattanut tehdä asioita puolestasi ja luoda useampia askelia datan sisällöstä riippuen. Monesti automaattisesti luodut askeleet tekevät haluttuja toimenpiteitä, mutta joskus oletukset voivat olla virheellisiä tai turhia.
  3. Napsauta kutakin askelta Query Settings (Kyselyasetukset) -paneelissa ja näet esikatselussa, millaisen lopputuloksen kukin askel on saanut aikaan. Voit myös tarkastella vaiheen toteuttavaa funktiota kaavarivillä. Esimerkiksi tässä kyselyssä kyselyeditori on tunnistanut tietyt sarakkeet numeerisiksi ja osan sarakkeista tekstiksi. Tietotyyppi näkyy kaavan lisäksi myös sarakeotsikoissa. Askeleiden toimintaa voi muokata joko funktiosta kaavariviltä tai askeleiden kohdalla olevilla ratas-painikkeilla. Jos kyselyeditori on tehnyt turhan vaiheen tai haluat poistaa itse tekemäsi operaation, voit poistaa vaiheen sen edessä olevalla rastilla.Tässä esimerkkikyselyssä kyselyeditori on tulkinnut kunnan numeron numeeriseksi tiedoksi ja poistanut kuntanumeroiden edestä etunollat. Jos haluaisit säilyttää etunollat voisit esimerkiksi poistaa Changed Type -vaiheen ja määrittää itse haluamasi tietotyypit eri sarakkeille. Tai voisit muokata kaavasta kohtaa, jossa tietotyyppimääritys tehdään (int64.Type).
    huomaa.png
  4. Tarkista, että kyselyn viimeinen askel on valittuna ennen kuin ryhdyt lisäämään uusia. Uusi askel lisätään aina valittuna olevan askeleen perään.
  5. Poista pikavalikon avulla turha Vaakuna-sarake ja kiinnitä huomiota, että poisto lisää uuden vaiheen askelten joukkoon.vaakunan-poisto
  6. Joidenkin sarakeotsikoiden kohdalla on indeksinumeroita hakasulkeiden sisällä. Lisäksi Kuntanro -sarakkeessa on tavuviiva. Siisti otsikot kaksoisnapsauttamalla niitä ja kirjoittamalla tilalle haluamasi tekstit.
  7. Tarkista vielä ennen datan latausta numeeristen sarakkeiden tietotyypit. Lue myös tietotyyppeihin liittyvä alla oleva tärkeä ohje! Jos sinulla ei näy tietotyyppejä sarakeotsikoiden edessä, lataa ja asenna uusin versio ohjelmasta.
    tietotyypitVihje: Editorissa ei voi valita useita sarakkeita maalaamalla kuten Excelissä, mutta voit valita maapinta-alan ja väestötiheyden kerralla, joko Shift– tai Ctrl-näppäimen avulla, kuten yleensäkin useita eri vaihtoehtoja valitaan.

Tietotyypit

Lukujen, päivämäärien ja kellonaikojen tietotyyppien määrittäminen on yksi tärkeimmistä kyselyeditorin operaatioista, jotta tiedot latautuvat oikeassa muodossa. Tietyissä tilanteissa tiedot latautuvat oikeassa muodossa ilman tietotyypin määrityksiä, mutta erityisesti Excelin Power Pivotiin ladattaessa täytyy olla tarkkana. Huomaa, että tietotyyppien tarkistaminen on tarpeen, vaikka tiedot näyttäisivät silmämääräisesti olevan oikeassa muodossa.

Tietotyypin voi tarkistaa kunkin otsikon edessä näkyvästä symbolista ja sen voi vaihtaa pikavalikon Change Type (Muuta tyyppi) -valikon avulla. Jos otsikossa lukee ABC ja 123, kyselyeditori ei ole pystynyt määrittämään onko kyseessä teksti vai numeerinen sarake.

Kun muutat sarakkeen tietotyyppiä, tulokseksi voi  tulla Error-virheilmoituksia, jos datassa on käytetty esimerkiksi pistettä desimaalierottimena. Valitse tällöin tietotyyppi Using Locale -komennolla ja kerro, missä muodossa luvut on esitetty (esim. English United States).

desimaalit.png

Power Queryssä määritetään vain tietotyyppi, mutta ei oleta kantaa tiedon muotoiluun, kuten desimaalien määrään. Muotoilu tehdään Excelissä, Power Pivotissa tai Power BI Desktopissa tietojen lataamisen jälkeen.

Kyselyn tuloksen lataaminen Excelissä

Tähän esimerkkiin liittyvä lähdedata on hyvin yksinkertainen eikä kyselyyn tarvita muita vaiheita, mutta jos tutkit kyselyeditorin välilehtiä tai pikavalikoita, huomaat että editorissa voisi tehdä hyvin monipuolisesti erilaisia operaatioita tiedolle. Tietoa voisi pilkkoa, ryhmitellä, lajitella tai sitä voisi suodattaa otsikoissa olevien suodatusnuolten avulla, jne.

Jos valitset vaihtoehdon Close & Load (Sulje ja lataa), tiedot latautuvat joko Exceliin tai Power Pivotiin siitä riippuen, millaiset kyselyasetukset olet määrittänyt Excelin puolella. Valitsemalla Close & Load To (Sulje ja lataa kohteeseen) pääset valitsemaan oletusasetuksesta poikkeavan latauskohteen.

load

load to

  • Vaihtoehto Table (Taulukko) lataa tiedot Exceliin uuteen laskentataulukkoon ja antaa taulukoksi muunnetulle kohdealueelle nimeksi kyselylle antamasi nimen.
  • Vaihtoehto Only Create Connection (Luo vain yhteys) ei lataa tulosta mihinkään, mutta luo kyselyn, jotta kyselyä voi käyttää hyödyksi esimerkiksi muissa kyselyissä.
  • Excel 2013 tai 2016 -versiossa voi valita vaihtoehdon Add this to the Data Model (Lisää nämä tiedot tietomalliin) jolloin tulos latautuu Power Pivotin tietomalliin ja taulun nimeksi tulee kyselylle antamasi nimi. Yleensä tietoja ei tällöin ladata duplikaattina Exceliin, joten Table (Taulukko) -vaihtoehto vaihdetaan Only Create Connection (Luo vain yhteys) -vaihtoehdoksi.

Valitse nyt ensimmäisellä kerralla latauskohteeksi Excel eli Table (Taulukko) ja suorita lataus.

Kyselyn päivittäminen ja muokkaaminen Excelissä

Kun tiedot on ladattu Exceliin, voit suorittaa kyselyn päivityksen monella eri tavalla.

  1. Valitsemalla tulostaulukon päältä pikavalikosta komennon Refresh (Päivitä) tai
  2. Tuomalla esiin työkirjan kyselyt Show Pane (Näytä ruutu) -painikkeella ja napsauttamalla kyselyn nimen kohdalla olevaa päivityspainiketta tai
    (Edit 01/2015: Alla oleva kuva on 12/2014 Power Query -versiosta, jossa painikkeen nimenä oli Workbook Queries). Excel 2016 -versiossa paneeli tuodaan esiin komennolla Data > Show Queries.
  3. Kyselyn nimen päältä pikavalikosta.

päivitys ja muokkaus

Pääset kyselyn nimen päältä pikavalikosta mm. muokkaamaan kyselyä (edit). Kun tutustut pikavalikkoon, huomaat että tarjolla on monia muita hyödyllisiä komentoja. Voit esimerkiksi monistaa (duplicate) kyselystä helposti kaksoiskappaleita, jos haluaisit tuoda kaupungit ja muut kunnat erillisinä luetteloina Exceliin. Edellä mainitut komennot löytyvät myös valintanauhaan ilmestyneeltä Query (Kysely) -välilehdeltä, kun valitset yhden solun tulosalueen sisältä.

Kun tallennat tiedoston, kysely tallentuu sen mukana.

Kyselyn tuloksen lataaminen Power BI Desktopissa sekä sen muokkaus ja päivitys

Lataat kyselyn tuloksen Power BI Desktopiin komennolla Close & Apply. Latauksen jälkeen voisit ryhtyä yhdistelemään dataan muita kyselyitä tai tekemään aineistosta visualisointeja. Ladattu data näkyy Power BI Desktopissa Data -osiossa. Pääset muokkaamaan kyselyä editorissa Edit Queries -toiminnolla ja voit päivittää datan Refresh -komennolla.

Power BI ladattu data

Kokeile itse:

A) Nouda Suomen Pankin valuuttakurssit osoitteesta http://www.suomenpankki.fi/fi/tilastot/valuuttakurssit/Pages/default.aspx

Ohjeita:

  1. Anna kyselylle nimi.
  2. Poista ensimmäinen rivi komennolla Home > Remove Rows.
  3. Poista ensimmäinen turha sarake
  4. Määritä tietotyypit.
  5. Määritä sarakeotsikot.

B) Nouda Perusteollisuuden osakelista Kauppalehden sivuilta: http://www.kauppalehti.fi/5/i/porssi/porssikurssit/lista.jsp?reverse=false&order=alpha&markets=XHEL&volume=cur&psize=50&listIds=kaikki&rdc=157ed41f6c4&gics=0&refresh=60&currency=euro

Ohjeita:

  1. Anna kyselylle nimi.
  2. Poista ylin rivi.
  3. Korvaa Replace-komennolla kaikista sarakkeista ne solut, jotka sisältävät vain viivan. (Viiva-solujen tietotyyppiä ei pysty määrittämään.) Suorita korvaus siten, ettet korvaa pois lukujen edessä olevia viivoja eli miinus-merkkejä.
  4. Korvaa prosentti-sarakkeesta prosenttimerkit pois, sillä kyselyeditori ei tunnista %-merkkejä.
  5. Poista turhat sarakkeet.
  6. Määritä jäljelle jäävien sarakkeiden tietotyypit.

Tutki saisitko muodostettua kyselystäsi duplikaatin ja muokattua sitä, jotta toinen kyselysi muodostaisi jonkun toisen osakelistan (esim . Teollisuustuotteet ja palvelut).

Edellinen kyselyoppaan juttu Seuraava kyselyoppaan juttu ->

9 kommenttia artikkeliin ”Power Query: Käyttöliittymä ja kyselyn luonti

  1. Power query opas on upea, Hyvä! Siinä on oivallettu oikeita ja sellaisia asioita joita tarvitaan mutta joita on vaikea löytää.

    Tykkää

  2. Hei. Minulla on vuosikohtaiset tiedostot, jotka voin Appendilla liittää peräkkäin. Kun olen määritellyt yhden vuoden tuonnin (mm. poistanut turhia sarakkeita ja muuntanut tiedot oikeaan muotoon), pystynkö käyttämään tätä määrittelyä seuraavan vuoden tietojen tuonnissa ilman, että tarvitsee tehdä samoja määrittelyjä jokaiseen vuoteen uudelleen. Vuosikohtainen tuonti sen takia, että kuluvan vuoden tietoja päivitetään kuukausittain ja vanhat vuodet pysyy stabiilina.

    Tykkää

    • Moi Auli,

      Voit tehdä yhdestä kyselystä helposti kopioita valitsemalla kyselypaneelissa pikavalikosta komennon Duplicate.

      Vielä helpommalla pääset, jos sijoitat tiedostot samaan kansioon, jotta voit ladata ne Exceliin yhdellä komennolla: Power Query > From File > From Folder. Huomaa, että jos teet kansioon alikansioita, nekin latautuvat. Jos kyseessä tekstitiedostot, tässä ohjevideo:

      Mikäli kyseessä on Excel-tiedostojen lataus, katso tämä video:

      Tykkää

  3. Oletko törmänny ongelmaan jossa PQ haku toimii excel 2010 versiossa mutta ei 2013?
    Ps. Hyvät sivut ja hienosti kirjoitettu.

    Tykkää

    • Kiitti Elmeri,

      Olen käyttänyt Excel 2010 -versiota useissa asiakkaille toteutetuissa ratkaisuissa ja kaikki testaamani 2010:ssä toteutetut Power Query -kyselyt ovat toimineet myös Excel 2013 -versiossa. Voisiko olla mahdollista, että Excel 2010 -työasemaan olisi asennettu uudempi PQ-versio kuin Excel 2013-työasemaan? Uudemmalla tehdyt kyselyt eivät välttämättä toimi vanhemmalla.

      t. Heidi

      Tykkää

      • Sama versio on. Kokeilin tehdä uudelleen kyselyn samasta sivustosta Excel 2013 versiolla mutta haku ei löydä sivustolta taulukkoa lainkaan. (Normaalisti Table 0 ja Document) Ompas visainen ongelma!

        Tykkää

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