Šiame mokyme parodyta, kaip "Power Query" užklausų rengyklę galite naudoti norėdami importuoti duomenis iš vietinio "Excel" failo, kuriame yra produkto informacija, ir iš "OData" informacijos santraukos, kurioje yra produkto užsakymo informacija. Atliekant transformavimo bei telkimo veiksmus jungiami duomenys iš abiejų šaltinių, kad būtų sukurta ataskaita "Bendras pardavimas pagal produktą ir metus".
Norint atlikti šį mokymą, reikalinga darbaknygė Produktai . Dialogo lange Įrašyti kaip įrašykite failo pavadinimą Produktai ir užsakymai.xlsx.
1 užduotis. Produktų importavimas į „Excel“ darbaknygę
Atlikdami šią užduotį galite importuoti produktus iš failų Produktai ir Orders.xlsx (atsisiųstas ir pervardytas anksčiau) į "Excel" darbaknygę, perkelti eilutes į stulpelių antraštes, pašalinti kai kuriuos stulpelius ir įkelti užklausą į darbalapį.
1 veiksmas. Prisijungimas prie "Excel" darbaknygės
- „Excel“ darbaknygės kūrimas
- Pasirinkti duomenis>Gauti duomenis>iš failo>iš darbaknygės.
- Dialogo lange Duomenų importavimas raskite atsisiųstą Products.xlsx failą, tada pasirinkite Atidaryti.
- Naršyklės srityje dukart spustelėkite lentelę Produktai. Rodoma "Power Query" rengyklė .
2 veiksmas. Užklausos veiksmų nagrinėjimas
Pagal numatytuosius parametrus "Power Query" automatiškai įtraukia kelis veiksmus, kad būtų patogiau. Norėdami sužinoti daugiau, išnagrinėkite kiekvieną veiksmą užklausos parametrų srities dalyje Pritaikyti veiksmai.
- Dešiniuoju pelės mygtuku spustelėkite veiksmą Šaltinis ir pasirinkite Redaguoti parametrus. Šis veiksmas buvo sukurtas importuojant darbaknygę.
- Dešiniuoju pelės mygtuku spustelėkite naršymo veiksmą ir pasirinkite Redaguoti parametrus. Šis veiksmas buvo sukurtas, kai pasirinkote lentelę dialogo lange Naršymas .
- Dešiniuoju pelės mygtuku spustelėkite veiksmą Pakeistas tipas ir pasirinkite Redaguoti parametrus. Šį veiksmą sukūrė "Power Query", kuri nustatė kiekvieno stulpelio duomenų tipus. Pasirinkite rodyklę žemyn į dešinę nuo formulės juostos, kad pamatytumėte visą formulę.
3 veiksmas. Kitų stulpelių šalinimas, kad būtų rodomi tik dominantys stulpeliai
Atlikdami šį veiksmą pašalinkite visus stulpelius, išskyrus ProductID, ProductName, CategoryID ir QuantityPerUnit.
- Duomenų peržiūroje pasirinkite stulpelius ProductID, ProductName, CategoryID ir QuantityPerUnit (naudokite "Ctrl" + "Click" arba "Shift" + "Click").
- Pasirinkite Pašalinti stulpelius,>pašalinkite kitus stulpelius.
4 veiksmas. Produktų užklausos įkėlimas
Šiuo veiksmu įkeliate produktų užklausą į "Excel" darbalapį.
- Pasirinkite Pagrindinis>, Uždaryti & Įkelti. Užklausa bus rodoma naujame "Excel" darbalapyje.
Santrauka: "Power Query" veiksmai, sukurti atliekant 1 užduotį
Kai "Power Query" atliekate užklausos veiksmus, užklausos veiksmai sukuriami ir pateikiami užklausos parametrų srityje, sąraše Pritaikyti veiksmai . Kiekvienas užklausos veiksmas turi atitinkamą "Power Query" formulę, dar žinomą kaip "M" kalba. Daugiau informacijos apie "Power Query" formules rasite "Power Query" formulių kūrimas programoje "Excel".
| Užduotis | Užklausos veiksmas | Formulė |
|---|---|---|
| "Excel" darbaknygės importavimas | Šaltinis | = Excel.Workbook(File.Contents("C:\Products ir Orders.xlsx"), null, true) |
| Pasirinkite lentelę Produktai | Naršyti | = Source{[Item="Products",Kind="Table"]}[Data] |
| "Power Query" automatiškai aptinka stulpelio duomenų tipus | Pakeistas tipas | = 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}}) |
| Kitų stulpelių pašalinimas, kad būtų rodomi tik dominantys stulpeliai | Pašalinti kiti stulpeliai | = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
2 užduotis. Užsakymo duomenų importavimas iš „OData“ informacijos santraukos
Atlikdami šią užduotį importuojate duomenis į "Excel" darbaknygę iš "Northwind OData" informacijos santraukos http://services.odata.org/Northwind/Northwind.svc, išplečiate Order_Details lentelę, pašalinate stulpelius, apskaičiuojate bendrą eilutės sumą, transformuojate OrderDate, grupuojate eilutes pagal ProductID ir Year, pervardykite užklausą ir išjungiate užklausų atsisiuntimą į "Excel" darbaknygę.
1 veiksmas. Prisijungimas prie "OData" informacijos santraukos
- Pasirinkti duomenis>Gauti duomenis>iš kitų šaltinių>iš "OData" informacijos santraukos.
- Dialogo lange „OData“ informacijos santrauka įveskite „Northwind OData“ informacijos santraukos URL.
- Pažymėkite Gerai.
- Naršyklės srityje dukart spustelėkite lentelę Užsakymai.
2 veiksmas: Order_Details lentelės išplėtimas
Šiame žingsnyje, galite išplėsti lentelę Order_Details, kuri susieta su lentele Orders, kad būtų sujungti lentelės Order_Details stulpeliai ProductID, UnitPrice ir Quantity į lentelę Orders. Operacija Išplėsti sujungia stulpelius iš susijusios lentelės į temos lentelę. Vykdant užklausą, susijusios lentelės (Order_Details) eilutės yra sujungiamos į eilutes su pirmine lentele (Užsakymai).
"Power Query" užklausoje stulpelio, kuriame yra susijusi lentelė, langelyje yra reikšmė Įrašas arba Lentelė . Tai vadinama struktūriniais stulpeliais. Įrašas nurodo vieną susijusį įrašą ir rodo ryšį "vienas su vienu" su dabartiniais duomenimis arba pirmine lentele. Lentelė nurodo susijusią lentelę ir rodo ryšį "vienas su daugeliu" su dabartine arba pirmine lentele. Struktūrinis stulpelis reiškia ryšį duomenų šaltinyje, kuriame yra sąryšinis modelis. Pvz., struktūrinis stulpelis nurodo objektą su išorinio rakto susiejimu "OData" informacijos santraukoje arba išorinio rakto ryšiu "SQL Server" duomenų bazėje.
Kai išplečiate Order_Details lentelę, į lentelę Užsakymai įtraukiami trys nauji stulpeliai ir papildomos eilutės, po vieną kiekvienai įdėtosios arba susietosios lentelės eilutei.
Duomenų peržiūroje horizontaliai slinkite iki stulpelio Order_Details.
Stulpelyje Order_Details pasirinkite išplėtimo piktogramą (
).Išskleidžiamajame sąraše Išplėsti:
Pasirinkite (Pasirinkti visus stulpelius), jei norite išvalyti visus stulpelius.
Pasirinkite ProductID, UnitPrice ir Quantity.
Pažymėkite Gerai.
Pastaba
"Power Query" galite išplėsti lentelės, susietas iš stulpelio, ir agreguoti susietos lentelės stulpelius prieš išplečiant temos lentelės duomenis. Daugiau informacijos, kaip vykdyti sudėtines operacijas, žr. Duomenų apibendrinimas iš stulpelio.
3 veiksmas. Kitų stulpelių šalinimas, kad būtų rodomi tik dominantys stulpeliai
Atlikdami šį veiksmą, pašalinate visus stulpelius, išskyrus OrderDate, ProductID, UnitPrice ir Quantity.
Duomenų peržiūros lange pasirinkite šiuos stulpelius:
- Pasirinkite pirmą stulpelį, UžsakymoID.
- "Shift" + spustelėkite paskutinį stulpelį, Siuntėjas.
- Shift + spustelėkite stulpelius OrderDate, Order_Details.ProductID, Order_Details.UnitPrice ir Order_Details.Quantity.
Dešiniuoju pelės mygtuku spustelėkite pažymėto stulpelio antraštę ir pasirinkite Pašalinti kitus stulpelius.
4 veiksmas. Kiekvienos Order_Details eilutės sumos apskaičiavimas
Atlikdami šį veiksmą sukuriate pasirinktinį stulpelį, skirtą kiekvienos Order_Details eilutės sumai apskaičiuoti.
-
Duomenų peržiūros lange pasirinkite peržiūros viršutiniame kairiajame kampe esančią lentelės piktogramą (
). - Spustelėkite Įtraukti pasirinktinį stulpelį.
- Dialogo lango Pasirinktinis stulpelis formulės lauke Pasirinktinis stulpelis įveskite [Order_Details.Vieneto_kaina] * [Order_Details.Kiekis].
- Lauke Naujas stulpelio pavadinimas įveskite Line Total.
- Pažymėkite Gerai.
5 veiksmas. OrderDate metų stulpelio keitimas
Atlikdami šį veiksmą, pakeisite stulpelį OrderDate, kad jame atsispindėtų užsakymo datos metai.
Duomenų peržiūros režimu dešiniuoju pelės mygtuku spustelėkite stulpelį Užsakymo_data ir pasirinkite Pakeisti>metus.
Stulpelio OrderDate pervardijimas į Year:
- Dukart spustelėkite stulpelį OrderDate ir įveskite Year arba
- Right-Click stulpelyje OrderDate pasirinkite Pervardyti ir įveskite Year.
6 veiksmas. Eilučių grupavimas pagal stulpelius ProductID ir Year
Duomenų peržiūroje pasirinkite Year ir Order_Details.ProductID.
Right-Click vieną iš antraščių ir pasirinkite Grupuoti pagal.
Dialogo lange Grupuoti pagal :
- Teksto lauke Naujas stulpelio pavadinimas įveskite Total Sales.
- Išskleidžiamajame sąraše Operacijos pasirinkite Suma.
- Išskleidžiamajame sąraše Stulpelis pasirinkite Eilučių suma.
Pažymėkite Gerai.
7 veiksmas. Užklausos pervardijimas
Prieš importuodami pardavimo duomenis į "Excel", pervardykite užklausą:
- Srityje Užklausos parametrai, lauke Pavadinimas įveskite Total Sales.
Rezultatai: galutinė 2 užduoties užklausa
Atlikę kiekvieną veiksmą, turėsite visą „Northwind OData“ informacijos santraukos pardavimo sumą.
Santrauka: "Power Query" veiksmai, sukurti atliekant 2 užduotį
Kai "Power Query" atliekate užklausos veiksmus, užklausos veiksmai sukuriami ir pateikiami užklausos parametrų srityje, sąraše Pritaikyti veiksmai . Kiekvienas užklausos veiksmas turi atitinkamą "Power Query" formulę, dar žinomą kaip "M" kalba. Daugiau informacijos apie "Power Query" formules rasite Daugiau apie "Power Query" formules.
| Užduotis | Užklausos veiksmas | Formulė |
|---|---|---|
| Prisijungimas prie „OData“ informacijos santraukos | Šaltinis | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"]) |
| Select a table | Naršymas | = Source{[Name="Orders"]}[Data] |
| Lentelės Order_Details išplėtimas | Order_Details išplėtimas | = Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"}) |
| Kitų stulpelių pašalinimas, kad būtų rodomi tik dominantys stulpeliai | RemovedColumns | = Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"}) |
| Kiekvienos „Order_Details“ eilutės sumos apskaičiavimas | Įtrauktas pasirinktinis |
= 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]) |
| Pakeiskite į prasmingesnį pavadinimą, Lne Total | Pervardyto stulpelio | = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}}) |
| Stulpelio OrderDate pakeitimas, kad atspindėtų metus | Išgauti metai | = Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}}) |
| Pakeisti į prasmingesni pavadinimai, UžsakymoData ir Metai |
Pervardytas 1 stulpelis |
Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}}) |
| Eilučių grupavimas pagal stulpelius ProductID ir Year | GroupedRows | = Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}}) |
3 užduotis. Produktų ir pardavimo sumos sujungimas
Naudodami "Power Query" galite sujungti kelias užklausas, jas suliedami arba papildydami. Suliejimo operacija gali būti taikoma bet kuriai "Power Query" lentelės formos užklausai, neatsižvelgiant į duomenų šaltinius, iš kurių gaunami duomenys. Daugiau informacijos apie duomenų šaltinių sujungimą rasite kelių užklausų sujungimas.
Atlikdami šią užduotį sujungsite užklausas Products ir Total Sales naudodami operaciją Suliejimo užklausą ir Išplėtimas , tada į "Excel" duomenų modelį įkelsite užklausą Total Sales per Product
1 veiksmas. ProductID suliejimas su pardavimo sumos užklausa
"Excel" darbaknygėje eikite į produktų užklausą darbalapio skirtuke Produktai .
Užklausoje pasirinkite langelį, tada pasirinkite Užklausų>suliejimas.
Dialogo lange Suliejimas pasirinkite Produktai kaip pirminę lentelę ir pasirinkite Total Sales kaip antrinę arba susijusią užklausą, skirtą sulieti. "Total Sales" taps nauju struktūriniu stulpeliu su išplėtimo piktograma.
Kad Total Sales atitiktų Products pagal ProductID, pasirinkite stulpelį ProductID iš lentelės Products ir stulpelį Order_Details.ProductID iš lentelės Total Sales.
Dialogo lange Privatumo lygiai:
- Pasirinkite Organizacijos, kad nustatytumėte abiejų duomenų šaltinių privatumo lygį.
- Pasirinkite Įrašyti.
Pažymėkite Gerai.
Pastaba
Privatumo lygiai apsaugo vartotojus nuo netyčinio kelių duomenų šaltinių, kurie gali būti asmeniniai arba organizacijos, sujungimo. Atsižvelgiant į užklausą, vartotojas gali netyčia siųsti duomenis iš privačių duomenų šaltinių į kitą duomenų šaltinį, kuris gali būti kenksmingas. "Power Query" analizuoja kiekvieną duomenų šaltinį ir klasifikuoja pagal nustatytą privatumo lygį: Viešasis, Organizacijos ir Asmeninis. Daugiau informacijos apie privatumo lygius žr. Privatumo lygių nustatymas.
Rezultatas
Suliejimo operacija sukuria užklausą. Užklausos rezultatas pateikia visus stulpelius iš pirminės lentelės (Produktai) ir vieną lentelės struktūros stulpelį į susijusią lentelę (Total Sales). Pasirinkite išplėtimo piktogramą, kad įtrauktumėte naujų stulpelių į pirminę lentelę iš antrinės arba susijusios lentelės.
2 veiksmas. Sulieto stulpelio išplėtimas
Atlikdami šį veiksmą, išplėsite sulietą stulpelį, pavadintą NewColumn , kad produktų užklausoje sukurtumėte du naujus stulpelius: Year ir Total Sales.
Duomenų peržiūroje pasirinkite piktogramą Išplėsti (
) šalia Naujas stulpelis.Išplečiamajame sąraše Išplėsti:
- Pasirinkite (Pasirinkti visus stulpelius), jei norite išvalyti visus stulpelius.
- Pasirinkite metus ir bendrą pardavimo sumą.
- Pažymėkite Gerai.
Pervardykite šiuos du stulpelius į Year ir Total Sales.
Norėdami sužinoti, kokie produktai ir kuriais metais buvo geriausiai parduodami, pasirinkite Rūšiuoti mažėjimo tvarka pagal bendrą pardavimo sumą.
Pervardykite užklausą į Total Sales per Product.
Rezultatas
3 veiksmas: Produkto pardavimo sumos užklausos įkėlimas į "Excel" duomenų modelį
Šiuo veiksmu įkeliate užklausą į "Excel" duomenų modelį, kad sukurtumėte ataskaitą, susietą su užklausos rezultatu. Įkėlę duomenis į "Excel" duomenų modelį, galite naudoti "Power Pivot" tolesnei duomenų analizei.
- Pasirinkite Pagrindinis>, Uždaryti & Įkelti.
- Dialogo lange Duomenų importavimas įsitikinkite, kad pasirinkote Įtraukti šiuos duomenis į duomenų modelį. Norėdami gauti daugiau informacijos apie šio dialogo lango naudojimą, pažymėkite klaustuką (?).
Rezultatas
Turite užklausą Produkto pardavimo suma, kuri sujungia duomenis iš Products.xlsx failo ir "Northwind OData" informacijos santraukos. Ši užklausa taikoma "Power Pivot" modeliui. Be to, užklausos pakeitimai pakeičia ir atnaujina rezultatų lentelę duomenų modelyje.
Santrauka: "Power Query" veiksmai, sukurti atliekant 3 užduotį
Kai "Power Query" atliekate užklausų suliejimo veiksmus, užklausos veiksmai sukuriami ir pateikiami užklausos parametrų srityje, sąraše Pritaikyti veiksmai . Kiekvienas užklausos veiksmas turi atitinkamą "Power Query" formulę, dar žinomą kaip "M" kalba. Daugiau informacijos apie "Power Query" formules rasite Daugiau apie "Power Query" formules.
| Užduotis | Užklausos veiksmas | Formulė |
|---|---|---|
| ProductID suliejimas su užklausa Total Sales | Šaltinis (suliejimo operacijos duomenų šaltinis) | = Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter) |
| Sulieto stulpelio išplėtimas | Išplėstas bendras pardavimas | = Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"}) |
| Dviejų stulpelių pervardijimas | Pervardyto stulpelio | = Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}}) |
| Rikiuoti bendrą pardavimo sumą didėjančia tvarka | Surūšiuotos eilutės | = Table.Sort(#"Pervardyti stulpeliai",{{"Total Sales", Order.Ascending}}) |