Mitme andmeallika ühendamine (Power Query)

Selles ülesandes impordite tooteid Orders.xlsx -failist (allalaaditud ja ümber nimetatud) Excel töövihikusse, aitate ridu veerupäistele, eemaldate mõned veerud ja laadite päringu töölehele.

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

1. Looge Exceli töövihik.

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

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

4. Topeltklõpsake paanil Navigaatortabelit Tooted. Kuvatakse Power Query redaktor.

2. juhis. Uurige päringutoiminguid

Vaikimisi lisab Power Query teile mugavalt mitu etappi. Lisateabe saamiseks uurige iga juhist paani Päring Sätted jaotises Rakendatud toimingud.

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

2. Paremklõpsake navigeerimise juhist ja valige Redigeeri Sätted. See toiming loodi siis, kui valisite tabeli dialoogiboksis Navigeerimine.

3. Paremklõpsake juhist Muudetud tüüp ja valige Redigeeri Sätted. Selle juhise lõi Power Query, mis tuletas iga veeru andmetüübid. Täieliku valemi kuvamiseks valige valemiribast paremal 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 andme eelvaatesveerud ProductID, ProductName, CategoryIDja QuantityPerUnit (kasutage klahvikombinatsiooni Ctrl+Click või Shift+Click).

2. Valige Eemalda veerud > Eemalda muud veerud.

   

4. juhis: tootepäringu laadimine

Selles etapis laadite päringu Tooted Excel.

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

Kokkuvõte: Power Query toimingud, mis on loodud 1. ülesandes

Kui teete Power Querys päringutoiminguid, luuakse päringu etapid ja need Sätted loendis Rakendatud toimingud. Igale päringuetapile vastab Power Query valem, mida tuntakse ka „M“-keelena. Lisateavet Power Query valemite kohta leiate teemast Power Query valemite loomine Excel.

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

Selles ülesandes impordite Excel töövihikusse andmeid http://services.odata.org/Northwind/Northwind.svc-i näidiskanalist Northwind OData,laiendate tabelit Order_Details, eemaldate veerge, arvutate rea kogusummat, teisendate tellimused, rühmitate read toote ID ja aasta järgi, nimetate päringu ümber ja keelate päringu allalaadimise Excel töövihikusse.

1. juhis: Ühendus OData kanalisse

1. Valige Data > Get Data>From Other Sources > From OData Feed (Too andmed > 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äivitamisel ühendatakse seotud tabeli read (Order_Details) ridadeks primaartabeliga (Tellimused).

Power Querys on seotud tabelit sisaldavas veerus lahtris väärtus Kirjevõi Tabel. Neid nimetatakse liigendatud veergudeks. Kirje tähistab ühte seotud 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 primaartabeliga. Liigendatud veerg tähistab seost andmeallikas, kus on relational-mudel. Näiteks liigendatud veerg tähistab OData kanalis võõrvõtmeseoga olemit või välisvõtme seost SQL Server.

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 jaotises Andmeeelvaadekerides horisontaalselt Order_Details veeruni.

2. Valige Order_Details laiendamise ikoon ().

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

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

   2. Valige TooteID, Ühikuhindja Kogus.

   3. Klõpsake nuppu OK.

      

      Märkus.: Power Querys saate veerust lingitud tabeleid laiendada ja lingitud tabeli veerud enne teematabelis andmete laiendamist liita. Kokkuvõttetoimingute kohta leiate lisateavet 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). 

1. Valige jaotises Andmeeelvaadejärgmised veerud.

   1. Valige esimene veerg OrderID.

   2. Shift+Klõpsake viimast veergu , Saatja.

   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 jaotises Andmeeelvaadeeelvaate vasakus ülanurgas tabeliikoon () .

2. Klõpsake nuppu Lisa kohandatud veerg.

3. Sisestage dialoogiboksi Kohandatud veerg väljale Kohandatud veeru valem [Order_Details.UnitPrice] * [Order_Details.Quantity].

4. Sisestage väljale New column name (Uus veeru nimi) väärtus Line Total (Rea kogusumma).

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 jaotises Andmeeelvaadeveergu Tellimused ja valige Teisenda >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 Tellimused valige Nimeta ümber ja sisestageAasta.

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

1. Valige jaotises Andmeeelvaadeväärtus Aasta ja Order_Details.ProductID.

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

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 importimist Excel nimetage päring ümber.

• Sisestage paanil Sätted väljale Nimi väärtus Total Sales (Müük kokku).

Tulemid: 2. ülesande lõplik päring

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

Kokkuvõte: Power Query toimingud, mis on loodud 2. ülesandes 

Kui teete Power Querys päringutoiminguid, luuakse päringu etapid ja need Sätted loendis Rakendatud toimingud. Igale päringuetapile vastab Power Query valem, mida tuntakse ka „M“-keelena. Lisateavet Power Query valemite 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	= Source{[Name="Orders"]}[Data]
Tabeli Order_Details laiendamine	Tabeli Order_Details laiendamine	= Table.ExpandTableColumn(Tellimused, "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	Lisatud kohandatud	= 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])
Tähendusrikkama nime muutmine, Lne Total	Ümbernimetatud veerud	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Veeru OrderDate teisendamine aasta renderdamiseks	Ekstraktitud aasta	= Table.TransformColumns(#"Rühmitatud read";{{"Year", Date.Year, Int64.Type}})
Muuda  tähendusrikkam nimed, Tellimused Ja Aasta	Ümber nimetatud 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 ülesandes kombineerite päringud Tooted ja Müügi kokku, kasutades koostepäringut ja laiendamistoimingut ning seejärel laadite päringu Müügi kogumaht toote kohta Excel andmemudelisse.

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

1. Liikuge Excel vahekaardil Tooted päringule Tooted.

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

3. Valige dialoogiboksis Ühendamine primaartabelina Tooted ja seejärel sekundaarse või seotud ühendamispäringuna väärtus Müügi kokku. Total Sales muutub uueks liigendatud veeruks, kus on laiendamise ikoon.

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

   

Tulem

Toiming Ühenda loob päringu. Päringutulem sisaldab kõiki primaartabeli(Tooted)veerge ja ühte tabeli liigendatud veergu seotud tabelisse (Müük kokku). Valige ikoon Laienda, et lisada teisese või seotud tabeli primaartabelisse uusi veerge.

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 jaotises Andmeeelvaadekäsk Laienda ikoon () suvandi NewColumn kõrval.

2. Ripploendis Laienda:

   1. Kõigi veergude tühjendamiseks valige (Valige 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 millistel aastatel tooted suurima müügimahu said, valige Sordi laskuvas järjestuses kogumüügijä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 saate päringu laadida Excel andmemudelisse, et koostada päringutulemiga ühendatud aruanne. Pärast andmete laadimist Excel andmemudelissesaate power Pivoti abil andmeanalüüsi edasi arendada.

1. Valige Avaleht > Sule & Laadi.

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

Tulem

Teil on päring Kogumüük toote kohta, mis ühendab Products.xlsx ja Northwind OData kanali andmed. See päring rakendatakse Power Pivoti mudelile. Lisaks muudetakse päringu muudatusi ja värskendatakse tulemuseks saadud tabelit andmemudelis.

Kokkuvõte: Power Query toimingud, mis on loodud 3. ülesandes

Kui teete Power Querys päringutegevuste ühendamist, luuakse päringu etapid ja need loetletakse paanil Päringu Sätted loendis Rakendatud toimingud. Igale päringuetapile vastab Power Query valem, mida tuntakse ka „M“-keelena. Lisateavet Power Query valemite 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(Tooted, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Ühendamisveeru laiendamine	Laiendatud müük kokku	= Table.ExpandTableColumn(Allikas, "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(#"Renamed Columns",{{"Total Sales", Order.Ascending}})