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.

 Haaste oli seuraavanlainen:

Hei Heidi,

olit viime vuoden lopulla pitämässä meillä Power Query kurssia. Power Query on helpottanut ja nopeuttanut työtä huomattavasti, aivan kuten lupasit.

Nyt olen kuitenkin törmännyt ongelmaan, jota en ole kyennyt ratkaisemaan. Liitteenä yksinkertaistettu esimerkki ongelmasta.

Eli siis:

Pystyykö Power Queryssä lisäämään sarakkeen, johon laskettaisiin summa niistä sarakkeista, joiden otsikot löytyvät määritellystä listasta.

Minulla on kuukausittain saatava csv-tiedosto, jossa on joka kuukausi hieman eri määrä sarakkeita.

Kuvitellaan että tiedostossa on päiväkohtaista dataa eläimistäni. Csv-tiedostoon tulee mukaan ainoastaan ne eläimet, joihin liittyy tarkasteltavana kuukautena jotain dataa.

Jos eläin x:stä ei ole mitään dataa k.o. kuukautena, sitä ei tule mukaan csv-tiedostoon.

Minulla on kuitenkin lista kaikista eläimistäni jaoteltuna eri eläinryhmiin (esimerkissä nisäkkäät, linnut sekä kalat).

Tahtoisin tehdä kyselyn, joka summaisi minulle päiväkohtaiset summat eri eläinryhmille, jotka minulla on listattuna.

Kysely lisäisi sarakkeet joka eläinryhmälle ja summaisi ne eläimet, jotka kuuluvat kyseiseen eläinryhmään.

Tulokseksi halutaan siis jotakin alla olevan kaltaista:

Ongelman ratkaisu

Lähdedatan rakenteessa esiintyy eräs hyvin tyypillinen ongelma: yksi datan ominaisuuksista (eläin) on käännetty (pivotoitu) csv-tiedostossa eri sarakkeisiin. Jotta dataa pystyisi fiksusti luokittelemaan ja jatkokäsittelemään, aineistossa tulisi olla kolme saraketta: Date, Animal ja Value.

Ratkaisu siis perustuu siihen, että ensimmäiseksi datan kaikki muut sarakkeet paitsi Date pitää kääntää kahdeksi sarakkeeksi (Animal ja Value) eli aineistolle suoritetaan ns. unpivot-operaatio. Tämän jälkeen aineistoon voi yhdistää Merge-toiminnolla kunkin eläimen eläinryhmä ja lopuksi laskea eläinryhmien summat. Summat voisi laskea datasta pivot-taulukon avulla, mutta kuvailen tässä esimerkissä ratkaisun, jossa kaikki tehdään loppuun asti Power Queryllä.

Esimerkkikyselyt löytyvät tästä tiedostosta. Käytin ratkaisussani .csv-tiedoston sijaan yhdessä Excel-tiedostossa olevia Excelin taulukoita (table), jotta sain koko ratkaisun yhteen tiedostoon.

Jätän näissä ohjeissa kaikki ylimääräiset hienosäätövaiheet tekemättä, kuten tietotyyppien määritykset päivämäärien osalta yms. Ratkaisussa keskitytään vain datan kääntämiseen ja yhdistämiseen. Huomio: Kaikissa kuvissa on väärät päivämäärät eli olin kadottanut etunollat jo lähdedatoja muodostaessani.  😉

Lähdedatat

Tiedostossa on seuraavat lähdetaulukot (SourceData ja SourceAnimalGroups):

Apukyselyiden luonnit (Data ja AnimalGroups)

Ensin molemmista lähdedatoista muodostetaan kyselyt, jotka myöhemmin yhdistetään yhdeksi tulokseksi. Power Queryhän edellyttää, että kaikki yhdistettävät aineistot ovat kyselyitä. Pelkät taulukot eivät riitä.

1. Muodosta ensin esimerkki SourceAnimalGroups-taulukosta kysely valitsemalla yksi solu taulukon sisältä ja käyttämällä komentoa
   Excel 2016: Data > Get & Transform > From Table
   Excel 2010 tai 2013: Power Query > Excel Data > From Table
2. Eläinryhmädatalle ei tarvitse tehdä mitään eikä sitä edes tarvitse ladata tiedostoon. Anna kyselylle nimeksi esimerkiksi AnimalGroups ja avaa kyselyeditorissa Home-välilehdellä Close & Load -painikkeen valikko ja valitse Close & Load to … -ikkunasta vaihtoehto Only Create Connection.
3. Valitse seuraavaksi yksi solu SourceData-aineiston sisältä ja aloita jälleen kyselyn luonti From Table -komennolla.
4. Valitse Date-sarake ja käännä muut sarakkeet pikavalikosta Unpivot Other Columns -komennolla. Unpivot Other Columns on loistava komento, sillä Power Queryn käännösskriptiin tallentuu vain viittaus Date-sarakkeeseen eli kaikki eläimet kääntyvät olipa niitä datassa 5 tai 10. Älä siis valitse eläinsarakkeita ja Unpivot Columns -komentoa, joka edellyttäisi, että datassa on aina samat eläimet!
5. Tuloksen pitäisi näyttää tältä:
6. Anna kyselylle nimeksi Data ja sulje kyselyeditori jälleen lataamatta tulosta Exceliin (Only Creata Connection).

Apukyselyiden yhdistäminen

Sinulla on nyt kaksi kyselyä, joista toinen sisältää datan ja toinen eläinryhmät. Suoritetaan seuraavaksi kyselyiden yhdistäminen.

1. Valitse Excelissä data-kyselyn päältä Merge.
2. Valitse yhdistettäväksi kyselyksi AnimalGroups ja valitse myös molemmista Animal-sarakkeet, jotka toimivat tässä tapauksessa yhdistävinä kenttinä ja hyväksy OK:lla.
   
3. Laajenna esiin Group -sarake. Poista rasti kohdasta Use original column name as prefix, jottei sarakkeen nimen eteen tule NewColumn-etuliitettä.
   
4. Kun eläinryhmät (group) on yhdistetty data-aineistoon, tuloksen pitäisi näyttää tältä:
   Edellä olevan tuloksen voisi jo palauttaa Exceliin ja jatkaa eläinryhmien summaamista esimerkiksi pivot-taulukon avulla. Tässä esimerkissä haluan kuitenkin näyttää, että Power Queryllä pystyy myös laskemaan ryhmäsummia ja palauttamaan valmiin lopputuloksen summineen.
5. Seuraavaksi valitaan komento Transform > Group By ja lasketaan summat ryhmien ja päivien perusteella. Group By -ikkunassa tehdään seuraavat valinnat:
   
   Koska ryhmityksissä ei ole mukana Animal-saraketta, se tulee katoamaan tulosjoukosta:
   
6. Tämän jälkeen aineiston voi vielä kääntää (pivotoida) siten, että Group-sarakkeesta muodostuu sarakeotsikot valitsemalla ensin Group-sarakkeen ja sen jälkeen Total-sarakkeen ja antamalla komennon Transform > Pivot column. (Huomaa, että pivotoitaessa on tärkeää valita ensin sarake, josta muodostuu sarakeotsikot ja sen jälkeen ctrl-näppäimellä sarake, joka sisältää sarakkeisiin muodostuvat luvut.)
   
7. Lopputulos: