Tässä opetusohjelmassa voit power queryn kyselyeditorin avulla tuoda tietoja paikallisesta Excel, joka sisältää tuotetietoja ja tuotteen tilaustietoja sisältävästä OData-syötteestä. Voit suorittaa muunnos- ja koostevaiheita sekä yhdistää molempien lähteiden tietoja tuote- ja vuosikohtainen kokonaismyynti -raportin tuottamiseksi.   

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

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

Vaihe 1: Yhdistä Excel-työkirjaan

  1. Luo Excel-työkirja.

  2. Valitse Tiedot> Hae>->työkirjasta.

  3. Etsi Tuo tiedot -valintaikkunassa lataamasi Products.xlsx ja valitse sitten Avaa.

  4. Kaksoisnapsauta Siirtymistoiminto-ruudussaTuotteet-taulukkoa.Power Query -editori tulee näkyviin.

Vaihe 2: Kyselyn vaiheiden tutkiminen

Oletusarvoisesti Power Query lisää automaattisesti useita vaiheita käyttömukavuuden täksi. Saat lisätietoja tutustumaan kuhunkin Kyselyruudun Käytössä olevat vaiheet Asetukset -ruutuun.

  1. Napsauta Lähde-vaihetta hiiren kakkospainikkeella ja valitse Asetukset. Tämä vaihe luotiin, kun toit työkirjan.

  2. Napsauta siirtymisvaihetta hiiren kakkospainikkeella ja valitse Muokkaa Asetukset. Tämä vaihe luotiin, kun valitsit taulukon Siirtyminen-valintaikkunasta.

  3. Napsauta Muutettu tyyppi -vaihetta hiiren kakkospainikkeella ja valitse Asetukset. Tämän vaiheen loi Power Query, joka päätteli kunkin sarakkeen tietotyypit. Näet koko kaavan valitsemalla kaavarivillä oikealla olevan alanuolen.

Vaihe 3: Näytä vain halutut sarakkeet poistamalla muut 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 -kohdassaTuotetunnus-,TuotteenNimi-,LuokanTunnus-ja MääräYksikköäYksikköä-sarakkeet (näppäinyhdistelmällä Ctrl+napsauta tai Vaihto+napsautuksella).

  2. Valitse Poista sarakkeet > muut sarakkeet.

    Piilota muut sarakkeet

Vaihe 4: Lataa tuotekysely

Tässä vaiheessa voit ladata Tuotteet-kyselyn Excel laskentataulukkoon.

  • Valitse Aloitus>Sulje & Lataa. Kysely tulee näkyviin uuteen Excel.

Yhteenveto: Power Queryn vaiheet, jotka on luotu tehtävässä 1

Kun suoritat kyselytoimia Power Queryssä, kyselyvaiheet luodaan ja Asetukset-ruudussa Kä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 Excel.

Tehtävä

Kyselyn vaihe

Kaava

Työkirjan Excel tuominen

Lähde

= Excel. Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)

Valitse Tuotteet-taulukko

Siirry

= Lähde{[Kohde="Tuotteet",Kind="Taulukko"]}[Tiedot]

Power Query tunnistaa saraketietotyypit automaattisesti

Muutettu tyyppi

= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", type 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"})

Tässä tehtävässä tiedot tuodaan Excel-työkirjaan Northwind OData -esimerkkisyötteestä http://services.odata.org/Northwind/Northwind.svc:ssä,laajennetaan Order_Details-taulukkoa, poistetaan sarakkeita, lasketaan rivisumma, muunnetaan tilauspäivä, ryhmitellä rivit tuotetunnusten ja vuoden mukaan, nimetä kysely uudelleen ja poistaa kyselyn lataamisen käytöstä Excel-työkirjaan.

Vaihe 1: Näyttöyhteys OData-syötteeseen

  1. Valitse Tietolähde>hae> muista lähteistä tai > 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 Tilaustiedot-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 riveihin ensisijaisen taulukon (Tilaukset)kanssa.

Power Queryssä liittyvän taulukon sisältävällä sarakkeesta on solussa arvo Tietuetai Taulukko. Näitä kutsutaan jäsennetyiksi sarakkeiksi. Tietue osoittaa yksittäisen liittyvän tietueen ja edustaa yksi-yhteen-yhteyttä nykyisten tietojen tai ensisijaisen taulukon kanssa. Taulukko osoittaa liittyvän taulukon ja edustaa yksi-moneen-yhteyttä nykyiseen tai ensisijaiseen taulukkoon. Jäsennetty sarake edustaa yhteyttä tietolähteessä, jossa on relaatiomalli. Esimerkiksi jäsennetty sarake osoittaa kohteen, jossa on viiteavainliitos OData-syötteessä tai viiteavainsuhteessa SQL Server tietokannassa.

Tilaustiedot-taulukon laajentamisen jälkeen 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 esikatselu-näkymässä vaakasuunnassa Order_Details sarakkeeseen.

  2. Valitse Order_Details-sarakkeessa laajennuskuvake (Laajenna).

  3. Avattavassa Laajenna-valikossa:

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

    2. Valitse Tuotetunnus,Yksikköhintaja Määrä.

    3. Valitse OK.

      Laajenna Tilaustiedot-taulukon linkki

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

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 esikatselu-kohdassa seuraavat sarakkeet:

    1. Valitse ensimmäinen tilaustunnus-sarake.

    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 Tilaustiedot-rivin rivisumma

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

  1. Valitse Tietojen esikatselu-kohdassa taulukkokuvake (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 Uuden sarakkeen nimi -ruutuunRivisumma.

  5. Valitse OK.

Laske jokaisen Tilaustiedot-rivin rivisumma

Vaihe 5: Muunna Tilauspäivä-vuosisarake

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 Nimeä uudelleenja kirjoita Vuosi.

Vaihe 6: Ryhmitä rivit tuotetunnuksen ja vuoden mukaan

  1. Valitse Tietojen esikatselu-kohdassa Vuosija Order_Details.Tuotetunnus.

  2. Right-Click jokin ylätunnisteista ja valitse Ryhmittely mukaan.

  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.

    Koostetoimintojen Ryhmittelyperuste-valintaikkuna

Vaihe 7: Nimeä kysely uudelleen

Ennen kuin tuot myyntitiedot Excel, nimeä kysely uudelleen:

  • Kirjoita kyselyruudunAsetukset-ruutuunKokonaismyynti.

Tulokset: Tehtävän 2 lopullinen kysely

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

Kokonaismyynti

Yhteenveto: Power Queryn vaiheet, jotka on luotu tehtävässä 2 

Kun suoritat kyselytoimia Power Queryssä, kyselyvaiheet luodaan ja Asetukset-ruudussa Kä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{[Nimi="Tilaukset"]}[Tiedot]

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(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipregion"})

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", jokainen [Order_Details.Yksikköhinta] * [Order_Details.Määrä])

Merkityksellisemmän nimen muuttaminen, Kokonaissumma

Uudelleennimetyt sarakkeet

= Table.RenameColumns(InsertedCustom,{{"Custom", "Rivisumma"}})

Muunna Tilauspäivä-sarake vuoden hahmontamiseksi

Poimittu vuosi

= Table.TransformColumns(#"Ryhmitellyt rivit",{{"Vuosi", Date.Year, Int64.Type}})

Muuta: 

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, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

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ä Tuotteet- ja Kokonaismyynti-kyselyt yhdistetään yhdistämiskyselyn ja Laajenna-toiminnon avulla ja ladataan sitten Tuotekohtainen kokonaismyynti -kysely Excel tietomalliin.

Vaihe 1: Yhdistä tuotetunnus Kokonaismyynti-kyselyyn

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

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

  3. Valitse Yhdistä-valintaikkunassaensisijaiseksi taulukoksi Tuotteet ja valitse Yhdistämiseen liittyväksi toissijaiseksi tai liittyväksi kyselyksi Kokonaismyynti. Kokonaismyynti-sarakkeesta 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.

    Tietoturvahuomautus:  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 kohdassa Yksityisyystasojen määrittäminen.

    Yhdistä-valintaikkuna

Tulos

Yhdistä-toiminto luo kyselyn. Kyselyn tulos sisältää kaikki ensisijaisen taulukon(Tuotteet) sarakkeet ja yhden jäsennetyn taulukkosarakkeen liittyvään taulukkoon(Kokonaismyynti). Lisää uusia sarakkeita toissijaisesta tai siihen liittyvästä taulukosta ensisijaiseen taulukkoon valitsemalla Laajenna-kuvake.

Yhdistä lopullinen

Vaihe 2: Yhdistetyn sarakkeen laajentaminen

Tässä vaiheessa voit laajentaa uuden sarakkeen nimellä UusiSarake ja luoda tuotteet-kyselyyn kaksi uutta saraketta: Vuosi ja Kokonaismyynti.

  1. Valitse Tietojen esikatselussaLaajenna-kuvake (Laajenna) UusiSarake-kohdan vieressä.

  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 mihin vuoteen tuotteet ovat saaneet eniten myyntiä, valitse Lajittele laskevassa järjestyksessä kokonaismyynnin mukaan.

  5. Nimeä uudelleen kysely nimellä Tuotekohtainen kokonaismyynti.

Tulos

Laajenna taulukon linkki

Vaihe 3: Lataa Tuotekohtainen kokonaismyynti -kysely Excel-tietomalliin

Tässä vaiheessa kysely ladataan kyselyyn Excel tietomalliin,jotta kyselytulokseen yhdistetty raportti voidaan muodostaa. Kun olet ladannut tiedot Excel tietomalliin, voit käyttää Power Pivotia tietojen analysointiin.

  1. Valitse Aloitus> sulje & Lataa.

  2. Varmista Tietojen tuominen -valintaikkunassa, että valitset Lisää nämä tiedot tietomalliin. Jos haluat lisätietoja tämän valintaikkunan käyttämisestä, valitse kysymysmerkki (?).

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äivitet täten tuloksena syntyvää taulukkoa tietomallissa.

Yhteenveto: Power Queryn vaiheet, jotka on luotu tehtävässä 3

Kun suoritat yhdistämiskyselyn toimintoja Power Queryssä, kyselyvaiheet luodaan ja luetteloidaan Kyselykysely Asetukset ruudun Kä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(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"}})

Kokonaismyynnin lajitteleminen nousevaan järjestykseen

Lajiteltuja rivejä

= Table.Sort(#"Uudelleennimetyt sarakkeet",{{"Kokonaismyynti", Tilaus.Nouseva}})

Katso myös

Power Query for Excel ohje

Tarvitsetko lisäohjeita?

Kehitä taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Microsoft Insider-käyttäjille

Oliko näistä tiedoista hyötyä?

Kuinka tyytyväinen olet käännöksen laatuun?

Mikä vaikutti kokemukseesi?

Haluatko antaa lisäpalautetta? (Valinnainen)

Kiitos palautteestasi.

×