Opi yhdistämään useita tietolähteitä (Power Query)

Käytetään kohteeseen
Excel for Microsoft 365 Excel 2024 Excel 2021

Tässä opetusohjelmassa tuodaan Power Query:n Kyselyeditorin avulla tietoja paikallisesta, tuotetietoja sisältävästä Excel-tiedostosta sekä tuotteen tilaustietoja sisältävästä OData-syötteestä. Luo tuote - ja vuosikohtainen kokonaismyynti -raportti muuntamalla ja koostamalla tietoja sekä yhdistämällä tietoja molemmista lähteistä.   

Tämän opetusohjelman suorittamiseen tarvitaan Tuotteet-työkirja . Anna tiedostolle Tallenna nimellä -valintaikkunassa nimeksi Tuotteet ja tilaukset.xlsx.

Tehtävä 1: Tuo tuotteet Excel-työkirjaan

Tässä tehtävässä voit tuoda tuotteita Tuotteet ja Orders.xlsx -tiedostosta (ladattu ja nimetty uudelleen edellisessä osassa) Excel-työkirjaan. Tämän jälkeen voit ylentää rivit sarakeotsikoiksi, poistaa joitakin sarakkeita ja ladata kyselyn laskentataulukkoon.

Vaihe 1: Yhdistäminen Excel-työkirjaan

  1. Luo Excel-työkirja.
  2. Valitse Tiedot>, Hae tiedot>tiedostosta>työkirjasta.
  3. Etsi Tietojen tuominen -valintaikkunasta lataamasi Products.xlsx tiedosto selaamalla ja valitse Avaa.
  4. Kaksoisnapsauta Siirtymistoiminto-ruudussaTuotteet-taulukkoa . Power Query -editori tulee näkyviin.

Vaihe 2: Kyselyn vaiheiden tarkasteleminen

Power Query lisää oletusarvoisesti automaattisesti useita vaiheita käytön helpottamiseksi. Saat lisätietoja tarkastelemalla kutakin vaihetta Kyselyasetukset-ruudunKäytössä olevat vaiheet -kohdassa.

  1. Napsauta hiiren kakkospainikkeella Lähde-vaihetta ja valitse Muokkaa asetuksia. Tämä vaihe luotiin, kun toit työkirjan.
  2. Napsauta hiiren kakkospainikkeella siirtymisvaihetta ja valitse Muokkaa asetuksia. Tämä vaihe luotiin, kun valitsit taulukon siirtymisvalintaikkunasta .
  3. Napsauta hiiren kakkospainikkeella Muutettu tyyppi -vaihetta ja valitse Muokkaa asetuksia. Tämän vaiheen loi Power Query, joka johti kunkin sarakkeen tietotyypit. Näet koko kaavan valitsemalla kaavarivin oikealla puolella olevan alanuolen.

Vaihe 3: Näytä vain halutut sarakkeet poistamalla muut sarakkeet

Tässä vaiheessa voit poistaa kaikki muut sarakkeet kuin Tuotetunnus,Tuotteen nimi, Tuoteryhmän tunnus ja MääräYksikköä.

  1. Valitse tietojen esikatselussasarakkeet ProductID,ProductName, CategoryID, ja QuantityPerUnit (käytä Ctrl+napsautus tai Vaihto+Napsauta).
  2. Valitse Poista sarakkeet>Poista muut sarakkeet.
    Näyttökuva, jossa näkyy Piilota muut sarakkeet.

Vaihe 4: Tuotekyselyn lataaminen

Tässä vaiheessa ladataan Tuotteet-kysely Excel-laskentataulukkoon.

  • Valitse Aloitus,>Sulje & Lataa. Kysely näkyy uudessa Excel-laskentataulukossa.

Yhteenveto: Tehtävässä 1 luodut Power Query -vaiheet

Kun suoritat kyselytoimintoja Power Query:ssä, se luo kyselyn vaiheita ja luetteloi ne Käytössä olevat vaiheet -luettelon Kyselyasetukset-ruudussa. Kullakin kyselyn vaiheella on sitä vastaava Power Query -kaava, jota kutsutaan myös M-kieleksi. Lisätietoja Power Query -kaavoista on Power Query -dokumentaatiossa.

Tehtävä Kyselyn vaihe Kaava
Excel-työkirjan tuominen Lähde = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Tuotteet-taulukon valitseminen Siirry = Source{[Item="Products",Kind="Table"]}[Data]
Power Query tunnistaa sarakkeen tietotyypit automaattisesti Muutettu tyyppi = Table.TransformColumnTypes( Products_Table,{{"Tuotetunnus", Int64.Type}, {"TuotteenNimi", type text}, {"Toimittajatunnus", Int64.Type}, {"Luokkatunnus", Int64.Type}, {"MääräYksikköäKohden", tyyppiteksti}, {"Yksikköhinta", tyyppinumero}, {"YksiköitäVarastossa", Int64.Tyyppi}, {"YksiköitäTilauksessa", Int64.Tyyppi}, {"Uudelleentilaustaso", Int64.Tyyppi}, {"Lopetettu", tyyppi looginen}})
Näytä vain halutut sarakkeet poistamalla muut sarakkeet Muut sarakkeet poistettu = Table.SelectColumns(FirstRowAsHeader,{"Tuotetunnus", "TuotteenNimi", "Luokkatunnus", "MääräYksikköäKohden"})

Tehtävä 2: Tuo tilaustiedot OData-syötteestä

Tässä tehtävässä tuot tietoja Excel-työkirjaan Northwind OData -esimerkkisyötteestä http://services.odata.org/Northwind/Northwind.svc, laajennat Order_Details-taulukkoa, poistat sarakkeita, lasket rivisumman, muunnat tilauspäivän, ryhmität rivit tuotetunnuksen ja vuoden mukaan, nimeät kyselyn uudelleen ja estät kyselyn lataamisen Excel-työkirjaan.

Vaihe 1: Yhteyden muodostaminen OData-syötteeseen

  1. Valitse tiedot>,hae tiedot>muista lähteistä>OData-syötteen avulla.
  2. Kirjoita OData-syöte-valintaikkunaan Northwind OData -syötteen URL.
  3. Valitse OK.
  4. Kaksoisnapsauta Etsintä-ruudussaTilaukset-taulukkoa .

Vaihe 2: Order_Details taulukon laajentaminen

Tässä vaiheessa voit laajentaa Tilaukset-taulukkoon liittyvän Tilaustiedot-taulukon yhdistääksesi Tuotetunnus-, Yksikköhinta- ja Määrä-sarakkeet Tilaustiedot-kohdasta Tilaukset-taulukkoon. Laajenna-toiminto yhdistää liittyvän taulukon sarakkeet aihetaulukkoon. Kun kyselyä suoritetaan, liittyvän taulukon (Order_Details) rivit yhdistetään riveille ensisijaisen taulukon (Tilaukset) kanssa.

Power Query liittyvän taulukon sisältävän sarakkeen solussa on arvo Tietue tai Taulukko. Tällaisia sarakkeita kutsutaan rakenteellisiksi sarakkeiksi. Tietue osoittaa yksittäisen liittyvän tietueen ja yksi-yhteen-yhteyden nykyisten tietojen tai ensisijaisen taulukon kanssa. Taulukko osoittaa liittyvän taulukon ja yksi-moneen-yhteyden nykyiseen tai ensisijaiseen taulukkoon. Rakenteellinen sarake edustaa suhdetta tietolähteessä, jossa on relaatiomalli. Jäsennetty sarake osoittaa esimerkiksi kohteen, jolla on viiteavainkytkentä OData-syötteessä tai viiteavainsuhde SQL Server -tietokannassa.

Kun laajennat Order_Details-taulukon , Tilaukset-taulukkoon lisätään kolme uutta saraketta ja lisää rivejä, yksi jokaista sisäkkäisen tai liittyvän taulukon riviä kohden.

  1. Vieritä tietojen esikatselussa vaakasuunnassa Order_Details-sarakkeeseen.

  2. Valitse Order_Details-sarakkeessa laajennuskuvake ( ).

  3. Avattavassa Laajenna-valikossa:

    1. Tyhjennä kaikki sarakkeet valitsemalla (Valitse kaikki sarakkeet).

    2. Valitse Tuotetunnus, Yksikköhinta ja Määrä.

    3. Valitse OK.
      Näyttökuva, jossa näkyy Laajenna Order_Details Taulukko -linkki.

      Huomautus

      Power Query -kyselyssä voit laajentaa sarakkeesta linkitettyjä taulukoita ja koostaa linkitetyn taulukon sarakkeet yhteen ennen aihetaulukon tietojen laajentamista. Lisätietoja koostetoimintojen suorittamisesta on artikkelissa Tietojen koostaminen sarakkeesta (Power Query).

Vaihe 3: Näytä vain halutut sarakkeet poistamalla muut sarakkeet

Tässä vaiheessa voit poistaa kaikki muut sarakkeet Tilauspäivä-, Tuotetunnus-, Yksikköhinta- ja Määrä-sarakkeita lukuun ottamatta. 

  1. Valitse tietojen esikatselussa seuraavat sarakkeet:

    1. Valitse ensimmäinen sarake, Tilaustunnus.
    2. Vaihto+napsauta viimeistä saraketta, Lähettäjä.
    3. Napsauta Tilauspäivä-, Tilaustiedot.Tuotetunnus-, Tilaustiedot.Yksikköhinta- ja Tilaustiedot.Määrä-sarakkeita Ctrl-napsautus-yhdistelmällä.

  2. Napsauta valittua sarakeotsikkoa hiiren kakkospainikkeella ja valitse Poista muut sarakkeet.

Vaihe 4: Laske jokaisen Order_Details rivin rivisumma

Tässä vaiheessa voit luoda mukautetun sarakkeen laskeaksesi jokaisen Tilaustiedot-rivin rivisumman.

  1. Valitse tietojen esikatselussa taulukkokuvake ( ) esikatselun vasemmassa yläkulmassa.
  2. Valitse Lisää mukautettu sarake.
  3. Kirjoita Mukautettu sarake -valintaikkunan Mukautettu sarakekaava -ruutuun [Order_Details.Yksikköhinta] * [Order_Details.Quantity].
  4. Kirjoita Uuden sarakkeen nimi -ruutuun Rivisumma.
  5. Valitse OK.

Näyttökuva, jossa näkyy Laske rivin jokaisen Order_Details rivisumma.

Vaihe 5: Muunna Tilauspäivä-vuosisarake

Tässä vaiheessa voit muuntaa Tilauspäivä-sarakkeen hahmontamaan tilauspäivän vuoden.

  1. Napsauta tietojen esikatselussaTilauspäivä-saraketta hiiren kakkospainikkeella ja valitse Muunna>vuosi.

  2. Nimeä Tilauspäivä-sarake uudelleen nimellä Vuosi:

    1. Kaksoisnapsauta Tilauspv-saraketta ja kirjoita Vuosi tai
    2. Napsauta Tilauspäivä-saraketta hiiren kakkospainikkeella, valitse Nimeä uudelleen ja kirjoita vuosi.

Vaihe 6: Ryhmitä rivit tuotetunnuksen ja vuoden mukaan

  1. Valitse Tietojen esikatselussaVuosi ja Order_Details.Tuotetunnus.

  2. Napsauta jotakin otsikkoa hiiren kakkospainikkeella ja valitse Ryhmittelyperuste.

  3. Ryhmittelyperuste valintaikkunassa:

    1. Kirjoita Uuden sarakkeen nimi -tekstiruutuun Kokonaismyynti.
    2. Valitse avattavassa Toiminto-valikossa Summa.
    3. Valitse avattavassa Sarake-valikossa Rivisumma.

  4. Valitse OK.
    Näyttökuva koostetoimintojen Ryhmittelyperuste-valintaikkunasta.

Vaihe 7: Nimeä kysely uudelleen

Ennen myyntitietojen tuomista Exceliin nimeä kysely uudelleen:

  • Kirjoita Kyselyasetukset-ruudunNimi-ruutuun Kokonaismyynti.

Tulokset: Tehtävän 2 viimeinen kysely

Jokaisen vaiheen suorittamisen jälkeen Northwind OData -syötteessä on Kokonaismyynti-kysely.

Näyttökuva kokonaismyynnistä.

Yhteenveto: Tehtävässä 2 luodut Power Query -vaiheet

Kun suoritat kyselytoimintoja Power Query:ssä, se luo kyselyn vaiheita ja luetteloi ne Käytössä olevat vaiheet -luettelon Kyselyasetukset-ruudussa. Kullakin kyselyn vaiheella on sitä vastaava Power Query -kaava, jota kutsutaan myös M-kieleksi. Lisätietoja Power Query -kaavoista on Power Query -dokumentaatiossa.

Tehtävä Kyselyn vaihe Kaava
Yhteyden muodostaminen OData-syötteeseen Lähde = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
Taulukon valitseminen Siirtyminen = Source{[Name="Orders"]}[Data]
Laajenna Tilaustiedot-taulukko Laajenna Tilaustiedot = Table.ExpandTableColumn(Tilaukset, "Order_Details", {"Tuotetunnus", "Yksikköhinta", "Määrä"}, {"Order_Details.Tuotetunnus", "Order_Details.Yksikköhinta", "Order_Details.Määrä"})
Näytä vain halutut sarakkeet poistamalla muut sarakkeet RemovedColumns = Table.RemoveColumns(#"Laajenna Order_Details",{"Tilaustunnus", "Asiakastunnus", "Työntekijätunnus", "VaadittuPäivämäärä", "Lähetyspäivä", "LähetysKautta", "Rahti", "Lähetysnimi", "Toimitusosoite", "Toimituskaupunki", "Toimitusalue", "Toimituspostikoodi", "Toimitusmaa", "Asiakas", "Työntekijä", "Lähettäjä"})
Laske jokaisen Tilaustiedot-rivin rivisumma Mukautettu lisätty = Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
= Table.AddColumn(#"Laajennettu Order_Details", "Rivisumma", kukin [Order_Details.UnitPrice] * [Order_Details.Quantity])
Vaihda kuvaavampaan nimeen, Lne Yhteensä. Uudelleennimetyt sarakkeet = Table.RenameColumns(InsertedCustom,{{"Mukautettu", "Rivisumma"}})
Muunna Tilauspäivä-sarake vuoden hahmontamiseksi Poimittu vuosiluku = Table.TransformColumns(#"Ryhmitellyt rivit",{{"Vuosi"; Date.Year, Int64.Type}})
Muuta muotoon
kuvaavampia nimiä, Tilauspäivä ja Vuosi		 Uudelleennimetyt sarakkeet 1 Table.RenameColumns
(TransformedColumn;{{"Tilauspäivä"; "Vuosi"}})
Ryhmitä rivit tuotetunnuksen ja vuoden mukaan GroupedRows = Table.Group(RenamedColumns1, {"Vuosi", "Order_Details.Tuotetunnus"}, {{"Kokonaismyynti", kukin List.Sum([Rivisumma]), tyypin numero}})

Tehtävä 3: Yhdistä Tuotekyselyt ja Kokonaismyynti-kyselyt

Power Query mahdollistaa useiden kyselyjen yhdistämisen yhdistämällä tai liittämällä ne. Voit suorittaa yhdistämistoiminnon missä tahansa taulukkomuotoisessa Power Query -kyselyssä tietolähteestä riippumatta. Lisätietoja tietolähteiden yhdistämisestä on artikkelissa Useiden kyselyjen yhdistäminen (Power Query).

Tässä tehtävässä voit yhdistää Tuotteet- ja Kokonaismyynti-kyselytYhdistä-kyselyn ja Laajenna-toiminnon avulla ja ladata sitten Tuotekohtainen kokonaismyynti -kyselyn Excel-tietomalliin.

Vaihe 1: Yhdistä tuotetunnus Kokonaismyynti-kyselyyn

  1. Siirry Excel-työkirjassa Tuotteet-laskentataulukon välilehden Tuotteet-kyselyyn.

  2. Valitse kyselyn solu ja valitse sitten Kyselyn>yhdistäminen.

  3. Valitse Yhdistäminen-valintaikkunassa ensisijaiseksi taulukoksi Tuotteet ja valitse Toissijaiseksi tai liittyväksi yhdistettäväksi kyselyksi Kokonaismyynti . Kokonaismyynnistä tulee uusi jäsennetty sarake, jossa on laajennuskuvake.

  4. Jotta Kokonaismyynti vastaa Tuotteet-kohtaa Tuotetunnus-kohdan mukaan, valitse Tuotetunnus-sarake Tuotteet-taulukosta ja Tilaustiedot.Tuotetunnus-sarake Kokonaismyynti-taulukosta.

  5. Yksityisyystasot valintaikkunassa:

    1. Valitse tietosuojan eristystasoksi Organisaatio kummankin tietolähteen kohdalla.
    2. Valitse Tallenna.

  6. Valitse OK.

    Huomautus

    Yksityisyystasot estävät käyttäjää vahingossa yhdistämästä useiden tietolähteiden tietoja, jotka voivat olla yksityisiä tai organisaation tietoja. Kyselystä riippuen käyttäjä voi tahattomasti lähettää tietoja yksityisestä tietolähteestä toiseen, mahdollisesti haitalliseen tietolähteeseen. Power Query analysoi jokaisen tietolähteen ja luokittelee sen määritettyyn tietosuojatasoon: Julkinen, Organisaatio ja Yksityinen. Lisätietoja yksityisyystasoista on artikkelissa Yksityisyystasojen määrittäminen (Power Query).

    Näyttökuva Yhdistä-valintaikkunasta.

Tulos

Yhdistämistoiminto luo kyselyn. Kyselyn tulos sisältää kaikki sarakkeet ensisijaisesta taulukosta (Tuotteet) sekä yhden taulukon jäsennetyn sarakkeen liittyvään taulukkoon (Kokonaismyynti). Valitse Laajenna-kuvake , jos haluat lisätä uusia sarakkeita ensisijaiseen taulukkoon toissijaisesta tai liittyvästä taulukosta.

Näyttökuva Yhdistä lopullinen -vaihtoehdosta.

Vaihe 2: Yhdistetyn sarakkeen laajentaminen

Tässä vaiheessa voit laajentaa yhdistetyn UusiSarake-nimisen sarakkeen, jotta Tuotteet-kyselyyn luodaan kaksi uutta saraketta: Vuosi ja Kokonaismyynti.

  1. Valitse tietojen esikatselussaUusiSarake-kohdan vieressä oleva Laajenna-kuvake ( ).

  2. Avattavassa Laajenna-luettelossa :

    1. Tyhjennä kaikki sarakkeet valitsemalla (Valitse kaikki sarakkeet).
    2. Valitse Vuosi ja Kokonaismyynti.
    3. Valitse OK.

  3. Nimeä nämä kaksi saraketta uudelleen nimillä Vuosi ja Kokonaismyynti.

  4. Jos haluat selvittää, mitkä tuotteet saivat eniten myyntiä ja minä vuosina, valitse Lajittele laskevastikokonaismyynnin mukaan.

  5. Nimeä uudelleen kysely nimellä Tuotekohtainen kokonaismyynti.

Tulos

Näyttökuva Laajenna taulukko -linkistä.

Vaihe 3: Lataa Tuotekohtainen kokonaismyynti -kysely Excel-tietomalliin

Tässä vaiheessa ladataan kysely Excel-tietomalliin, jotta voit luoda kyselyn tulokseen yhdistetyn raportin. Kun olet ladannut tietoja Excel-tietomalliin, voit analysoida tietoja Power Pivotin avulla.

  1. Valitse Aloitus,>Sulje & Lataa.
  2. Varmista Tietojen tuominen -valintaikkunassa, että valitset Lisää nämä tiedot tietomalliin. Saat lisätietoja tämän valintaikkunan käyttämisestä valitsemalla kysymysmerkin (?).

Tulos

Sinulla on Tuotekohtainen kokonaismyynti -kysely, joka yhdistää Products.xlsx-tiedoston ja Northwind OData -syötteen tiedot. Tätä kyselyä sovelletaan Power Pivot -malliin. Lisäksi kyselyyn tehdyt muutokset muokkaavat ja päivittävät tietomallin tuloksena olevaa taulukkoa.

Yhteenveto: Tehtävässä 3 luodut Power Query -vaiheet

Kun suoritat yhdistämiskyselytoimintoja Power Query:ssä, kyselyn vaiheita luodaan ja luetteloidaan Käytössä olevat vaiheet -luettelon Kyselyasetukset-ruudussa. Kullakin kyselyn vaiheella on sitä vastaava Power Query -kaava, jota kutsutaan myös M-kieleksi. Lisätietoja Power Query -kaavoista on Power Query -dokumentaatiossa.

Tehtävä Kyselyn vaihe Kaava
Yhdistä tuotetunnus Kokonaismyynti-kyselyyn Lähde (Yhdistä-toiminnon tietolähde) = Table.NestedJoin(Tuotteet, {"Tuotetunnus"}, #"Kokonaismyynti", {"Order_Details.Tuotetunnus"}, "Kokonaismyynti", JoinKind.LeftOuter)
Laajenna Yhdistä-sarake Laajennettu kokonaismyynti = Table.ExpandTableColumn(Lähde, "Kokonaismyynti", {"Vuosi", "Kokonaismyynti"}, {"Kokonaismyynti.Vuosi", "Kokonaismyynti.Kokonaismyynti"})
Kahden sarakkeen nimeäminen uudelleen Uudelleennimetyt sarakkeet = Table.RenameColumns(#"Laajennettu kokonaismyynti",{{"Kokonaismyynti.Vuosi"; "Vuosi"}, {"Kokonaismyynti.Kokonaismyynti"; "Kokonaismyynti"}})
Lajittele kokonaismyynti nousevassa järjestyksessä Lajitellut rivit = Table.Sort(#"Uudelleennimetyt sarakkeet",{{"Kokonaismyynti", Tilaus.Nouseva}})

Katso myös

Microsoft Power Query for Excelin ohje