Mitä yleisemmäksi itsepalveluraportointityökaluksi Power BI on tullut, sitä enemmän olen saanut avunpyyntöjä käyttäjiltä, joille Excel on tuttu ja jotka ovat nyt ryhtyneet laatimaan visualisointeja myös Power BI:llä.

Koska valtaosa ongelmista on liittynyt lähdedatan rakenteeseen, kerron tässä jutussa millaisista datatauluista Power BI eniten tykkää ja miten ”epäsopivan” datan saa helposti muokattua sopivaan muotoon.

Tietolähteenä määrämuotoinen datataulu vai ”exportattu” raportti?

Datataulut ovat pääsääntöisesti sopivassa muodossa sellaisenaan, jos niitä luetaan suoraan tietovarastoista, kuten Data Warehouse –ratkaisuista, tietokannoista tai käyttämällä erilaisia Power BI:ssä olevia muita määrämuotoisia tietolähteitä.

Jos aineisto puolestaan muodostetaan jossakin lähdejärjestelmässä (esim. SAP) olevalla export-komennolla ja yritetään käyttää muodostunutta Excel- tai .csv -tiedostoa tietolähteenä, data mitä todennäköisimmin vaatii muokkausta eikä sovellu raportointiin sellaisenaan. Myös monet avoimen datan tietolähteet edellyttävät ylimääräistä puuhastelua.

Muokkaustarve johtuu siitä, että export-komennon tuottamat lopputulokset ovat usein muodoltaan raportteja, eivät dataa.

Data soveltuu raportointiin, mutta jo kertaalleen jatkojalostetut raportit eivät välttämättä kovin hyvin!

Käytän alla olevissa esimerkeissä mahdollisimman yksinkertaista henkilöstöaineistoa, enkä ota kantaa useista eri tauluista muodostuvan kokonaisuuden rakenteeseen. Laajempien tietomallien rakentaminen jääköön tämän jutun ulkopuolelle.

Raportointiin soveltuva datataulu

Tutustutaanpa ensin millaista datan tulisi olla.

Raportointiin soveltuvan aineiston kussakin sarakkeessa on tietoa kuvaileva ominaisuus, kuten työntekijän tyyppi, toimipaikka, vuosi, sukupuoli tms. Kuvailevien sarakkeiden lisäksi taulussa on yksi tai useita erilaisia numeerisia tietoja, kuten lukumäärä, euromäärä, %-osuus, järjestysnumero, tms.

(Kuvailevat sarakkeet voivat sisältää myös ID-tunnuksia, kuten työntekijätyypin tunnus, toimipaikan ID, jne. jos kyseessä on useista eri tauluista muodostuva kokonaisuus.)

Jos kyseessä olisi Excel-luettelo, pystyisit suodattamaan sitä ja voisit tehdä siitä helposti pivot-taulukoita.

Tällainen aineisto soveltuu loistavasti myös Power BI:n lähdedataksi ja mitä enemmän siinä on kuvailevia sarakkeita, sitä monipuolisempia raportteja pystyt tekemään: esimerkiksi sukupuoli, ikä, vuosi, rooli (työntekijä vai esimies), kustannuspaikka, jne.

Raportointiin epäsopiva aineisto

Seuraavassa on kuvattu millaisten aineistojen kanssa päädyt jossain vaiheessa umpikujaan.

Export-komennot tuottavat tyypillisesti taulukoita eli raportteja, joissa yksi tai useampia kuvailevista ominaisuuksista (kuten toimipaikka ja/tai esimerkiksi vuosi) on käännetty eli pivotoitu vaakasuuntaisiksi otsikkoriveiksi. Raportti on siis matriisi, josta on helppo lukea yksittäisiä lukuja kuten ”Vakituisten kokoaikaisten lukumäärä Helsingissä”.

Raporttia on helppo lukea, mutta se ei valitettavasti sovellu Power BI:n eikä muidenkaan raportointivälineiden lähdedataksi.

Excelissäkään et pystyisi käyttämään suodatusta tietyn toimipaikan tietojen tarkasteluun, vaan sinun pitäisi ryhtyä piilottamaan turhia sarakkeita muilla komennoilla. Pivot-taulukoiden luonti olisi myös hankalaa, sillä et pystyisi valitsemaan toimipaikkaa yhdeksi pivot-taulukon suodattimeksi tai osittajaksi, koska toimipiste ei ole yhdessä sarakkeessa.  Kyseessähän on jo jatkojalostettu eli kertaalleen pivotoitu aineisto.

Mikä avuksi?

Selvitä aina ensin, saisitko datan lähdejärjestelmästä fiksummin datataulun muotoisena luettelona! Kysy, se saattaa olla mahdollista.

Jos et saa dataa halutussa muodossa, sinun pitää suorittaa datan kääntäminen eli poistaa aineiston ns. pivotointi.

Onneksi pivotoidun raportin kääntäminen onnistuu Power BI Desktopissa melko vaivattomasti!

Raportin kääntäminen datatauluksi

Tämän ohjeen ei ole tarkoitus opettaa Power BI Desktopin perusteita, eli oletan että kyselyeditorin toimintaperiaate on tuttu. Kerron tässä ohjeessa ainoastaan, miten aineisto käännetään.

Kun olet valinnut tietolähteen (esim. Excel-työkirjan taulukon) ja siirtynyt Power BI Desktopin kyselyeditoriin, huolehdi ensin siitä, että poistat mahdolliset turhat rivit aineiston yläpuolelta ja nostat vaakasuuntaisesti pivotoidun ominaisuuden (esim. toimipaikka) otsikkoriviksi (Use first row as headers, Käytä ensimmäistä riviä otsikkoina). Poista myös kaikki turhat sarakkeet.

Valitse tämän jälkeen kaikki ne kuvailevat sarakkeet, jotka ovat jo kunnossa ja anna pikavalikosta komento Unpivot other columns (Poista muiden sarakkeiden pivot-toiminto).

Komento kääntää muut kuin valitut sarakkeet kahdeksi sarakkeeksi, joista ensimmäiseen tulee otsikkorivillä olleet toimipaikat ja toiseen niissä olleet luvut. Samalla aineiston rivimäärä moninkertaistuu.

Voit tämän jälkeen vaihtaa sarakeotsikot, määrittää numeerisen sarakkeen tietotyypin ja jatkaa datan muokkausta ennen kuin lataat datataulun Power BI Desktopin tietomalliin ja ryhdyt tekemään siitä raportteja.

Vaakasuunnassa hierarkisen raportin kääntäminen

Entäpäs jos otsikoiksi on pivotoitu lähdejärjestelmässä kaksi tai useampia erilaisia kuvailevia tietoja, kuten vuosi ja toimipiste?

Sarakeotsikkohierarkian käsittely edellyttää hieman esivalmistelua jo lähdedatassa. Lisää lähdedatan yläpuolelle ylimääräinen rivi, johon muodostat otsikkorivin siten, että ylemmän (kuvassa vuosi) ja alemman tason (kuvassa toimipaikka) otsikoiden välillä on jokin erikoismerkki (kuvassa putkimerkki | ). Jos otsikkotasoja on useampia, käytä jokaisen tason välillä samaa erotinmerkkiä.

Kun olet Power BI Desktopin kyselyeditorissa käsittelemässä aineistoa, huolehdi että kyseinen uusi otsikkorivi näkyy kyselyeditorissa otsikkorivinä, poista alkuperäiset otsikot Remove Rows > Remove Top Rows (Poista rivit > Poista ylimmät rivit) –toiminnolla, valitse kaikki ne sarakkeet jotka ovat jo kunnossa ja suorita jälleen Unpivot other columns (Poista muiden sarakkeiden pivot-toiminto).

Muut sarakkeet kääntyvät jälleen kahdeksi sarakkeeksi, joista ensimmäisessä näkyy muodostamasi hierarkia erikoismerkkeineen ja toisessa luvut. Voit nyt jakaa sarakkeen käyttämäsi erottimen mukaan useaksi sarakkeeksi ja jatkaa kyselyeditorissa datan muokkausta, kuten tietotyyppien määritystä.

Valmis  taulu:

Pystysuunnassa hierarkisen raportin käsittely

Entäpä jos riviotsikot on ryhmitelty hierarkisesti, kuten työntekijätyypit toimipisteittäin?

Voit täyttää sarakkeessa olevat ylätason otsikot Power BI Desktopin kyselyeditorissa pikavalikon Fill > Down (Täytä > Alas) -toiminnolla. Toiminto täyttää tiedot alapuolella oleviin null-soluihin. Tarvittaessa soluihin pitää Replace values (Korvaa arvot) -komennolla muodostaa null-arvot, jos niissä ei ole sellaisia.

Summa summarum

Paras ja helpoin vaihtoehto on se, että data on valmiiksi oikeassa muodossa.

Power BI Desktopilla pystyy kuitenkin tekemään taikoja ja hankalatkin aineistot on mahdollista saada raportoinnin kannalta sopivaan muotoon!

Huom! Monet esimerkiksi henkilöstökyselyissä käytetyt työkalut (kuten Digium tai Survey Monkey) antavat tulosdatan sellaisessa muodossa, että jokainen kysymys on omassa sarakkeessaan ja kysymykset pitää ensin kääntää (unpivotoida) datatauluksi ennen raportointia.

Huomaa myös, että erilaiset raporttiin lasketut välisummat (kuten työntekijämäärä toimipisteittäin) ovat yleensä täysin turhia ja jopa aiheuttavat ongelmia visualisointivaiheessa. Suodata ne siis pois datasta.