Opetusohjelma: Tietojen muotoilu ja yhdistäminen Power BI Desktopissa

Power BI Desktopilla voit muodostaa yhteyksiä moniin erilaisiin tietolähteisiin ja muotoilla sitten tietoja tarpeidesi mukaisesti. Näin voit luoda visuaalisia raportteja, joita voit jakaa muille. Tietojen muotoileminen tarkoittaa tietojen muuntamista: sarakkeiden tai taulukoiden nimeämistä uudelleen, tekstin muuntamista luvuiksi, rivien poistamista, ensimmäisen rivin määrittämistä otsikoiksi ja niin edelleen. Tietojen yhdistäminen tarkoittaa kahden tai useamman tietolähteen yhdistämistä, tietojen muotoilua tarvittaessa ja sitten tietojen yhdistämistä hyödylliseksi kyselyksi.

Tässä opetusohjelmassa opit:

  • Tietojen muotoilu Power Query -editori avulla.
  • yhdistämään eri tietolähteisiin
  • yhdistämään kyseiset tietolähteet ja luomaan tietomallin raportissasi käytettäviksi.

Tässä opetusohjelmassa näytetään, miten voit muotoilla kyselyä Power BI Desktopilla, sekä tuodaan esiin yleisimpiä tehtäviä. Tässä käytetty kysely kuvataan tarkemmin ohjeartikkelissa Power BI Desktopin käytön aloittaminen, josta saat myös ohjeet kyselyn luomiseen kokonaan itse.

Power Query -editori in Power BI Desktop hyödyntää runsaasti hiiren kakkospainikkeella käytettäviä valikoita sekä Muunna-valintanauhaa. Useimmat toiminnot, jotka ovat valittavissa valintanauhassa, ovat käytettävissä myös napsauttamalla kohdetta, esimerkiksi saraketta, hiiren kakkospainikkeella ja valitsemalla sitten toiminto näyttöön avautuvasta valikosta.

Tietojen muotoileminen

Kun muotoilet tietoja Power Query -editori, annat vaiheittaiset ohjeet, jotka Power Query -editori suorittaa puolestasi tietojen muokkaamiseksi ja esittämiseksi. Tämä ei vaikuta alkuperäiseen tietolähteeseen, sillä vain tätä tietonäkymää muokataan eli muotoillaan.

määrittämäsi vaiheet (kuten taulukon nimeäminen uudelleen, tietotyypin muuntaminen tai sarakkeen poistaminen) kirjataan Power Query -editori. Aina, kun tämä kysely muodostaa yhteyden tietolähteeseen, Power Query -editori suorittaa nämä vaiheet niin, että tiedot muotoillaan aina määrittämälläsi tavalla. Tämä prosessi suoritetaan aina, kun käytät Power Query -editori tai kun joku käyttää jaettua kyselyäsi esimerkiksi Power BI -palvelu. Vaiheet tallennetaan järjestyksessä Kyselyn Asetukset -ruudun Käytössä olevat vaiheet -kohtaan. Käymme läpi jokaisen näistä vaiheista seuraavissa kappaleissa.

Applied steps in Query Settings

Käytämme tässä esimerkissä Power BI Desktopin käytön aloittaminen -artikkelista tuttuja eläköitymistietoja, jotka löysimme muodostamalla yhteyden verkkotietolähteeseen. Muotoilemme tässä esimerkissä näitä tietoja omiin tarpeisiimme. Lisäämme mukautetun sarakkeen, jolla lasketaan sijoitus sillä perusteella, että kaikki tiedot ovat yhtä tärkeitä, ja verrataan tätä saraketta olemassa olevaan Rank-sarakkeeseen.

  1. Valitse Lisää sarake-valintanauhasta Mukautettu sarake, jonka avulla voit lisätä mukautetun sarakkeen.

    Select Custom Column

  2. Kirjoita Mukautettu sarake -ikkunan Uuden sarakkeen nimi -kohtaan New Rank. Lisää Mukautettu sarakekaava -kohtaan seuraavat tiedot:

    ([Cost of living] + [Weather] + [Health care quality] + [Crime] + [Tax] + [Culture] + [Senior] + [#"Well-being"]) / 8
    
  3. Varmista, että tilailmoitus on Syntaksivirheitä ei ole havaittu, ja valitse OK.

    Custom Column page with no snytanx errors

  4. Jotta sarakkeiden tiedot pysyvät johdonmukaisena, muunna uudet sarakearvot kokonaisluvuiksi. Voit vaihtaa arvot napsauttamalla sarakeotsikkoa hiiren kakkospainikkeella ja valitsemalla sitten Muuta tyyppi > Kokonaisluku.

    Jos haluat valita useita sarakkeita, valitse sarake, paina VAIHTO-näppäin pohjaan, valitse vierekkäisiä sarakkeita ja napsauta sitten sarakeotsikkoa hiiren kakkospainikkeella. Painamalla CTRL-näppäintä valitessasi voit valita kerralla useita sarakkeita, jotka eivät ole vierekkäin.

    Select Whole Number column data

  5. Jos haluat muuntaa sarakkeen tietotyyppejä, eli muuntaa nykyisen tietotyypin toiseksi, valitse Tietotyyppi tekstiMuunna-valintanauhasta.

    Select Data Type Text

  6. Kaikki tietojen muotoiluvaiheet kirjataan Kyselyasetukset-ruudun Käytössä olevat vaiheet -luetteloon. Jos haluat poistaa vaiheen muotoiluprosessista, valitse haluamasi vaiheen vasemmalta puolelta X.

    Seuraavassa kuvassa Käytössä olevat vaiheet-luettelo kuvastaa tähän mennessä lisättyjä vaiheita:

    • Lähde: Sivustoon yhdistäminen.

    • Poimittu taulukko HTML:stä: Taulukon valitseminen.

    • Muutettu tyyppi: Tekstipohjaisten lukusarakkeiden muuttaminen tekstistäkokonaisluvuksi.

    • Lisätty mukautettu: mukautetun sarakkeen lisääminen.

    • Muutettu tyyppi1: Viimeisin käytetty vaihe.

      List of Applied Steps

Säädä tietoja

Ennen kuin voimme käyttää tätä kyselyä, meidän täytyy säätää sen tietoja tekemällä muutama muutos:

  • Säädä sijoituksia poistamalla sarake.

    Olemme päättäneet, sarake Cost of living ei vaikuta tuloksiimme. Kun tämä sarake on poistettu, tiedot pysyvät muuttumattomina.

  • Korjaa muutama virhe.

    Koska poistimme sarakkeen, meidän täytyy muokata New Rank -sarakkeen laskelmia ja muuttaa kaavaa.

  • Lajittele tiedot.

    Tiedot lajitellaan New Rank- ja Rank-sarakkeiden perusteella.

  • Korvaa tiedot.

    Näytämme, miten voit korvata tietyn arvon ja miksi käytössä oleva vaihe täytyy lisätä.

  • Muuta taulukon nimi.

    Koska Table 0 ei kuvaa taulukkoa hyödyllisellä tavalla, muutamme sen nimen.

  1. Jos haluat poistaa Cost of living -sarakkeen, valitse sarake, valitse valintanauhan Aloitus-välilehti ja valitse sitten Poista sarakkeet.

    Select Remove Columns

    Huomaat, että New Rank -arvot eivät ole muuttuneet. Tämä johtuu vaiheiden järjestyksestä. Koska Power Query -editori kirjaa vaiheet järjestyksessä ja silti toisistaan riippumatta, voit siirtää kutakin käytössä olevaa vaihetta järjestyksessä ylös- tai alaspäin.

  2. Napsauta vaihetta hiiren kakkospainikkeella. Power Query -editori sisältää valikon, jossa voit tehdä seuraavat tehtävät:

    • Nimeä uudelleen; nimeä vaihe uudelleen.
    • Poista: Poista vaihe.
    • DeleteUntil End: Poista nykyinen vaihe ja kaikki myöhemmät vaiheet.
    • Siirrä ennen: Siirrä vaihetta ylöspäin luettelossa.
    • Siirry jälkeen: Siirrä vaihetta alaspäin luettelossa.
  3. Siirrä viimeinen vaihe eli Removed ColumnsAdded Custom -vaiheen yläpuolelle.

    Move up step in Applied Steps

  4. Valitse Added Custom -vaihe.

    Huomaat, että tiedoissa näkyy nyt virhe, joka meidän täytyy korjata.

    Error result in column data

    Voit hankkia lisätietoa eri virheistä muutamin tavoin. Jos valitset solun napsauttamatta Virhe-sanaa, Power Query -editori näyttää virhetiedot.

    Error information in Power Query Editor

    Jos valitset Virhe-sanan suoraan, Power Query -editori luo käytössä olevan vaiheenKyselyn Asetukset -ruutuun ja näyttää tietoja virheestä.

  5. Koska meidän ei tarvitse näyttää tietoja virheistä, valitse Peruuta.

  6. Jos haluat korjata virheet, valitse New Rank -sarake ja näytä sarakkeen tiedot valitsemalla Kaavarivi-valintaruutu Näytä-valintanauhasta.

    Select Formula Bar

  7. Poista Cost of living -parametri ja pienennä jakaja muuttamalla kaava seuraavaan muotoon:

     Table.AddColumn(#"Removed Columns", "New Rank", each ([Weather] + [Health care quality] + [Crime] + [Tax] + [Culture] + [Senior] + [#"Well-being"]) / 7)
    
  8. Valitse kaavaruudun vasemmalla puolella oleva vihreä valintamerkki tai valitse Enter.

Power Query -editori korvaa tiedot tarkistetuilla arvoilla, ja Added Custom -vaihe valmistuu ilman virheitä.

Huomautus

Voit myös valita Poistaa virheet käyttämällä valintanauhaa tai hiiren kakkospainiketta. Tämä poistaa kaikki rivit, joilla on virheitä. Emme kuitenkaan halunneet tehdä näin tässä opetusohjelmassa, koska halusimme säilyttää taulukon tiedot.

  1. Lajittele tiedot New Rank -sarakkeen perusteella. Valitse ensin viimeisin käytetty vaihe eli Changed Type1 näyttääksesi uusimmat tiedot. Valitse sitten avattava valikko New Rank -sarakeotsikon vierestä ja valitse Lajittele nousevaan järjestykseen.

    Sort data in New Rank column

    Tiedot lajitellaan nyt New Rank -sarakkeen perusteella. Jos taas katsot Rank-saraketta, näet, että tietoja ei lajitella oikein kohdissa, joissa New Rank -arvo on tasapeli. Korjaamme tämän seuraavassa vaiheessa.

  2. Voit korjata tietojen lajitteluongelman valitsemalla New Rank -sarakkeen ja vaihtamalla sitten kaavarivin kaavan seuraavaan muotoon:

     = Table.Sort(#"Changed Type1",{{"New Rank", Order.Ascending},{"Rank", Order.Ascending}})
    
  3. Valitse kaavaruudun vasemmalla puolella oleva vihreä valintamerkki tai valitse Enter.

    Rivit on nyt järjestetty sekä New Rank- että Rank-sarakkeen mukaisesti. Voit myös valita käytössä olevan vaiheen luettelon mistä tahansa kohdasta ja jatkaa tietojen muotoilua järjestyksen tässä kohdassa. Power Query -editori lisää automaattisesti uuden vaiheen tällä hetkellä valitun käytössä olevan vaiheen perään.

  4. Valitse Käytössä oleva vaihe -kohdassa mukautettua saraketta edeltävä vaihe, eli Poistetut sarakkeet vaihe. Korvaamme tässä Weather-sijoituksen arvon Arizonassa. Napsauta hiiren kakkospainikkeella solua, joka sisältää Arizonan Weather-sijoituksen. Valitse sitten Korvaa arvot. Huomioi, mikä käytössä oleva vaihe on valittuna.

    Select Replace Values for column

  5. Valitse lisää.

    Koska lisäämme vaiheen, Power Query -editori varoittaa tämän vaaroista: myöhemmät vaiheet voivat aiheuttaa kyselyn keskeytymisen.

    Insert Step verification

  6. Muuta tietoarvoksi 51.

    Power Query -editori korvaa Arizonan tiedot. Kun luot uuden käytössä olevan vaiheen, Power Query -editori sen nimen toiminnon perusteella. Tässä tapauksessa se on Replaced Value. Jos sinulla kyselyssä useita vaiheita, joilla on sama nimi, Power Query -editori lisää jokaiseen myöhempään käytettyun vaiheeseen numeron (järjestyksessä), jotta erotat vaiheet toisistaan.

  7. Valitse viimeinen käytössä oleva vaihe, Lajiteltu rivit.

    Huomaa, että tiedot ovat muuttuneet Arizonan uuden sijoituksen suhteen. Muutos johtuu siitä, että lisäsimme Replaced Value -vaiheen oikeaan sijaintiin ennen Added Custom -vaihetta.

  8. Lopuksi haluamme vaihtaa taulukon nimeksi paremmin kuvaavan nimen. Kirjoita kysely asetukset-ruudun ominaisuudet-kohtaan taulukon uusi nimi ja valitse Anna. Anna tälle taulukolle nimeksi RetirementStats.

    Rename table in Query Settings

    Kun ryhdyt luomaan raportteja, taulukoiden nimien kannattaa olla kuvaavia. Tämä pätee etenkin silloin, jos muodostat yhteyden useisiin tietolähteisiin, jotka on lueteltu raporttinäkymänKentät-ruudussa.

    Nyt olemme muotoilleet tietojamme tarpeeksi. Seuraavaksi muodostamme yhteyden toiseen tietolähteeseen ja yhdistämme tiedot.

Tietojen yhdistäminen

Tiedot eri osavaltioista ovat mielenkiintoisia. Niistä on myös hyötyä muidenkin analyysien ja kyselyiden luomisessa. Tässä on kuitenkin yksi ongelma: suurin osa saatavilla olevista tiedoista käyttää kaksikirjaimisia osavaltiolyhenteitä, ei osavaltioiden koko nimiä. Meidän täytyy siis yhdistää osavaltioiden nimet ja niiden lyhenteet.

Meillä on kuitenkin onnea, sillä eräs toinen julkinen tietolähde tekee juuri tämän, mutta sen tiedot vaativat suhteellisen paljon muotoilua, ennen kuin voimme yhdistää ne tähän eläköitymistaulukkoomme. Jos haluat muotoilla tietoja, toimi seuraavasti:

  1. Valitse Power Query -editori Aloitus-valintanauhasta Uusi lähde > Verkko.

  2. Anna osavaltioiden lyhenteet sisältävän sivuston osoite, https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations, ja valitse sitten Yhdistä.

    Siirtymistoiminto näyttää verkkosivuston sisällön.

    Navigator page

  3. Valitse Koodit ja lyhenteet.

    Vihje

    Vaatii melko paljon työtä, ennen kuin taulukon tiedot on muotoiltu haluamallamme tavalla. Onko alla lueteltujen vaiheiden suorittamiseen jokin nopeampi tai helpompi tapa? Kyllä, voit luoda kahden taulukon välille suhteen ja muotoilla tietoja sitten tämän suhteen perusteella. Seuraavista vaiheista on kuitenkin hyötyä, kun opettelet taulukoiden käyttöä, mutta suhteiden avulla voit käyttää nopeasti tietoja useista taulukoista.

Jos haluat saada tiedot kuntoon, toimi seuraavasti:

  1. Poista ylin rivi. Ylin rivi on seurausta tavasta, jolla verkkosivun taulukko on luotu, joten emme tarvitse sitä. Valitse Aloitus-valintanauhasta Poista rivit Poista ylimmät > rivit.

    Select Remove Top Rows

    Näyttöön avautuu Poista ylimmät rivit ikkuna, jossa voit määrittää, montako riviä haluat poistaa.

    Huomautus

    Jos Power BI tuo vahingossa taulukko-otsikot rivinä tietotaulukkoosi, voit korjata taulukkosi valitsemalla valintanauhan Aloitus- tai Muunna-välilehdestä Käytä ensimmäistä riviä otsikkoina.

  2. Poista alimmat 26 riviä. Nämä rivit ovat Yhdysvaltain alueita, joita meidän ei tarvitse sisällyttää. Valitse Aloitus-valintanauhasta Poista rivit Poista alimmat > rivit.

    Select Remove Bottom Rows

  3. Koska RetirementStats-taulukko ei sisällä Washington DC:n tietoja, meidän täytyy suodattaa se luettelostamme. Valitse avattava Region Status -valikko ja poista sitten valintaruutu Federal district -kohdan vierestä.

    Clear Federal district check box

  4. Poista muutama tarpeeton sarake. Koska meidän täytyy vain yhdistää kukin osavaltio sen viralliseen kaksikirjaimiseen lyhenteeseen, voimme poistaa useita sarakkeita. Valitse ensin sarake, paina CTRL-näppäin pohjaan ja valitse sitten muut poistettavat sarakkeet. Valitse valintanauhan Aloitus-välilehdestä Poista sarakkeet > Poista sarakkeet.

    Remove column

    Huomautus

    Nyt on hyvä panna esiin, että Power Query -editori käytössä olevien vaiheiden järjestyksellä on paljon merkitystä, ja se voi vaikuttaa tietojen muotoiluun. On tärkeää huomioida se, miten yksi vaihe voi vaikuttaa toiseen myöhempään vaiheeseen. Jos poistat vaiheen käytetyistä vaiheista, myöhemmät vaiheet eivät ehkä toimi niin kuin alun perin tarkoitit, koska vaiheen poistaminen kyselyn vaihejärjestyksestä vaikuttaa muihin vaiheisiin.

    Huomautus

    Kun pienennät Power Query -editori ikkunan kokoa leveyden pienentämiseksi, jotkin valintanauhan kohteet tiivistetään, jotta näkyvä tila on käytettävissä mahdollisimman tehokkaasti. Kun levennät Power Query -editori ikkunaa, valintanauhan kohteet laajentuvat, jotta valintanauhan suurempi alue saadaan hyödyntämään eniten.

  5. Nimeä sarakkeet ja taulukko uudelleen. Sarakkeen voi nimetä uudelleen muutamalla tavalla: Valitse ensin sarake ja valitse sitten Nimeä uudelleen valintanauhan Muunna-välilehdestä tai napsauta hiiren kakkospainikkeella ja valitse Nimeä uudelleen. Seuraavassa kuvassa on nuolet, jotka osoittavat molempiin toimintoihin, mutta sinun tarvitsee käyttää vain toista.

    Rename column in Power Query Editor

  6. Anna sarakkeiden uusiksi nimiksi State Name ja State Code. Jos haluat nimetä taulukon uudelleen, anna nimiKyselyasetukset-ruudussa. Anna tälle taulukolle nimeksi StateCodes.

Yhdistä kyselyt

Nyt kun olemme muotoilleet StateCodes-taulukon halutulla tavalla, yhdistetään nämä kaksi taulukkoa tai kyselyä yhdeksi. Koska taulukot, jotka meillä nyt ovat, ovat tulosta tietoihin soveltamistamme kyselyistä, niitä kutsutaan usein kyselyiksi.

Tärkeimmät kyselyjen yhdistämistavat ovat yhdistäminen ja liittäminen.

  • Jos sinulla on sarake tai sarakkeita, jonka tai jotka haluat lisätä toiseen kyselyyn, yhdistä kyselyt.
  • Jos sinulla on lisärivejä tietoja, jotka haluat lisätä olemassa olevaan kyselyyn, liitä ne kyselyn loppuun.

Tässä tapauksessa haluamme yhdistää kyselyt. Toimi seuraavasti:

  1. Valitse Power Query -editori vasemmasta ruudusta kysely, johon haluat toisen kyselyn yhdistyvän. Tässä tapauksessa se on RetirementStats.

  2. Valitse Yhdistä kyselyt > Yhdistä kyselyt valintanauhan Aloitus-välilehdestä .

    Select Merge Queries

    Sinua saatetaan pyytää määrittämään yksityisyystaso. Näin varmistetaan, että tiedot yhdistetään siten, että sisällytä tai siirrä tietoja, joita et halua.

    Seuraavaksi näyttöön avautuu Yhdistä-ikkuna. Siinä kysytään, minkä taulukon haluat yhdistää valittuun taulukkoon ja pyydetään määrittämään yhdistämisen vastaavat sarakkeet.

  3. Valitse RetirementStats-taulukosta State ja valitse sitten StateCodes-kysely.

    Kun valitset oikeat vastaavat sarakkeet, OK-painike on käytössä.

    Merge window

  4. Valitse OK.

    Power Query -editori luo kyselyn loppuun uuden sarakkeen, joka sisältää sen taulukon (kyselyn) sisällön, joka yhdistettiin olemassa olevaan kyselyyn. Kaikki yhdistetyn kyselyn sarakkeet tiivistetään sarakkeeseen, mutta voit laajentaa taulukon ja sisällyttää haluamasi sarakkeet.

    NewColumn column

  5. Jos haluat laajentaa yhdistetyn taulukon ja valita sisällytettävät sarakkeet, valitse laajennuskuvake (Expand icon).

    Näyttöön avautuu Laajenna-ikkuna.

    NewColumn in query

  6. Tässä tapauksessa haluamme vain State Code -sarakkeen. Valitse sarake, poista kohdan Käytä alkuperäisen sarakkeen nimeä etuliitteenä valinta ja valitse sitten OK.

    Jos olisimme jättäneet kohdan Käytä alkuperäisen sarakkeen nimeä etuliitteenä valintaruudun valituksi, yhdistetyn sarakkeen nimeksi olisi tullut NewColumn.State Code.

    Huomautus

    Haluatko kokeilla, miten voit tuoda NewColumn-taulukon? Voit kokeilla eri tapoja. Jos et pidä tuloksista, poista kyseinen vaihe Kyselyasetukset-ruudun käytössä olevien vaiheiden luettelosta. Tämä palauttaa kyselysi tilaan, jossa se oli ennen tätä laajennusvaihetta. Voit tehdä näin niin monta kertaa kuin haluat, kunnes saat laajennusprosessista haluamasi kaltaisen.

    Nyt käytössä on yksittäinen kysely (taulukko), joka yhdistää kaksi tarpeiden mukaan muotoiltua tietolähdettä. Tämän kyselyn pohjalta voit luoda useita muitakin kiinnostavia tietoyhteyksiä, esimerkiksi asumiskuluista, demografisista tiedoista tai työmahdollisuuksista missä tahansa osavaltiossa.

  7. Jos haluat ottaa muutokset käyttöön ja sulkea Power Query -editorin, valitse Sulje & käytä Aloitus-valintanauhan välilehdeltä.

    Muunnettu tietojoukko näytetään Power BI Desktopissa, jossa voit luoda raportteja sen pohjalta.

    Select Close & Apply

Seuraavat vaiheet

Lisää tietoja Power BI Desktopista ja sen toiminnoista on seuraavissa resursseissa: