Power Query: Kyselyiden yhdistäminen Merge-toiminnolla

Power Query: Kyselyiden yhdistäminen Merge-toiminnolla
Kyselyoppaan sisällysluettelo
Edellinen kyselyoppaan juttu Seuraava kyselyoppaan juttu ->

Power Query:ssä voi yhdistää tietoja eli kyselyitä kahdella eri menetelmällä:

  1. Joko Merge (Yhdistä) -toiminnolla, jos molemmissa kyselyissä on yhdistävä tieto, kuten esimerkiksi tuote- tai henkilötunnus. Merge-toiminto muistuttaa VLOOKUP (PHAKU) -tyyppistä yhdistämistä, mutta on huomattavasti monipuolisempi. Yhdistämisen voi esimerkiksi tehdä useamman sarakkeen perusteella ja tulosjoukossa voi olla enemmän rivejä kuin alkuperäisessä luettelossa.
  2. Tai Append (Liitä loppuun) -toiminnolla, jolloin samankaltaisten kyselyiden tuloksia yhdistetään peräkkäin.

Tässä jutussa kerrotaan Merge-toiminnosta.

[Edit 10/2016: Väline kehittyy joten tämä teksti on tarkistettu ja korvattu lokakuussa 2016.]

Toimintaperiaate

Merge yhdistää kyselyitä. Seuraavassa kuvassa Tuotteet-kyselyyn on yhdistetty Tuoteryhmät-kysely ja jokaisen tuotteen kohdalle on valittu näytettäväksi tuoteryhmän nimi. Yhdistäminen on mahdollista, sillä molemmista löytyy tuoteryhmän tunnus (TR ID), joka on molemmissa tietotyypiltään samanlainen eli tässä tapauksessa numeerinen tieto.

product merge

Erot VLOOKUP (PHAKU) -yhdistämiseen verrattuna

  • Voit yhdistää yhdellä Merge-toiminnolla kerralla useita eri sarakkeita (ks. seuraava kuva).
  • Molemmissa yhdistettävissä luetteloissa yhdistävä tieto (kuten TR ID) voi esiintyä useita kertoja. Kyselyeditori muodostaa useita rivejä, jos sama TR ID esiintyy useita kertoja tuoteryhmäluettelossa.
  • Yhdistämisen voi aloittaa kummasta kyselystä lähtien tahansa. Voit myös yhdistää Tuoteryhmät -kyselyyn Tuotteet, jolloin jokaista tuoteryhmäriviä monistetaan niin moneen kertaan kuin Tuotteet-listalta löytyy tuotteita:tuoteryhmät tuotteineen
  • Merge-toiminnolla voi yhdistämisen lisäksi tai sijaan laskea yhteenvetotuloksia.
    laskeminen
  • Jos tauluja yhdistävä sarake on tekstitietoa, merge-toiminnolla yhdistäminen vaatii, että tekstien pitää olla jopa kirjainkooltaan samanlaisia. VLOOKUP ei ota kantaa kirjainkokoon. Muunna siis yhdistettävissä kyselyissä tekstisarake valmiiksi samaan muotoon joko esimerkiksi isoiksi tai pieniksi kirjaimiksi.

Kyselyiden latauksen määritys

Alkuperäisiä kyselyitä ei yleensä haluta ladata tiedostoon. Niiden sijaan ladataan vain yhdistetty tulos.

Latauksen estäminen Excelissä

Kunkin kyselyn latautuvuuden voi määrittää Power Query > Show Pane (Power Query > Näytä ruutu) -paneelista pikavalikon avulla. Excel 2016 -versiossa paneeli avautuu komennolla Data > Show Queries.

lataaminen

Latauksen estäminen Power BI Desktopissa

Siirry Home > Edit Queries -komennolla kyselyeditoriin ja ota lataus pois käytöstä Queries -paneelissa pikavalikosta.

Power BI latauksen määritys

Ohje: yhdistäminen Excelissä

  1. Luo ensin ne kyselyt, jotka haluat yhdistää.
  2. Tuo esiin kyselyt Excel 2010/2013 -versiossa Power Query > Show Pane (Power Query > Näytä ruutu) -painikkeella ja Excel 2016 -versiossa komennolla Data > Show Queries.
  3. Valitse sen kyselyn pikavalikosta Merge (Yhdistä), johon haluat yhdistää toisen kyselyn tietoja.merge-komento
  4. Valitse yhdistettävä kysely.valitse toinen kysely
  5. Valitse molemmista kyselyistä yhdistävä sarake. (Jos yhdistäviä sarakkeita on useita, voit valita useita sarakkeita. Huomaa kuitetnkin, että napsautat niitä samassa järjestyksessä molemmista kyselyistä. Kuhunkin sarakkeeseen ilmestyy pieni järjestysnumero, joten näet helposti oletko valinnut ne oikeassa järjestyksessä.) Valitse myös liitoksen laji (join kind). Löydät lisätietoja liitoslajeista täältä.
    merge-ikkuna
  6. Kun etenet OK:lla, Power Query muodostaa uuden yhdistävän kyselyn ja avaa editorin. Anna kyselylle nimi.
    uusi kysely
  7. Yhdistettävä taulu näkyy esikatseluruudussa uutena sarakkeena (NewColumn) ja kullakin rivillä lukee Table. Napsauta kyseisen sarakkeen otsikossa näkyvää laajennuspainiketta ja valitse ensin haluatko laajentaa esiin sarakkeita (expand) vai suorittaa tiedoilla koostelaskutoimituksen (aggregate). Valitse sen jälkeen joko laajennettavat tai laskettavat tiedot.Jos haluat laskea summia, keskiarvoja tai lukumääriä, sarakkeiden tietotyyppien pitää olla numeerisia siinä kyselyssä, jota olet yhdistämässä. Tästä syystä on tärkeä määrittää jokaisen kyselyn tietotyypit tarkoituksenmukaisesti.Jos jätät rastin kohtaan Use original column name as prefix (Käytä alkuperäisen sarakkeen nimeä etuliitteenä), sarakkeen nimeksi tulee NewColumn.Tuoteryhmä. Muutoin sarakkeen nimeksi tulee vain Tuoteryhmä.tietojen laajentaminen
  8. Tarkista ja korjaa vielä sarakkeiden tietotyypit ja lataa kyselyn tulos joko Exceliin tai Power Pivotiin.

Poista lopulta Tuotteet ja Tuoteryhmät -kyselyiden lataus, jos et tarvitse niitä erikseen.

load to

load-to

Kun valitset, että tietoja ei ladata mihinkään (Only Create Connection), Excel ilmoittaa tietojen katoamisesta. Kysely ei kuitenkaan katoa, joten voit hyväksyä seuraavan ikkunan.

ilmoitus

Yhdistävän kyselyn päivitys Excelissä

Kun päivität yhdistävän kyselyn, Power Query suorittaa aina ensin ne kyselyt, jotka yhdistetään. ”Alikyselyiden” suoritus ei valitettavasti näy missään.

päivitys

Omituisuus, johon toivottavasti tulee korjaus tulevissa Power Query -versioissa: Jopa kyselyn päältä avautuva ruutu näyttää vanhaa päivitysajankohtaa, vaikka kysely on päivitetty yhdistävän kyselyn suorituksen seurauksena.

last refreshed

Ohje: Yhdistäminen Power BI Desktopissa

  1. Luo ensin ne kyselyt, jotka haluat yhdistää.
  2. Siirry kyselyeditoriin Home > Edit Queries -komennolla.
  3. Valitse Queries-paneelista se kysely (esim. Tuotteet), johon haluat liittää toisen ja anna komento Home > Merge Queries > Merge Queries as New ja valitse taulu, jonka yhdistät.

    Valitse ne sarakkeet, joiden perusteella yhdistäminen tehdään. Huomaa, että voit napsauttaa jopa useiden sarakkeiden muodostaman kokonaisuuden. Valitse myös liitoslaji. Löydät liitoslajeista lisätietoja esimerkiksi täältä tai täältä.merge ikkuna power bi desktopissa

  4. Kun etenet OK:lla, yhdistetty taulu näkyy yhtenä sarakkeena (NewColumn) ja kullakin rivillä lukee Table. Napsauta kyseisen sarakkeen otsikossa näkyvää laajennuspainiketta ja valitse ensin haluatko laajentaa esiin sarakkeita (expand) vai suorittaa tiedoilla koostelaskutoimituksen (aggregate). Valitse sen jälkeen joko laajennettavat tai laskettavat tiedot. Jos haluat laskea summia, keskiarvoja tai lukumääriä, sarakkeiden tietotyyppien pitää olla numeerisia siinä kyselyssä, jota olet yhdistämässä. Tästä syystä on tärkeä määrittää jokaisen kyselyn tietotyypit tarkoituksenmukaisesti. Jos jätät rastin kohtaan Use original column name as prefix (Käytä alkuperäisen sarakkeen nimeä etuliitteenä), sarakkeen nimeksi tulee NewColumn.Tuoteryhmä. Muutoin sarakkeen nimeksi tulee vain Tuoteryhmä.mergen jälkeen power bi desktopissa
  5. Tarkista ja korjaa vielä sarakkeiden tietotyypit.

Poista lopulta Tuotteet ja Tuoteryhmät -kyselyiden lataus (Queries-paneelin pikavalikosta Enable Load), jos et tarvitse niitä erikseen.

Kokeile yhdistämistä itse

  1. Aloita uusi työkirja ja suorita nouda Suomen kunnat Exceliin edellisen blogijutun ohjeiden avulla.
  2. Nouda samaan työkirjaan myös Suomen kuntien koordinaatit osoitteesta http://fi.wikipedia.org/wiki/Luettelo_Suomen_kuntien_koordinaateista
  3. Kun olet saanut molemmat kyselyt valmiiksi, yhdistä ensimmäiseen kyselyyn mukaan jälkimmäisessä kyselyssä olevat koordinaatit. Kun aloitat kahden kyselyn yhdistämisen ja kyselyt noutavat tietoja työkirjan ulkopuolelta, sinua pyydetään määrittämään kyseisten tietolähteiden yksityisyystaso. Valitse, että molemmat kyselyt sisältävät julkista tietoa.yksityisyystaso
  4. Määritä lopuksi, että vain yhdistävä kysely ladataan työkirjaan.

Yhdistävän kyselyn lopputuloksena on sekä kuntien perustiedot että koordinaatit.

Kyselyoppaan sisällysluettelo
Edellinen kyselyoppaan juttu Seuraava kyselyoppaan juttu ->

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s