Todellisista käytännön haasteista oppii aina eniten, joten jaan teille eräältä analyytikolta saapuneen kysymyksen sekä sen ratkaisun. Jälleen kerran Power Query hoiti hienosti homman.
Power Query -taikoja
Avainsana-arkisto: Power Query Opas
Ravitsemustietokanta Fineli Excelissä
Terveyden ja hyvinvoinnin laitoksen ravitsemustietokanta Fineli julkaistiin viime viikolla uudistuneena ja tietokantaan lisättiin useiden uusien elintarvikkeiden tietoja. Koska tiedot ovat avoimesti saatavilla, latasin datan testimielessä Excelin Power Pivot -tietomalliin.
Power BI: Kenen palavereissa istut?
Kenen palavereissa vietät eniten työaikaasi? Miten monta kokouspyyntöä olet itse lähettänyt? Entä miten suuri osa palavereistasi on online-kokouksia?
Kirjoitin ohjeet, joiden avulla voit ladata Outlookin kalenterimerkinnät analysoitavaksi Power BI Desktopiin tai Exceliin. Ohjeita soveltamalla pystyt analysoimaan myös sähköpostiviestejäsi tai muita Outlookin kohteita.
Power Query: Tiedon täyttäminen, jakaminen, yhdistäminen ja muuntaminen
| <- Kyselyoppaan sisällysluettelo | |
| <- Edellinen juttu |
Kirjoitin aiemmin ohjeita sekä rivien ja sarakkeiden peruskäsittelystä että niiden kääntämisestä, joten tässä jutussa on muutamia muita hyödyllisiä muokkaustoimintoja.
Power Query: Rivien ja sarakkeiden peruskäsittely
| <- Kyselyoppaan sisällysluettelo | |
| <- Edellinen juttu | Seuraava juttu -> |
Kyselyeditorin Home (Aloitus) –välilehdellä on runsaasti rivien ja sarakkeiden peruskäsittelyyn liittyviä toimintoja. Monet toiminnoista löytyvät myös pikavalikoista. Jos jokin tarvitsemasi komento puuttuu pikavalikosta tai se on himmennettynä valintanauhassa, kyseisen sarakkeen tietotyyppi voi estää toiminnon käytön sarakkeessa. Muunna tarvittaessa tietotyyppi toiseksi.
Lue loppuun
Power Query: Datan lataaminen Power Pivotiin
| <- Kyselyoppaan sisällysluettelo | |
| <- Edellinen juttu | Seuraava juttu -> |
Tämä kyselyoppaan juttu liittyy poikkeuksellisesti vain Excelin Power Queryyn, ei Power BI Desktop -ohjelmaan.
Ohjeessa oletetaan, että sinulla on kokemusta Power Pivotista ja olet ladannut tai linkittänyt tietoja tietomalliin Power Pivotin omilla komennoilla.
Jutussa kerrotaan miten suoritat latauksen Power Queryllä. Lisäksi artikkelissa annetaan hyviä ohjeita, miten vältät Power Queryn ja Power Pivotin yhteiskäyttöön liittyvät ongelmat.
Lue loppuun
Power Query: Kyselyyn viittaaminen Reference-toiminnolla
| <- Kyselyoppaan sisällysluettelo | |
| <- Edellinen kyselyoppaan juttu | Seuraava kyselyoppaan juttu -> |
Kyselyssä voi viitata (reference) toiseen kyselyyn eli käyttää hyödyksi jo olemassa olevaa kyselyä ja jatkaa sen lopputuloksen jalostamista edelleen. Ominaisuudesta on hyötyä esimerkiksi tilanteissa, joissa samasta aineistosta pitää muodostaa useita erilaisia osajoukkoja.
[Edit 10/2016: Väline kehittyy joten tämä teksti on tarkistettu ja korvattu lokakuussa 2016.]
Lue loppuun
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 09/2018: Väline kehittyy joten tämä teksti on tarkistettu ja muokattu syyskuussa 2018.]
| Ohjelma ja versio | Nimi |
| Excel 2010 | Power Query |
| Excel 2013 | Power Query |
| Excel 2016, 2019, 365 | Query Editor (Kyselyeditori) ja Power Query Editor |
| Power BI Desktop | Power Query |
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 tuontia on voinut 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ä.
Power Query pitää erikseen ladata ja asentaa Excel 2010 ja 2013 -versioihin. Uudemmissa versioissa se on sisäänrakennettu ominaisuus, joka löytyy Data (Tiedot) -välilehdeltä.
Excelin lisäksi dataa voi mallintaa ja havainnollistaa kesällä 2015 julkistetulla ilmaisella Power BI Desktop -ohjelmalla, jossa Power Query on myös sisäänrakennettuna ominaisuutena.
Power Query & Power BI Kyselyopas Excel-käyttäjälle
Power Query & Power BI Kyselyopas on tarkoitettu suomalaiselle Excel tai Power BI Desktop -käyttäjälle kyselyiden ja muokkausoperaatioiden perusteiden opiskeluun. Opas täydentyy vuoden 2015 aikana.
Oppaassa neuvotaan miten kyselyeditoria käytetään tiedon etsimiseen, lataamiseen, muokkaamiseen ja muuntamiseen olipa kyseessä julkinen avoin data tai organisaation sisäisissä tietokannoissa oleva data. Oppaassa tulee olemaan lukuisia käytännön esimerkkejä ja valmiita ladattavia esimerkkityökirjoja kyselyineen, joissa tietolähteinä mm. tekstitiedostoja, Excel-tiedostoja, tietokantoja, nettisivuilta linkitettyä dataa, jne.
Suuri osa oppaan kuvista on otettu Excelin Power Query -apuohjelmasta, joten ne eroavat värimaailmaltaan Power BI Desktopin kyselyeditorin ulkoasusta. Lisätietoja Power BI -palvelusta ja Power BI Desktop -ohjelmasta: https://hexcelligent.fi/power-bi/
Power Query & Power BI kyselyopas
01 Power Query: Juttusarjan johdanto
- Miksi?
- Hieman ”hyvä tietää” -historiaa ja taustatietoja
- Power Query Excelissä
- Power BI Desktop -ohjelman kyselyt
- Power Queryn toimintaperiaate
- ETL ja SSIS?
02 Power Query: Käyttöönotto ja päivittäminen
- Työaseman teknisen vaatimukset
- Excel-lisenssin ja bittisyyden tarkistaminen sekä 2010 että 2013 -versioissa
- Power Queryn lataus ja asennus
- Power Query välilehden esiin tuonti
- Power Queryn päivittäminen
- Kehitysehdotusten lähettäminen Power Query -tiimille
03 Power Query: Käyttöliittymä ja perusperiaatteet
- Power Query välilehti Excel 2010 ja 2013 -versioissa
- Tuontitoiminnallisuudet Excel 2016 -versiossa
- Kyselyn luonti (esimerkkinä Suomen kuntien nouto Wikipediasta)
- Kyselyeditorissa työskentely
- Kyselyn vaiheet
- Tietotyypit
- Kyselyn tulos Exceliin
- Kyselyn päivittäminen ja muokkaaminen
04 Power Query: Kyselyiden yhdistäminen Merge-toiminnolla
- Mergen toimintaperiaate
- Erot VLOOKUP (PHAKU) -yhdistämiseen verrattuna
- Ohje: yhdistäminen
- Yhdistävän kyselyn päivitys
- Kokeile yhdistämistä itse
- Suomen kunnat sekä niiden koordinaatit
05 Power Query: Kyselyiden liittäminen peräkkäin Append-toiminnolla
- Appendin toimintaperiaate
- Ohje: peräkkäin liittäminen
- Liittävän kyselyn päivitys
- Ohje: Suomen Palloliiton Lohko A – D kyselyiden luonti
- Ensimmäisen kyselyn luonti
- Muiden kyselyiden monistaminen ja muokkaus
- Kokeile
- Otteluluettelot
06 Power Query: Kyselyyn viittaaminen Reference-toiminnolla
- Referencen toimintaperiaate
- Ohje: Aiempaan kyselyyn viittaaminen
- Viittaavan kyselyn päivitys
- Kokeile
- ensimmäisen kyselyn muokkaus
07 Power Query: Datan lataaminen Power Pivotiin
- Power Pivotin ja Power Queryn yhteensopivuus
- 5 ohjetta ongelmien välttämiseen
- Miksi käyttäisit Power Queryä lataamisessa?
- Lataaminen, päivitys ja muokkaaminen eri versioissa (2010, 2013 ja 2016)
- Lisätietoja
08 Power Query: Rivien ja sarakkeiden peruskäsittely
- Tietotyypit
- Otsikoiden määrittäminen
- Rivien ja sarakkeiden valitseminen
- Rivien suodattaminen
09 Power Query: Rivien ja sarakkeiden kääntäminen
- Usean sarakkeen kääntäminen kahdeksi sarakkeeksi – unpivot
- Useiden sarakkeiden luonti kahden sarakkeen perusteella – pivot
- Pivot-kääntäminen ilman numeerista arvo-saraketta
- Rivit sarakkeiksi ja päinvastoin – transpose
10 Power Query: Tiedon täyttäminen, jakaminen, yhdistäminen ja muuntaminen
- Tiedon täyttäminen
- Sarakkeen monistaminen
- Sarakkeen sisällön jakaminen
- Sarakkeiden yhdistäminen
- Merkkien korvaaminen
- Merkkijonojen muuntaminen
- Lukujen muuntaminen
- Päivämäärien muuntaminen
Erilaisia esimerkkejä
Tulossa mm.:
Power Query: Mukautetut kaava-sarakkeet
- oleellisimpia funktiota
- M-funktiot kaavoissa
Power Query: Virheiden hallinta
Power Query: Parametrien välittäminen
- parametritaulu
- parametrien lukeminen muuttujiin
HExcelligent.fi 