Selles õppeteemas saate Power Query päringuredaktori abil importida andmeid tooteteavet sisaldavast kohalikust Exceli failist ja tootetellimuse teavet sisaldavast OData kanalist. Sooritate teisendus- ja koondamistoiminguid ning kombineerite mõlemast allikast pärinevaid andmeid, et luua aruanne "Müük kokku toote ja aasta kohta".
Õppetüki sooritamiseks vajate töövihikut Tooted . Tippige dialoogiboksis Nimega salvestamine faili nimeks Tooted ja tellimused.xlsx.
Ülesanne 1: toodete importimine Exceli töövihikusse
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. toiming: Ühenduse loomine Exceli töövihikuga
- Looge Exceli töövihik.
- Valige Andmed>Too andmed>töövihikust failist>.
- Otsige dialoogiboksis Andmete importimine üles allalaaditud Products.xlsx fail ja seejärel valige Ava.
- Topeltklõpsake paanil Navigaator tabelit Tooted . Kuvatakse Power Query redaktor .
2. toiming: päringuetappide uurimine
Vaikimisi lisab Power Query teile automaatselt mitu etappi. Lisateabe saamiseks uurige igat etappi päringusätete paani jaotises Rakendatud etapid.
- Paremklõpsake etappi Allikas ja valige Redigeeri sätteid. See toiming loodi töövihiku importimisel.
- Paremklõpsake navigeerimistoimingut ja valige Redigeeri sätteid. See juhis loodi, kui valisite tabeli dialoogiboksis Navigeerimine .
- Paremklõpsake juhist Muudetud tüüp ja valige Redigeeri sätteid. Selle etapi 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 huvipakkuvad 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).
- Valige andmete eelvaatesveerud ProductID (Toote ID), ProductName (Tootenimi), CategoryID (Kategooria ID) ja QuantityPerUnit (kasutage klahvikombinatsiooni Ctrl+Click või Shift+Click).
- 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: rakenduses Task 1 loodud Power Query etapid
Päringutegevuste sooritamisel Rakenduses Power Query luuakse päringuetapid ja need loetletakse paani Päringu sätted loendis Rakendatud etapid . Igale päringuetapile vastab Power Query valem, mida tuntakse ka „M“-keelena. Lisateavet Power Query valemite 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 | Navigeerimine | = 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"}) |
Ülesanne 2: tellimisandmete importimine OData kanalist
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äev, 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
- Valige DataGet Data>From Other Sources fromOData Feed (Andmed >muudest allikatest> OData kanalist).
- Sisestage dialoogiboksis OData kanal Northwindi OData kanali URL.
- Klõpsake nuppu OK.
- Topeltklõpsake paanil Navigaator tabelit Tellimused .
2. juhis: Order_Details tabeli laiendamine
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 Querys on seostuvat 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õõrvõtme seos või SQL Serveri andmebaasis välisvõtme seos.
Pärast tabeli Order_Details laiendamist lisatakse tabelisse Tellimused kolm uut veergu ja veel üks rida, üks iga pesastatud või seotud tabeli rea kohta.
Liikuge aknas Andmete eelvaade horisontaalselt veeruni Order_Details .
Valige veerus Order_Details laiendamisikoon (
).Tehke rippmenüüs Laienda järgmist.
Kõigi veergude tühjendamiseks valige (Vali kõik veerud ).
Valige ProductID (Toote ID), UnitPrice (ÜhikuHind) ja Quantity (Kogus).
Klõpsake nuppu OK.
Märkus.
Power Querys saate lingitud tabeleid veerust laiendada ja lingitud tabeli veerud enne teematabelis andmete laiendamist kokku liita. Lisateavet kokkuvõttetoimingute tegemise kohta leiate teemast Veeruandmete liitmine.
3. juhis: eemaldage muud veerud, et kuvada ainult huvipakkuvad veerud
Selles etapis tuleb teil eemaldada kõik veerud peale järgmiste: OrderDate (Tellimiskuupäev), ProductID (Toote ID), UnitPrice (Ühiku hind) ja Quantity (Kogus).
Valige aknas Andmete eelvaade järgmised veerud.
- Valige esimene veerg TellimuseID.
- Shift + klõpsake viimast veergu Tarnija.
- 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).
Paremklõpsake valitud veerupäist ja valige Eemalda muud veerud.
4. juhis: iga Order_Details rea kogusumma arvutamine
Selles juhises tuleb teil luua kohandatud veerg, et arvutada tabeli Order_Details (Tellimuse_üksikasjad) iga rea kohta rea kogusumma.
- Valige andmete eelvaates eelvaate vasakus ülanurgas
). - Klõpsake käsku Lisa kohandatud veerg.
- Sisestage dialoogiboksi Kohandatud veerg väljale Kohandatud veeru valem tekst [Order_Details.ÜhikuHind] * [Order_Details.Kogus].
- Sisestage väljale Uue veeru nimi väärtus Reasumma.
- Klõpsake nuppu OK.
5. toiming. Tellimusekuupäeva veeru teisendamine
Selles etapis tuleb teil veerg OrderDate (Tellimiskuupäev) teisendada tellimiskuupäeva aasta renderdamiseks.
Paremklõpsake andmete eelvaates veergu Tellimusekuupäev ja valige Teisenda>aasta.
Nimetage veerg OrderDate (Tellimiskuupäev) ümber veeruks Year.
- Topeltklõpsake veergu OrderDate (Tellimiskuupäev) ja sisestage Year või
- Right-Click veerus Tellimiskuupäev valige Nimeta ümber ja sisestage aasta.
6. juhis: ridade rühmitamine ProductID ja Year järgi
Valige andmete eelvaatesYear (Aasta ) ja Order_Details.ProductID (Toote ID).
Right-Click üks päistest ja valige Rühmitusalus.
Tehke dialoogiboksis Rühmitusalus järgmist.
- Sisestage tekstiväljale Uus veeru nimi nimi Müük kokku.
- Valige rippmenüüst Toiming käsk Summa.
- Valige rippmenüüst Veerg väärtus Line Total.
Klõpsake nuppu OK.
7. toiming: päringu ümbernimetamine
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: 2. toimingus loodud Power Query etapid
Päringutegevuste sooritamisel Rakenduses Power Query luuakse päringuetapid ja need loetletakse paani Päringu sätted loendis Rakendatud etapid . Igale päringuetapile vastab Power Query valem, mida tuntakse ka „M“-keelena. Lisateavet Power Query valemite kohta leiate teemast Lisateave 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}}) |
Ü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 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
Liikuge Exceli töövihikus töölehe vahekaardil Tooted päringule Tooted .
Valige päringus lahter ja seejärel valigePäringukooste>.
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.
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).
Tehke dialoogiboksis Privaatsustasemed järgmist.
- Valige mõlema andmeallika privaatsuse eraldamise tasemeks Organisatsioonisisene.
- Valige Salvesta.
Klõpsake nuppu OK.
Märkus.
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).
Valige andmete eelvaates nupu NewColumn kõrval laiendamisikoon (
).Tehke ripploendis Laienda järgmist.
- Kõigi veergude tühjendamiseks valige (Vali kõik veerud ).
- Valige Year (Aasta ) ja Total Sales (Müük kokku).
- Klõpsake nuppu OK.
Nimetage need kaks veergu ümber veergudeks Year ja Total Sales.
Selleks et teada saada, millistel toodetel ja aastatel on kõige suurem müügimaht, valige Sordi laskuvas järjestuseskogumüügi järgi.
Nimetage päring ümber päringuks Total Sales per Product.
Tulem
3. toiming: päringu "Total Sales per Product" laadimine 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.
- Valige Avaleht>Sule & Laadi.
- 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: 3. toimingus loodud Power Query etapid
Power Querys ühendamispäringutegevuste sooritamisel luuakse päringuetapid, mis loetletakse paani Päringusätted loendis Rakendatud etapid. Igale päringuetapile vastab Power Query valem, mida tuntakse ka „M“-keelena. Lisateavet Power Query valemite kohta leiate teemast Lisateave 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 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}}) |