Mitmest andmeallikast pärinevate andmete kombineerimine (Power Query)

NB! :  See artikkel on masintõlgitud, vaadake lahtiütlust. Selle artikli ingliskeelse versiooni leiate aadressilt (viiteks).

Märkus. : Power Query kannab rakenduses Excel 2016 nime Saamine ja transformeerimine. Siin esitatud teave kehtib mõlema kohta. Lisateavet leiate teemast Saamine ja transformeerimine rakenduses Excel 2016.

Selles õpetuses kuvatakse Power Query Päringuredaktor abil andmete importimine kohalikud Exceli faili, mis sisaldab tooteteave ja OData kanali, mis sisaldab toote tellimuse teave. Teisendamiseks ja koondamine toimingute ja Total Sales per Product ja aasta aruande nii allikatest pärinevate andmete kombineerimine.

Õppetüki sooritamiseks vajate töövihikutTooted ja tellimused. Tippige dialoogiboksis Nimega salvestamine faili nimeks Tooted ja tellimused.xlsx.

Selle õppetüki teemad

Ülesanne 1: toodete importimine Exceli töövihikusse

1. juhis: looge ühendus Exceli töövihikuga

2. juhis: määrake esimene rida tabeli veerupäisteks

3. juhis: eemaldage muud veerud, et kuvada ainult olulised veerud

Power Query etappide loomine

4. juhis: importige tootepäring

Ülesanne 2: tellimisandmete importimine OData kanalist

1. juhis: looge ühendus OData kanaliga

2. juhis: laiendage tabel Order_Details

Tabeli Order_Details lingi laiendamine

3. juhis: eemaldage muud veerud, et kuvada ainult olulised veerud

Valitud veergude eemaldamine

4. juhis: arvutage rea kogusumma iga tabeli Order_Details rea kohta

Rea kogusumma arvutamine iga tabeli Order_Details rea kohta

5. juhis: teisendage OrderDate veerg aasta veeruks

6. juhis: rühmitage read väärtuste ProductID ja Year alusel

7. juhis: nimetage päring ümber

Lõplikud päringutulemid

Power Query etappide loomine

8. juhis: keelake päringu allalaadimine Exceli töövihikusse

Päringu allalaadimise keelamine

Ülesanne 3: päringute „Products“ ja „Total Sales“ kombineerimine

1. juhis: ühendage ProductID päringusse „Total Sales“

2. juhis: laiendage ühendamisveerg

Tabeli NewColumn lingi laiendamine

Power Query etappide loomine

3. juhis: laadige päring „Total Sales per Product“ Exceli andmemudelisse

Päringu „Total Sales per Product“ laadimine Exceli andmemudelisse

Lõplik päring „Total Sales per Product“

Ülesanne 1: toodete importimine Exceli töövihikusse

Selles ülesandes tuleb teil importida tooted failist Tooted ja tellimused.xlsx Exceli töövihikusse.

1. juhis: looge ühendus Exceli töövihikuga

  1. Looge Exceli töövihik.

  2. Klõpsake menüüs POWER QUERY nuppu Failist ja valige siis Excelist.

  3. Dialoogiboksis Excel otsige sirvides üles või tippige faili Tooted ja tellimused.xlsx tee, et fail importida või sellega linkida.

  4. Topeltklõpsake paanil Navigaator töölehte Products (Tooted) või klõpsake töölehte Products (Tooted) ja siis nuppu Muuda päringut. Päringu redigeerimisel või uue andmeallikaga ühenduse loomisel kuvatakse Päringuredaktori aken.

    Märkus. : Selle artikli lõpus on toodud päringuredaktori kuvamise juhiseid sisaldav ülevaatlik video.

2. juhis: määrake esimene rida tabeli veerupäisteks

Paanil Päringu eelvaade ei sisalda tabeli esimene rida tabeli veerunimesid. Esimese rea tabeli veerupäisteks määramiseks tehke järgmist.

  1. Klõpsake andmete eelvaate vasakpoolses ülanurgas tabeliikooni ( Tabeliikoon ).

  2. Klõpsake nuppu Kasuta esimest rida päistena.

Esimese rea määramine tabeli veerupäisteks

3. juhis: eemaldage muud veerud, et kuvada ainult olulised veerud

Selles etapis tuleb teil eemaldada kõik veerud peale järgmiste: ProductID (Toote ID), ProductName (Toote nimi), CategoryID (Kategooria ID) ja QuantityPerUnit (Ühiku kogus).

  1. Valige paanil Päringu eelvaade veerud ProductID, ProductName, CategoryID ja QuantityPerUnit (klahvikombinatsiooniga Ctrl+Click või Shift+Click).

  2. Valige päringuredaktori menüülindil Eemalda veerud > Eemalda muud veerud või paremklõpsake soovitud veerupäist ja klõpsake siis käsku Eemalda muud veerud.

    Muude veergude peitmine

Power Query etappide loomine

Päringutegevuste sooritamisel rakenduses Power Query luuakse järjest päringuetapid, mis on ära toodud paani Päringu sätted loendis RAKENDATUD ETAPID. Igale päringuetapile vastab Power Query valem, mida tuntakse ka „M“-keelena. Power Query valemikeele kohta lisateabe saamiseks lugege artiklit Lisateavet Power Query valemite kohta.

Ülesanne

Päringu etapp

Valem

Exceli töövihikuga ühenduse loomine

Allikas

Source{[Name="Products"]}[Data]

Esimese rea määramine tabeli veerupäisteks

FirstRowAsHeader

Table.PromoteHeaders

(Products)

Muude veergude eemaldamine ainult oluliste veergude kuvamiseks

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

4. juhis: importige tootepäring

Selles etapis tuleb teil importida päring Products (Tooted) oma Exceli töövihikusse.

  1. Klõpsake päringuredaktori menüüribal nuppu Rakenda ja sule. Tulemid kuvatakse uuel Exceli töölehel.

Lehe algusse

Ülesanne 2: tellimisandmete importimine OData kanalist

Selles ülesandes impordite andmed Exceli töövihikusse Northwindi OData näidiskanalist http://services.odata.org/Northwind/Northwind.svc.

1. juhis: looge ühendus OData kanaliga

  1. Klõpsake menüüs POWER QUERY nuppu Muust allikast ja siis käsku OData kanalist.

  2. Sisestage dialoogiboksis OData kanal Northwindi OData kanali URL.

  3. Klõpsake nuppu OK.

  4. Topeltklõpsake paanil Navigaator tabelit Orders (Tellimused) või klõpsake tabelit Orders (Tellimused) ja siis nuppu Muuda.

Märkus. :  Kursori viimisel tabelile kuvatakse hüpikuna tabeli eelvaade.

Kursori viimine andmeallikale

2. juhis: laiendage tabel Order_Details

Selles etapis tuleb teil laiendada tabeliga Orders (Tellimused) seotud tabel Order_Details (Tellimuse üksikasjad), et koondada tabeli Order_Details veergude ProductID (Toote ID), UnitPrice (Ühiku hind) ja Quantity (Kogus) andmed tabelisse Orders (Tellimused). Toiming Laienda koondab seostuva tabeli veerud sihttabelisse. Päringu käivitamisel kombineeritakse seostuva tabeli (Order_Details) read sihttabeli (Orders) ridadega.

Rakenduses Power Query on seostuva tabeli linki sisaldavas veerus kas kirje link või tabeli link. Kirje link viib ühele seostuvale kirjele ning tähistab üks-ühele seost sihttabeliga. Tabeli link viib seostuvale tabelile ning tähistab üks-mitmele seost sihttabeliga. Link kujutab endast relatsioonmudelis andmeallika navigeerimisatribuute. OData kanali korral on navigeerimisatribuudid välisvõtmeseosega olem. Andmebaasis (nt SQL Serveris) on navigeerimisatribuudid andmebaasi välisvõtmeseosed.

Tabeli Order_Details lingi laiendamine

Pärast tabeli Order_Details (Tellimuse üksikasjad) laiendamist lisatakse tabelisse Orders (Tellimused) kolm uut veergu ja täiendavad read – üks rida iga pesastatud või seostuva tabeli rea kohta.

  1. Liikuge paanil Päringu eelvaade veerule Order_Details (Tellimuse üksikasjad).

  2. Klõpsake veerus Order_Details laiendusikooni ( Laienda ).

  3. Tehke rippmenüüs Laienda järgmist.

    1. Kõigi veergude valiku tühjendamiseks klõpsake väärtust (Vali kõik veerud).

    2. Klõpsake veerge ProductID (Toote ID), UnitPrice (Ühiku hind) ja Quantity (Kogus).

    3. Klõpsake nuppu OK.

      Tabeli Order_Details lingi laiendamine

      Märkus. : Rakenduses Power Query saate lingitud tabelid veergudeks laiendada. Samuti saate lingitud tabeli veergudes enne sihttabeli andmete laiendamist teha mitmesuguseid liitväärtuste toiminguid. Liitväärtustega seotud toimingute kohta leiate lisateavet artiklist Andmete liitmine veerus.

3. juhis: eemaldage muud veerud, et kuvada ainult olulised veerud

Selles etapis tuleb teil eemaldada kõik veerud peale järgmiste: OrderDate (Tellimiskuupäev), ProductID (Toote ID), UnitPrice (Ühiku hind) ja Quantity (Kogus). Eelmises ülesandes kasutasite selleks käsku Eemalda muud veerud. Selles ülesandes tuleb teil aga eemaldada valitud veerud.

Valitud veergude eemaldamine

  1. Valige paanil Päringu eelvaade kõik veerud.

    1. Klõpsake esimest veergu (OrderID (Tellimuse ID)).

    2. Klõpsake tõstuklahvi (Shift) all hoides viimast veergu (Shipper (Tarnija)).

    3. Klõpsake juhtklahvi (Ctrl) all hoides veerge OrderDate (Tellimiskuupäev), Order_Details.ProductID (Tellimuse_üksikasjad.TooteID), Order_Details.UnitPrice (Tellimuse_üksikasjad.ÜhikuHind) ja Order_Details.Quantity (Tellimuse_üksikasjad.Kogus).

  2. Paremklõpsake mõnda valitud veerupäist ja klõpsake käsku Eemalda veerud.

4. juhis: arvutage rea kogusumma iga tabeli Order_Details rea kohta

Selles juhises tuleb teil luua kohandatud veerg, et arvutada tabeli Order_Details (Tellimuse_üksikasjad) iga rea kohta rea kogusumma.

Rea kogusumma arvutamine iga tabeli Order_Details rea kohta

  1. Klõpsake paanil Päringu eelvaade eelvaate vasakpoolses ülanurgas tabeliikooni ( Tabeliikoon ).

  2. Valige Sisesta veerg > Kohandatud.

  3. Sisestage dialoogiboksis Kohandatud veeru sisestamine väljale Kohandatud veeru valem järgmine valem: [Order_Details.UnitPrice] * [Order_Details.Quantity] ([Tellimuse_üksikasjad.ÜhikuHind] * [Tellimuse_üksikasjad.Kogus]).

  4. Sisestage tekstiväljale Uus veeru nimi nimi Rea summa.

  5. Klõpsake nuppu OK.

Rea kogusumma arvutamine iga tabeli Order_Details rea kohta

5. juhis: teisendage OrderDate veerg aasta veeruks

Selles etapis tuleb teil veerg OrderDate (Tellimiskuupäev) teisendada tellimiskuupäeva aasta renderdamiseks.

  1. Paremklõpsake paanil Eelvaade veergu OrderDate (Tellimiskuupäev) ja valige siis Transformatsioon > Aasta.

  2. Nimetage veerg OrderDate (Tellimiskuupäev) ümber veeruks Year.

    1. Topeltklõpsake veergu OrderDate (Tellimiskuupäev) ja sisestage Year või

    2. Paremklõpsake veergu OrderDate (Tellimiskuupäev), klõpsake käsku Nimeta ümber ja sisestage Year.

6. juhis: rühmitage read väärtuste ProductID ja Year alusel

  1. Valige paanil Päringu eelvaade veerud Year (Aasta) ja Order_Details.ProductID (Tellimuse_üksikasjad.TooteID).

  2. Paremklõpsake ühte päistest ja klõpsake siis käsku Rühmita.

  3. Tehke dialoogiboksis Rühmitusalus järgmist.

    1. Sisestage tekstiväljale Uus veeru nimi nimi Müük kokku.

    2. Valige rippmenüüst Toiming käsk Summa.

    3. Valige rippmenüüst Veerg väärtus Line Total.

  4. Klõpsake nuppu OK.

    Rühmitusaluse dialoogiboks liitväärtustega seotud toimingute jaoks

7. juhis: nimetage päring ümber

Enne müügiandmete Excelisse importimist pange päringule nimeks Total Sales.

  1. Sisestage paanil Päringu sätted tekstiväljale Nimi uue nimena Total Sales.

Lõplikud päringutulemid

Pärast iga etapi sooritamist on teil Northwindi OData kanali jaoks olemas päring „Total Sales“.

Total Sales

Power Query etappide loomine

Päringutegevuste sooritamisel rakenduses Power Query luuakse järjest päringuetapid, mis on ära toodud paani Päringu sätted loendis RAKENDATUD ETAPID. Igale päringuetapile vastab Power Query valem, mida tuntakse ka „M“-keelena. Power Query valemikeele kohta lisateabe saamiseks lugege artiklit Lisateavet Power Query valemite kohta.

Ülesanne

Päringu etapp

Valem

OData kanaliga ühenduse loomine

Allikas

Source{[Name="Orders"]}[Data]

Tabeli Order_Details laiendamine

Tabeli Order_Details laiendamine

Table.ExpandTableColumn

(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})

Muude veergude eemaldamine ainult oluliste veergude kuvamiseks

RemovedColumns

Table.RemoveColumns

(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})

Rea kogusumma arvutamine iga tabeli Order_Details rea kohta

InsertedColumns

Table.AddColumn

(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

Veeru OrderDate teisendamine aasta renderdamiseks

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Custom", "Line Total"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}})

RenamedColumns1

Table.RenameColumns

(TransformedColumn,{{"OrderDate", "Year"}})

Ridade rühmitamine veergude ProductID ja Year alusel

GroupedRows

Table.Group
(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", iga List.Sum([Line Total]), tippige arv}})

8. juhis: keelake päringu allalaadimine Exceli töövihikusse

Kuna päring Total Sales ei tähista lõplikku aruannet Total Sales per Product and Year, tuleb päringu allalaadimine Exceli töövihikusse keelata. Kui sätte Laadi töölehele väärtus paanil Päringud sätted on Väljas, ei laadita selle päringu tulemit alla, kuid päringu saab soovitud tulemi koostamiseks endiselt teiste päringutega kombineerida. Järgmises ülesandes õpite seda päringut kombineerima päringuga Products (Tooted).

Päringu allalaadimise keelamine

  1. Tühjendage paanil Päringu sätted ruut Laadi töölehele.

  2. Klõpsake päringuredaktori menüüribal nuppu Rakenda ja sule. Paanil Töövihiku päringud kuvatakse päringus Total Sales (Müük kokku) teade Laadimine on keelatud.

    Päringu allalaadimise keelamine

Lehe algusse

Ülesanne 3: päringute „Products“ ja „Total Sales“ kombineerimine

Rakenduses Power Query saate mitu päringut omavahel kombineerida need ühendades või lisades ühe päringu teise päringu lõppu. Toimingut Ühenda saab kasutada suvalises tabelina esitatud Power Query päringus sõltumata sellest, millisest andmeallikast andmed pärinevad. Andmeallikate kombineerimise kohta leiate lisateavet artiklist Mitme päringu kombineerimine.

Selles ülesandes tuleb teil omavahel kombineerida päringud Products (Tooted) ja Total Sales (Müük kokku), kasutades selleks päringuetappe Ühendamine ja Laiendamine.

1. juhis: ühendage ProductID päringusse „Total Sales“

  1. Liikuge Exceli töövihikus lehel 2 päringule Products (Tooted).

  2. Klõpsake menüüs PÄRING nuppu Ühenda.

  3. Valige dialoogiboksis Ühendamine esmaseks tabeliks Products (Tooted). Seejärel valige teiseks või seostuvaks ühendatavaks päringuks Total Sales. Total Sales muutub uueks laiendatavaks veeruks.

  4. Veeru Total Sales (Müük kokku) vastendamiseks veeruga Products (Tooted) ProductID (Toote ID) alusel valige tabelist Products (Tooted) veerg ProductID (Toote ID) ja seejärel tabelist Total Sales (Müük kokku) veerg Order_Details.ProductID (Tellimuse_üksikasjad.TooteID).

  5. Tehke dialoogiboksis Privaatsustasemed järgmist.

    1. Valige mõlema andmeallika privaatsuse eraldamise tasemeks Organisatsioonisisene.

    2. Klõpsake nuppu Salvesta.

  6. Klõpsake nuppu OK.

    Turbeteade : Privaatsustasemed ei luba kasutajal eksikombel andmeid kombineerida mitmest andmeallikast, mis võivad olla privaatsed või kuuluda ettevõttele. Sõltuvalt päringust võib kasutaja kogemata saata andmeid privaatsest andmeallikast mõnda teise andmeallikasse, mis võib olla pahatahtlik. Power Query analüüsib iga andmeallikat ja liigitab selle ühte määratletud privaatsustasemetest: Avalik, Organisatsioonisisene ja Privaatne. Privaatsustasemete kohta leiate lisateavet artiklist Privaatsustasemed.

    Dialoogiboks Ühendamine

Pärast nupu OK klõpsamist loob toiming Ühenda päringu. Päringu tulem sisaldab kõiki veerge esmasest tabelist (Products (Tooted)) ja ühte veergu navigeerimislingiga seostuvale tabelile (Total Sales (Müük kokku)). Toiming Laienda lisab esmasesse ehk sihttabelisse seostuvast tabelist uusi veerge.

Lõplik ühendamine

2. juhis: laiendage ühendamisveerg

Selles etapis tuleb teil ühendatud veerg nimega NewColumn (Uus veerg) laiendada, et luua päringus Products (Tooted) kaks uut veergu: Year (Aasta) ja Total Sales (Müük kokku).

Tabeli NewColumn lingi laiendamine

  1. Klõpsake ruudustikul Päringu eelvaade veeru NewColumn (Uus veerg) laiendamise ikooni ( Laienda ).

  2. Tehke rippmenüüs Laienda järgmist.

    1. Kõigi veergude valiku tühjendamiseks klõpsake väärtust (Vali kõik veerud).

    2. Klõpsake veerge Year (Aasta) ja Total Sales (Müük kokku).

    3. Klõpsake nuppu OK.

  3. Nimetage need kaks veergu ümber veergudeks Year ja Total Sales.

  4. Sortige laskuvas järjestuses, võttes aluseks veeru Total Sales, et selgitada välja, millised tooted on kõige paremaid müügitulemusi näidanud ja millised olid kõige edukamad aastad.

  5. Nimetage päring ümber päringuks Total Sales per Product.

Tabelilingi laiendamine

Power Query etappide loomine

Toiminguga Ühendamine seotud päringutegevuste sooritamisel rakenduses Power Query luuakse järjest päringuetapid, mis on ära toodud paani Päringu sätted loendis RAKENDATUD ETAPID. Igale päringuetapile vastab Power Query valem, mida tuntakse ka „M“-keelena. Power Query valemikeele kohta lisateabe saamiseks lugege artiklit Lisateavet Power Query valemite kohta.

Ülesanne

Päringu etapp

Valem

ProductID ühendamine päringusse „Total Sales“

Allikas (andmeallikas toimingu Ühenda jaoks)

Table.NestedJoin

(Products,{"ProductID"},#"Total Sales",{"Order_Details.ProductID"},"NewColumn")

Ühendamisveeru laiendamine

ExpandNewColumn

Table.ExpandTableColumn

(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})

RenamedColumns

Table.RenameColumns

(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})

SortedRows

Table.Sort

(RenamedColumns,{{"Total Sales", Order.Descending}})

3. juhis: laadige päring „Total Sales per Product“ Exceli andmemudelisse

Selles etapis tuleb teil keelata suvand Laadi töölehele ja laadida päring päringutulemiga ühendatud aruande koostamiseks Exceli andmemudelisse. Lisaks päringutulemite laadimisele Exceli töölehele lubab Power Query teil päringutulemi laadida ka Exceli andmemudelisse. Pärast andmete laadimist Exceli andmemudelisse saate täpsema andmeanalüüsi jaoks kasutada lisandmooduleid Power Pivot ja Power View.

Päringu „Total Sales per Product“ laadimine Exceli andmemudelisse

  1. Tühjendage paanil Päringu sätted ruut Laadi töölehele ja märkige ruut Laadi andmemudelisse.

  2. Päringu laadimiseks Exceli andmemudelisse klõpsake nuppu Rakenda ja sule.

Exceli andmemudeli laadimine

Lõplik päring „Total Sales per Product“

Kui olete kõik toimingud ära teinud, oletegi saanud päringu Total Sales per Product (Müük kokku toote kohta), mis kombineerib andmeid failist Tooted ja tellimused.xlsx ning Northwindi OData kanalist. Selle päringu saab rakendada Power Pivoti mudelile. Lisaks muudavad ja värskendavad Power Query’s päringusse tehtud muudatused ka tulemiks saadud tabelit Power Pivot mudelis.

Lehe algusse

Märkus. : Päringuredaktor kuvatakse ainult siis, kui te Power Query abil päringu laadite, seda redigeerite või uue päringu loote. Järgmises videos näidatakse päringuredaktori akent, mis kuvatakse pärast Exceli päringu redigeerimist. Kui soovite vaadata päringuredaktorit ilma töövihiku päringut laadimata või redigeerimata, klõpsake lindimenüü Power Query jaotises Välisandmete toomine nuppe Muudest allikatest > Tühi päring. Järgmises videos näidatakse ühte võimalust päringuredaktori kuvamiseks.

Kuidas Excelis Päringuredaktorit vaadata

Märkus. : Masintõlke lahtiütlus. Selle artikli tõlkis arvutisüsteem ilma inimese sekkumiseta. Microsoft pakub selliseid masintõlkeid, et inglise keelt mittekõnelevad kasutajad saaksid vaadata sisu Microsofti toodete, teenuste ja tehnoloogiate kohta. Kuna artikkel on masintõlgitud, võib see sisaldada sõnavara-, süntaksi- või grammatikavigu.

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×