Mitme andmeallika kombineerimine (Power Query)

Selle toiminguga saate tooted importida toodete ja Orders.xlsx (ülal alla laaditud ja ümber nimetatud) failist Exceli töövihikusse, määrata read veerupäisteks, eemaldada mõned veerud ja laadida päringu töölehele.

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

1. Looge Exceli töövihik.

2. Valige Andmed > Too andmed > töövihikust > failist.

3. Otsige dialoogiboksis Andmete importimine üles allalaaditud Products.xlsx fail ja seejärel valige Ava.

4. Topeltklõpsake paanil Navigaator tabelit Tooted . Kuvatakse Päringuredaktor Toide.

2. toiming: päringuetappide uurimine

Vaikimisi lisab Power Query teile automaatselt mitu juhist. Lisateabe saamiseks uurige igat etappi päringusätete paani jaotises Rakendatud etapid.

1. Paremklõpsake etappi Allikas ja valige Redigeeri sätteid. See toiming loodi töövihiku importimisel.

2. Paremklõpsake navigeerimistoimingut ja valige Redigeeri sätteid. See juhis loodi, kui valisite tabeli dialoogiboksis Navigeerimine .

3. Paremklõpsake juhist Muudetud tüüp ja valige Redigeeri sätteid. Selle toimingu lõi Power Query, mis tuletas iga veeru andmetüübid. Täieliku valemi kuvamiseks valige valemiribast paremal asuv allanool.

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 andmete eelvaatesveerud ProductID (Toote ID), ProductName (Tootenimi), CategoryID (Kategooria ID) ja QuantityPerUnit (kasutage klahvikombinatsiooni Ctrl+Click või Shift+Click).

2. Valige Eemalda veerud > Eemalda muud veerud.

   

4. juhis: toodete päringu laadimine

Selles etapis laaditakse päring Tooted Exceli töölehele.

• Valige Avaleht > Sule & Laadi. Päring kuvatakse uuel Exceli töölehel.

Kokkuvõte: Power Query ülesandes 1 loodud etapid

Päringutegevuste sooritamisel Power Query luuakse päringuetapid ja need loetletakse paani Päringusätted loendis Rakendatud etapid. Igale päringuetapile vastab Power Query valem, mida tuntakse ka „M“-keelena. Lisateavet valemite Power Query kohta leiate teemast Power Query valemite loomine Excelis.

Ülesanne	Päringu etapp	Valem
Exceli töövihiku importimine	Lähtevaluuta	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Valige tabel Tooted	Navigeerida	= Source{[Item="Products",Kind="Table"]}[Data]
Power Query tuvastab automaatselt veeru andmetüübid	Muudetud tüüp	= 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}, {"Katkestatud", tüüp loogika}})
Muude veergude eemaldamine ainult oluliste veergude kuvamiseks	Eemaldatud muud veerud	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Selle toiminguga impordite andmed Oma Exceli töövihikusse Northwindi OData näidiskanalist http://services.odata.org/Northwind/Northwind.svc,laiendate Order_Details tabelit, eemaldate veerge, arvutate reasumma, teisendate tellimusekuupäeva, rühmitate read ProductID ja Year järgi, nimetate päringu ümber ja keelate päringu allalaadimise Exceli töövihikusse.

1. toiming: OData kanaliga ühenduse loomine

1. Valige Andmed > Too andmed > muudest allikatest > OData kanalist.

2. Sisestage dialoogiboksis OData kanal Northwindi OData kanali URL.

3. Klõpsake nuppu OK.

4. Topeltklõpsake paanil Navigaator tabelit Tellimused .

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äitamisel ühendatakse seotud tabeli (Order_Details) read esmase tabeliga (Tellimused) ridadele.

Power Query on seotud tabelit sisaldava veeru lahtris väärtus Kirje või Tabel. Neid nimetatakse struktureeritud veergudeks. Kirje tähistab ühte seostuvat kirjet ja tähistabüks-ühele seost praeguste andmete või primaartabeliga. Tabel tähistab seotud tabelit ja tähistab üks-mitmele seost praeguse või esmase tabeliga. Liigendatud veerg tähistab seost andmeallikas, millel on relatsioonmudel. Näiteks tähistab liigendatud veerg olemit, millel on OData kanalis välisvõtme seos või SQL Server andmebaasis võõrvõtme seos.

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 aknas Andmete eelvaade horisontaalselt veeruni Order_Details .

2. Valige veerus Order_Details laiendamisikoon ().

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

   1. Kõigi veergude tühjendamiseks valige (Vali kõik veerud ).

   2. Valige ProductID (Toote ID), UnitPrice (ÜhikuHind) ja Quantity (Kogus).

   3. Klõpsake nuppu OK.

      

      Märkus.: Power Query saate enne teematabeli andmete laiendamist laiendada veerust lingitud tabeleid ja lingitud tabeli veerud liita. Lisateavet kokkuvõttetoimingute tegemise kohta leiate teemast Veeruandmete liitmine.

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

1. Valige aknas Andmete eelvaade järgmised veerud.

   1. Valige esimene veerg TellimuseID.

   2. Shift + klõpsake viimast veergu 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 valitud veerupäist ja valige Eemalda muud 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.

1. Valige andmete eelvaates eelvaate vasakus ülanurgas tabeliikoon ().

2. Klõpsake käsku Lisa kohandatud veerg.

3. Sisestage dialoogiboksi Kohandatud veerg väljale Kohandatud veeru valem tekst [Order_Details.ÜhikuHind] * [Order_Details.Kogus].

4. Sisestage väljale Uue veeru nimi väärtus Reasumma.

5. Klõpsake nuppu OK.

5. juhis: teisendage OrderDate veerg aasta veeruks

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

1. Paremklõpsake andmete eelvaates veergu Tellimiskuupäev ja valige Transformatsioon > Aasta.

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

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

   2. Right-Click veerus Tellimiskuupäev valige Nimeta ümber ja sisestage aasta.

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

1. Valige andmete eelvaatesYear (Aasta ) ja Order_Details.ProductID (Toote ID).

2. Right-Click üks päistest ja valige Rühmitusalus.

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.

   

7. juhis: nimetage päring ümber

Enne müügiandmete Excelisse importimist nimetage päring ümber.

• Sisestage paanil Päringu sätted väljale Nimi väärtus Kogumüük.

Tulemid: 2. toimingu lõplik päring

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

Kokkuvõte: Power Query ülesandes 2 loodud etapid 

Päringutegevuste sooritamisel Power Query luuakse päringuetapid ja need loetletakse paani Päringusätted loendis Rakendatud etapid. Igale päringuetapile vastab Power Query valem, mida tuntakse ka „M“-keelena. Lisateavet valemite Power Query kohta leiate teemast Teave Power Query valemite kohta.

Ülesanne	Päringu etapp	Valem
OData kanaliga ühenduse loomine	Allikas	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
Select a table	Navigeerimine	= Allikas{[Nimi="Tellimused"]}[Andmed]
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	Kohandatud on lisatud	= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
Muuda tähenduslikumaks nimeks Lne Total	Ümbernimetatud veerud	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Veeru OrderDate teisendamine aasta renderdamiseks	Ekstraktitud aasta	= Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}})
Valige  tähendusrikkamad nimed, OrderDate ja Year	Ümbernimetatud veerud 1	Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}})
Ridade rühmitamine veergude ProductID ja Year alusel	GroupedRows	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

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 toimingus tuleb teil kombineerida päringud Products (Tooted ) ja Total Sales (Müük kokku ), kasutades toimingut Ühenda ja Laienda ning seejärel laadida päringu Total Sales per Product (Müük kokku toote kohta) Exceli andmemudelisse.

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

1. Liikuge Exceli töövihikus töölehe vahekaardil Tooted päringule Tooted .

2. Valige päringus lahter ja seejärel valige Päring > Ühenda.

3. Valige dialoogiboksis Ühendamine esmaseks tabeliks Tooted ja seejärel teisese või seotud päringuna, mille soovite ühendada, käsk Total Sales . Total Sales saab uueks liigendatud veeruks laiendusikooniga.

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. Valige 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. Lisateavet privaatsustasemete kohta leiate teemast Privaatsustasemete määramine.

   

Tulem

Toiming Ühenda loob päringu. Päringutulem sisaldab kõiki veerge esmasest tabelist (Tooted) ja ühte liigendatud tabeli veergu seotud tabeliga (Total Sales). Teisesest või seotud tabelist esmasesse tabelisse uute veergude lisamiseks valige ikoon Laienda .

2. juhis: ühendatud veeru laiendamine

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

1. Valige andme eelvaates nupu NewColumn kõrval laiendamisikoon ().

2. Tehke ripploendis Laienda järgmist.

   1. Kõigi veergude tühjendamiseks valige (Vali kõik veerud ).

   2. Valige 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. Selleks et teada saada, millistel toodetel ja aastatel on kõige suurem müügimaht, valige Sordi laskuvas järjestuseskogumüügi järgi.

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

Tulem

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

Selles etapis laadite päringu Exceli andmemudelisse, et koostada päringutulemiga ühendatud aruanne. Kui olete andmed Exceli andmemudelisse laadinud, saate andmeanalüüsi täiustamiseks kasutada Power Pivotit.

1. Valige Avaleht > Sule & Laadi.

2. Märkige dialoogiboksis Andmete importimine kindlasti ruut Lisa need andmed andmemudelisse. Selle dialoogiboksi kasutamise kohta lisateabe saamiseks valige küsimärk (?).

Tulem

Teil on päring Total Sales per Product , mis ühendab Products.xlsx faili ja Northwind OData kanali andmed. See päring rakendatakse Power Pivoti mudelile. Lisaks muudavad ja värskendavad päringu muudatused tulemitabelit andmemudelis.

Kokkuvõte: Power Query toimingus Ülesanne 3 loodud etapid

Kui teete Power Query päringutoimingute ühendamine, luuakse päringuetapid ja need loetletakse paani Päringusätted loendis Rakendatud etapid. Igale päringuetapile vastab Power Query valem, mida tuntakse ka „M“-keelena. Lisateavet valemite Power Query kohta leiate teemast Teave 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"}, "Total Sales", JoinKind.LeftOuter)
Ühendamisveeru laiendamine	Laiendatud kogumüük	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
Kahe veeru ümbernimetamine	Ümbernimetatud veerud	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Kogumüügi sortimine tõusvas järjestuses	Sorditud read	= Table.Sort(#"Ümbernimetatud veerud",{{"Total Sales", Order.Ascending}})