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

Tässä opetusohjelmassa voit tuoda Power Query Kyselyeditori tietoja paikallisesta Excel-tiedostosta, joka sisältää tuotetietoja, sekä OData-syötteestä, joka sisältää tuotetilaustietoja. Suoritat muunnos- ja koostevaiheet ja yhdistät tietoja molemmista lähteistä tuottaaksesi "Kokonaismyynti tuote- ja vuosikohtaisesti" -raportin.   

Jotta voit suorittaa tämän opetusohjelman, tarvitset Tuotteet-työkirjan . Anna tiedostolle Tallenna nimellä -valintaikkunassa nimeksi Tuotteet ja tilaukset.xlsx.

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

Tässä tehtävässä tuot tuotteet Tuotteet- ja Orders.xlsx (ladatut ja uudelleennimetty yllä) -tiedostosta Excel-työkirjaan, ylennät rivit sarakeotsikoiksi, poistat joitakin sarakkeita ja lataat kyselyn laskentataulukkoon.

Vaihe 1: Yhteyden muodostaminen Excel-työkirjaan

1. Luo Excel-työkirja.
2. Valitse Tietojennoutaminen>>tiedostosta>työkirjasta.
3. Etsi Tuo tiedot -valintaikkunassa lataamasi Products.xlsx tiedosto ja valitse sitten Avaa.
4. Kaksoisnapsauta Siirtymistoiminto-ruudussaTuotteet-taulukkoa . Näkyviin tulee Power Query -editori.

Vaihe 2: Kyselyn vaiheiden tarkasteleminen

oletusarvoisesti Power Query lisää automaattisesti useita vaiheita. Lisätietoja on kyselyasetusruudunKäytössä olevat vaiheet -kohdan jokaisessa vaiheessa.

1. Napsauta Lähde-vaihetta hiiren kakkospainikkeella ja valitse Muokkaa asetuksia. Tämä vaihe luotiin, kun toit työkirjan.
2. Napsauta siirtymisvaihetta hiiren kakkospainikkeella ja valitse Muokkaa asetuksia. Tämä vaihe luotiin, kun valitsit taulukon Siirtyminen-valintaikkunasta.
3. Napsauta Muutettu tyyppi - vaihetta hiiren kakkospainikkeella ja valitse Muokkaa asetuksia. Tämän vaiheen on luonut Power Query joka johdatti kunkin sarakkeen tietotyypit. Saat koko kaavan näkyviin valitsemalla kaavarivin oikealla puolella olevan alanuolen.

Vaihe 3: Poista muut sarakkeet, jos haluat näyttää vain kiinnostavat sarakkeet

Tässä vaiheessa voit poistaa kaikki muut sarakkeet Tuotetunnus-, TuotteenNimi-, Tuoteryhmätunnus- ja MääräYksikköäKohden-sarakkeita lukuun ottamatta.

1. Valitse Tietojen esikatselu -kohdassaProductID-, ProductName-, CategoryID- ja QuantityPerUnit-sarakkeet (käytä näppäinyhdistelmää Ctrl+Napsautus tai Vaihto+Napsautus).
2. Valitse Poista sarakkeet>Poista muut sarakkeet.
   

Vaihe 4: Tuotekyselyn lataaminen

Tässä vaiheessa lataat Tuotteet-kyselyn Excel-laskentataulukkoon.

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

Yhteenveto: Power Query vaiheessa, jotka on luotu tehtävässä 1

Kun suoritat kyselytoimintoja Power Query, kyselyvaiheet luodaan ja luetellaan Kyselyasetukset-ruudunKäytössä olevat vaiheet -luettelossa. Kullakin kyselyn vaiheella on sitä vastaava Power Query -kaava, jota kutsutaan myös M-kieleksi. Lisätietoja Power Query kaavoista on artikkelissa Power Query kaavojen luominen Excelissä.

Tehtävä	Kyselyn vaihe	Kaava
Excel-työkirjan tuominen	Lähde	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Valitse Tuotteet-taulukko	Siirry	= Lähde{[Item="Tuotteet",Kind="Taulukko"]}[Tiedot]
Power Query tunnistaa sarakkeen tietotyypit automaattisesti	Muutettu tyyppi	= Table.TransformColumnTypes( Products_Table,{{"ProductID", Int64.Type}, {"ProductName", kirjoita text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})
Näytä vain halutut sarakkeet poistamalla muut sarakkeet	Muut sarakkeet poistettu	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

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

Tässä tehtävässä tuot tietoja Excel-työkirjaan Northwind OData -mallisyötteestä http://services.odata.org/Northwind/Northwind.svc, laajennat Order_Details taulukkoa, poistat sarakkeita, lasket rivisumman, muunnat Tilauspäivämäärän, ryhmittelet rivit tuotetunnuksen ja vuoden mukaan, nimeät kyselyn uudelleen ja poistat kyselyn lataamisen käytöstä Excel-työkirjaan.

Vaihe 1: Yhteyden muodostaminen OData-syötteeseen

1. Valitse Tietojen>noutaminen>muista lähteistä>OData-syötteestä.
2. Kirjoita OData-syöte-valintaikkunaan Northwind OData -syötteen URL.
3. Valitse OK.
4. Kaksoisnapsauta Siirtymistoiminto-ruudussaTilaukset-taulukkoa .

Vaihe 2: Laajenna Order_Details taulukko

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 rivit (Order_Details) yhdistetään riveiksi ensisijaisen taulukon (Tilaukset) kanssa.

Power Query yhteydessä olevan taulukon sisältävän sarakkeen solussa on tietue- tai taulukkoarvo. Näitä kutsutaan rakenteellisiksi sarakkeiksi. Tietue tarkoittaa yhtä yhdistettyä tietuetta ja edustaa yksi-yhteen-yhteyttä nykyisten tietojen tai ensisijaisen taulukon kanssa. Taulukko osoittaa liittyvän taulukon ja edustaa yksi-moni-yhteyttä nykyiseen tai ensisijaiseen taulukkoon. Rakenteellinen sarake edustaa suhdetta tietolähteessä, jossa on relaatiomalli. Esimerkiksi rakenteellinen sarake osoittaa entiteetin, jolla on viiteavainkytkentä OData-syötteessä tai viiteavainsuhteen SQL Server tietokannassa.

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

1. Vieritä tietojen esikatselussa vaakasuunnassa Order_Details-sarakkeeseen .

2. Valitse Order_Details-sarakkeessa laajennuskuvake ( ).

3. Avattavassa Laajenna-valikossa:

   1. Poista kaikki sarakkeet valitsemalla (Valitse kaikki sarakkeet ).

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

   3. Valitse OK.
      

      Huomautus

      Power Query voit laajentaa sarakkeesta linkitettyjä taulukoita ja koostaa linkitetyn taulukon sarakkeet ennen aihetaulukon tietojen laajentamista. Lisätietoa koostetoimintojen suorittamisesta on ohjeaiheessa Tietojen koostaminen sarakkeesta.

Vaihe 3: Poista muut sarakkeet, jos haluat näyttää vain kiinnostavat 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: Kunkin Order_Details rivin rivin summan laskeminen

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

1. Valitse tietojen esikatselussa taulukon kuvake ( ) esikatselun vasemmassa yläkulmassa.
2. Valitse Lisää mukautettu sarake.
3. Kirjoita Mukautettu sarake -valintaikkunan Mukautettu sarakekaava -ruutuun [Order_Details.Yksikköhinta] * [Order_Details.Määrä].
4. Kirjoita Uusi sarakkeen nimi -ruutuun Rivin summa.
5. Valitse OK.

Vaihe 5: OrderDate year -sarakkeen muuntaminen

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

1. Napsauta Tietojen esikatselu -kohdassa Tilauspäivä-saraketta hiiren kakkospainikkeella ja valitse Muunna>vuosi.

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

   1. kaksoisnapsauta Tilauspäivä-saraketta ja kirjoita Vuosi tai
   2. Right-Click Tilauspäivä-sarakkeessa , valitse Nimeä uudelleen ja kirjoita Vuosi.

Vaihe 6: Ryhmittele rivit tuotetunnuksen ja vuoden mukaan

1. Valitse Tietojen esikatselu -kohdassa Vuosi ja Order_Details.ProductID.

2. Right-Click ylätunniste 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.
   

Vaihe 7: Kyselyn nimeäminen uudelleen

Ennen kuin tuot myyntitiedot Exceliin, nimeä kysely uudelleen:

• Kirjoita Kyselyasetukset-ruudunNimi-ruutuunKokonaismyynti.

Tulokset: Tehtävän 2 lopullinen kysely

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

Yhteenveto: Power Query vaiheessa, jotka on luotu tehtävässä 2

Kun suoritat kyselytoimintoja Power Query, kyselyvaiheet luodaan ja luetellaan Kyselyasetukset-ruudunKäytössä olevat vaiheet -luettelossa. Kullakin kyselyn vaiheella on sitä vastaava Power Query -kaava, jota kutsutaan myös M-kieleksi. Lisätietoja Power Query kaavoista on artikkelissa Tietoja Power Query kaavoista.

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	= Lähde{[Name="Orders"]}[Tiedot]
Laajenna Tilaustiedot-taulukko	Laajenna Tilaustiedot	= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
Näytä vain halutut sarakkeet poistamalla muut sarakkeet	RemovedColumns	= Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "ShipPer"})
Laske jokaisen Tilaustiedot-rivin rivisumma	Mukautettu	= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Laajennettu Order_Details", "Rivi yhteensä", kukin [Order_Details.UnitPrice] * [Order_Details.Quantity])
Vaihda kuvaavampaan nimeen, Lne Total	Uudelleennimettyjä sarakkeita	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Muunna Tilauspäivä-sarake vuoden hahmontamiseksi	Poimittu vuosi	= Table.TransformColumns(#"Ryhmitetyt rivit",{{"Vuosi",Date.Year, Int64.Type}})
Muuta muotoon merkityksellisempiä nimiä, Tilauspäivä ja Vuosi	Uudelleennimettyjä sarakkeita 1	Table.RenameColumns (TransformedColumn;{{"Tilauspäivä"; "Vuosi"}})
Ryhmitä rivit tuotetunnuksen ja vuoden mukaan	GroupedRows	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

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

Power Queryn avulla voit yhdistellä useita kyselyjä yhdistämällä tai liittämällä ne. Yhdistä-toiminto suoritetaan kaikissa taulukkomuotoisissa Power Query -kyselyissä tietolähteestä riippumatta. Lisätietoja tietolähteiden yhdistämisestä on artikkelissa Monien kyselyjen yhdistäminen.

Tässä tehtävässä yhdistät Tuotteet- ja Kokonaismyynti-kyselytyhdistämiskyselyn ja Laajenna-toiminnon avulla ja lataat sitten Tuotekohtainen kokonaismyynti -kyselyn Excelin tietomalliin.

Vaihe 1: Yhdistä tuotetunnus kokonaismyyntikyselyksi

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

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

3. Valitse Yhdistä-valintaikkunassaEnsisijaiseksi taulukoksi Tuotteet ja valitse yhdistämiseen liittyväksi toissijaiseksi tai liittyväksi kyselyksi Kokonaismyynti . Kokonaismyynnistä tulee uusi rakenteellinen 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 tietosuojatasoista on artikkelissa Tietosuojatasojen määrittäminen.

   

Tulos

Yhdistä-toiminto luo kyselyn. Kyselyn tulos sisältää kaikki ensisijaisen taulukon (Tuotteet) sarakkeet ja yksittäisen taulukkorakenteisen sarakkeen liittyvään taulukkoon (Kokonaismyynti). Valitse Laajenna-kuvake , jos haluat lisätä uusia sarakkeita ensisijaiseen taulukkoon toissijaisesta tai siihen liittyvästä taulukosta.

Vaihe 2: Yhdistetyn sarakkeen laajentaminen

Tässä vaiheessa laajennat yhdistetyn sarakkeen nimellä NewColumn ja luot kaksi uutta saraketta Tuotteet-kyselyyn : Vuosi ja Kokonaismyynti.

1. Valitse Tietojen esikatselu -kohdassa Laajenna-kuvake ( ) NewColumn-kohdan vierestä.

2. Avattavassa Laajenna-luettelossa :

   1. Poista 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 ja minä vuosina tuotteet ovat saaneet eniten myyntiä, valitse Lajittele laskevastikokonaismyynnin mukaan.

5. Nimeä uudelleen kysely nimellä Tuotekohtainen kokonaismyynti.

Tulos

Vaihe 3: Tuotekohtaisen kokonaismyynnin kyselyn lataaminen Excel-tietomalliin

Tässä vaiheessa kysely ladataan Excelin tietomalliin kyselyn tulokseen yhdistetyn raportin muodostamiseksi. Kun olet ladannut tiedot Excelin tietomalliin, voit käyttää Power Pivotia tietojen analysoinnin edistämiseen.

1. Valitse Aloitus>Sulje & Lataa.
2. Varmista Tuo tiedot -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ä käytetään Power Pivot -mallissa. Lisäksi kyselyn muutokset muokkaavat ja päivittävät tuloksena olevaa taulukkoa tietomallissa.

Yhteenveto: Power Query vaiheessa, jotka on luotu tehtävässä 3

Kun suoritat yhdistämiskyselytoimintoja Power Query, kyselyvaiheet luodaan ja luetellaan Kyselyasetukset-ruudunKäytössä olevat vaiheet -luettelossa. Kullakin kyselyn vaiheella on sitä vastaava Power Query -kaava, jota kutsutaan myös M-kieleksi. Lisätietoja Power Query kaavoista on artikkelissa Tietoja Power Query kaavoista.

Tehtävä	Kyselyn vaihe	Kaava
Yhdistä tuotetunnus Kokonaismyynti-kyselyyn	Lähde (Yhdistä-toiminnon tietolähde)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Laajenna Yhdistä-sarake	Laajennettu kokonaismyynti	= Table.ExpandTableColumn(Lähde, "Kokonaismyynti", {"Vuosi", "Kokonaismyynti"}, {"Kokonaismyynti.vuosi", "Kokonaismyynti.Kokonaismyynti"})
Kahden sarakkeen nimeäminen uudelleen	Uudelleennimettyjä sarakkeita	= Table.RenameColumns(#"Laajennettu kokonaismyynti",{{"Kokonaismyynti.vuosi","Vuosi"}, {"Kokonaismyynti.Kokonaismyynti", "Kokonaismyynti"}})
Myynnin kokonaismäärän lajitteleminen nousevassa järjestyksessä	Lajitellut rivit	= Table.Sort(#"Uudelleennimettyjä sarakkeita",{{"Kokonaismyynti",Tilaus.Nouseva}})

Katso myös

Microsoft Power Query for Excelin ohje