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, 2019 ja O365 -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 09/2018: Teksti on tarkistettu ja muokattu syyskuussa 2018.]

Tämän jutun aiheet

Power Query välilehti Excel 2010 ja 2013 -versioissa
Tuontitoiminnallisuudet Excel 2016, 2019 ja O365 -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 muutamia kertoja vuodessa uusi versio, joten käyttämäsi versio voi poiketa artikkelin kuvista.

Tiedon 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

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

Loput välilehden toiminnot liittyvät työkirjan ja ylipäätään erilaisten tietolähteiden asetuksiin sekä ohjeisiin.

Tuontitoiminnallisuudet Excel 2016, 2019 ja O365 -versioissa

Tuontikomennot on koottu Data (Tiedot) -välilehdelle yhdeksi ryhmäksi. Versiosta riippuen kyselyn luonti aloitetaan New Query tai Get Data -nimisellä painikkeella. Lisäksi Data-välilehdeltä voi avata kyselypaneelin (Show Queries tai Queries & Connections versiosta riippuen) sekä ryhtyä tekemään kyselyä taulukoksi muunnetusta alueesta (From Table).

Tuontitoiminnallisuudet Power BI Desktopissa

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

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 tai Get Data > 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.
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.
   
7. Power Query (kyselyeditori) käynnistyy.

Huomaa Power BI Desktopissa: Power Query -ikkuna saattaa jäädä piiloon Power BI Desktop -ikkunan taakse, kun olet painanut Edit-painiketta. Siirry tällöin itse Power Query -ikkunaan.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.

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 Power Query -ikkunassa joko valintanauhan eri välilehdiltä tai sarakeotsikoiden päältä avautuvista pikavalikoista tai suodatusnuolten avulla. Kuva on Excelin Power Querystä, joten se poikkeaa väritykseltään Power BI Desktopista.

(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. 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). Power Query siis nauhoittaa suorittamiasi toimenpiteitä ja toimenpiteet suoritetaan nauhoitusjärjestyksessä, kun päivität kyselyt.

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.
3. Jos kuntaluettelon otsikot näkyvät kahdesti (sekä otsikkoina että ensimmäisenä tietueena), käytä esimerkiksi jossakin sarakeotsikossa olevaa suodatusnuolta ja suodata ylimääräinen otsikkorivi pois datasta.
4. 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ä.

   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.
   

5. 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.
6. Poista pikavalikon avulla turha Vaakuna-sarake ja kiinnitä huomiota, että poisto lisää uuden vaiheen askelten joukkoon.
7. Joidenkin sarakeotsikoiden kohdalla on indeksinumeroita hakasulkeiden sisällä. Lisäksi Kuntanro -sarakkeessa on tavuviiva. Siisti otsikot kaksoisnapsauttamalla niitä ja kirjoittamalla tilalle haluamasi tekstit.
8. 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.
   Vihje: 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).

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.

• 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ää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.

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 ->