Mitmest andmeallikast pärinevate andmete kombineerimine (Power Query)

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

Selles õpikus saate Power Query redaktori kaudu andmeid importida kohalikust Exceli failist, mis sisaldab teavet toote kohta ja OData kanalilt, mis sisaldab teavet tellimuse kohta. Lisaks teisendamis- ja koondamistoimingutele tuleb teil mõlemast allikast pärit andmed aruande Müük kokku toote ja aasta kohta loomiseks kombineerida.

Selle õpetuse tegemiseks on vaja töövihikut tooted ja tellimused . Tippige dialoogiboksis Nimega salvestamine faili nimeks Tooted ja tellimused.xlsx.

Selle õppetüki teemad

Ülesanne 1: toodete importimine Exceli töövihikusse

Selles ülesandes impordite tooted ja tellimused. xlsx faili 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

Tabel. PromoteHeaders

(Products)

Muude veergude eemaldamine ainult oluliste veergude kuvamiseks

RemovedOtherColumns

Tabel. 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 ühe seostuva kirjeni ja esindabühte seost teema tabeliga.Tabeli link navigeerib seotud tabelisse ja kujutab endast üht-mitmele seost teema tabeliga. Link tähistab navigeerimise atribuute andmeallikas, mis on seotud mudeli sees. OData kanali korral esindavad navigeerimise atribuudid välisvõtme seosega üksust. Andmebaasis (nt SQL Server) tähistavad navigation atribuudid andmebaasi Välisvõtmete seoseid.

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 ikooni Laienda ikooni ( Laiendamine ).

  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.: Power Querys saate laiendada veerust lingitud tabeleid ja ka võimalust teha summeeritud toiminguid lingitud tabeli veergudes enne, kui laiendate tabelis teema olevad andmed. Lisateavet summeerimise toimingute kohta leiate teemast andmete koondamine veerust.

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 selle tabeli ikooni ( Tabeliikoon ) eelvaate ülemises vasakus nurgas.

  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“.

Müük kokku

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

Tabel. ExpandTableColumn

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

Muude veergude eemaldamine ainult oluliste veergude kuvamiseks

RemovedColumns

Tabel. 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

Tabel. AddColumn

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

Veeru OrderDate teisendamine aasta renderdamiseks

RenamedColumns

Tabel. RenameColumns

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

TransformedColumn

Tabel. TransformColumns

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

RenamedColumns1

Tabel. RenameColumns

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

Ridade rühmitamine veergude ProductID ja Year alusel

GroupedRows

Tabel. rühm
(RenamedColumns1, {"Year", "Order_Details. ProductID"}, {{"Total Sales", iga loend. SUM ([rida Kokku]), tippige number}})

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 esmase tabelina tooted ja valige ühendamiseks teise või seostuva päringuga kogu müük . Müük kokku muutub uueks laiendatav veerg.

  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 laiendage kirjakooste veergu nimega NewColumn , et luua kaks uut veergu toodete päring: aasta ja Total Sales.

Tabeli NewColumn lingi laiendamine

  1. Klõpsake päringu eelvaate ruudustikus ikooni NewColumn laiendamine ( Laiendamine ).

  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)

Tabel. NestedJoin

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

Ühendamisveeru laiendamine

ExpandNewColumn

Tabel. ExpandTableColumn

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

RenamedColumns

Tabel. RenameColumns

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

SortedRows

Tabel. sortimise

(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.

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, et otsingupäringu kuvamiseks ei laadita olemasolevat töövihiku päringut, valige menüü Power Query (Power Query ) jaotise välisandmed jaotisest " Too välisandmed " väli muudest allikatest > tühi päring. Järgmises videos näidatakse ühte võimalust päringuredaktori kuvamiseks.

Kuidas Excelis päringuredaktorit vaadata

Lehe algusesse

Märkus.:  See leht on tõlgitud automaatselt ning sellel võib leiduda grammatikavigu ja ebatäpsusi. Tahame, et sellest sisust oleks teile abi. Andke meile teada, kui see teave oli teile abiks. Soovi korral saate ingliskeelset artiklit lugeda siit.​

Facebook LinkedIn Meilisõnum

Kas vajate veel abi?

Täiendage Office'i kasutamise 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.

×