Sužinokite, kaip sujungti kelis duomenų šaltinius ("Power Query")

Šioje užduotyje galite importuoti produktus iš produktų ir Orders.xlsx (atsisiųsto ir pervardyto aukščiau) failo į "Excel" darbaknygę, perkelti eilutes į stulpelių antraštes, pašalinti kai kuriuos stulpelius ir įkelti užklausą į darbalapį.

1 veiksmas. Prisijungimas prie „Excel“ darbaknygės

1. „Excel“ darbaknygės kūrimas

2. Pasirinkite Duomenų > Gauti duomenis > iš failo > iš darbaknygės.

3. Dialogo lange Duomenų importavimas raskite ir raskite atsisiųstą Products.xlsx failą, tada pasirinkite Atidaryti.

4. Srityje Naršyklė dukart spustelėkite lentelę Produktai . Rodomas maitinimo Užklausų rengyklė.

2 veiksmas: peržiūrėkite užklausos veiksmus

Pagal numatytuosius nustatymus "Power Query" automatiškai įtraukia kelis veiksmus, kad būtų patogiau. Norėdami sužinoti daugiau, ištirkite kiekvieną veiksmą srities Užklausos parametrai dalyje Pritaikyti veiksmai.

1. Dešiniuoju pelės mygtuku spustelėkite šaltinio veiksmą ir pasirinkite Redaguoti parametrus. Šis veiksmas buvo sukurtas, kai importavote darbaknygę.

2. Dešiniuoju pelės mygtuku spustelėkite veiksmą Naršymas ir pasirinkite Redaguoti parametrus. Šis veiksmas buvo sukurtas, kai dialogo lange Naršymas pasirinkote lentelę.

3. Dešiniuoju pelės mygtuku spustelėkite veiksmą Pakeistas tipas ir pasirinkite Redaguoti parametrus. Šį veiksmą sukūrė "Power Query", kuri numačiusi kiekvieno stulpelio duomenų tipus. Pasirinkite rodyklę žemyn, esančią į 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.

1. Dalyje Duomenų peržiūra pasirinkite stulpelius ProductID, ProductName, CategoryID ir QuantityPerUnit (naudokite Ctrl + spustelėjimas arba Shift + spustelėjimas).

2. Pasirinkite Šalinti stulpelius > Pašalinti kitus stulpelius.

   

4 veiksmas: produktų užklausos įkėlimas

Atlikdami šį veiksmą, įkelsite užklausą Produktai į "Excel" darbalapį.

• Pasirinkite Pagrindinis > Uždaryti & Įkelti. Užklausa rodoma naujame "Excel" darbalapyje.

Santrauka: "Power Query" 1 užduotyje sukurtus veiksmus

Atliekant užklausų veiksmus "Power Query", užklausos veiksmai sukuriami ir pateikiami srityje Užklausos parametrai, sąraše Taikomi veiksmai. Kiekvienas užklausos veiksmas turi atitinkamą "Power Query" formulę, dar vadinamą "M" kalba. Daugiau informacijos apie "Power Query" formules žr. "Power Query" formulių kūrimas programoje "Excel".

Užduotis	Užklausos veiksmas	Formulė
"Excel" darbaknygės importavimas	Šaltinis	= Excel.Workbook(File.Contents("C:\Products and 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"})

Atlikdami šią užduotį importuojate duomenis į "Excel" darbaknygę iš "Northwind OData" informacijos santraukos http://services.odata.org/Northwind/Northwind.svc,išskleiskite Order_Details lentelę, pašalinkite stulpelius, apskaičiuokite bendrą eilučių sumą, transformuokite OrderDate, grupuojate eilutes pagal ProductID ir Year, pervardykite užklausą ir išjunkite užklausų atsisiuntimą į "Excel" darbaknygę.

1 veiksmas: prisijungimas prie "OData" informacijos santraukos

1. Pasirinkite Duomenys > Gauti duomenis > iš kitų šaltinių > iš "OData" informacijos santraukos.

2. Dialogo lange „OData“ informacijos santrauka įveskite „Northwind OData“ informacijos santraukos URL.

3. Pažymėkite Gerai.

4. Srityje Naršyklė dukart spustelėkite lentelę Užsakymai .

2 veiksmas. Lentelės Order_Details 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 sujungiamos į eilutes su pirmine lentele (Užsakymai).

"Power Query" stulpelyje, kuriame yra susijusi lentelė, langelyje yra reikšmė Įrašas arba Lentelė. Jie vadinami struktūriniais stulpeliais. Įrašas nurodo vieną susijusį įrašą ir nurodoryšį "vienas su vienu" su dabartiniais duomenimis arba pirmine lentele. Lentelė nurodo susijusią lentelę ir nurodo ryšį "vienas su daugeliu" su dabartine arba pirmine lentele. Struktūrinis stulpelis nurodo ryšį duomenų šaltinyje, kuriame yra sąryšinis modelis. Pavyzdžiui, 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 lentelę Order_Details, į lentelę Orders įtraukiami trys nauji stulpeliai ir papildomos eilutės, po vieną kiekvienai įdėtosios arba susietosios lentelės eilutei.

1. Duomenų peržiūros rodinyje slinkite horizontaliai iki Order_Details stulpelio.

2. Stulpelyje Order_Details pasirinkite išplėtimo piktogramą ().

3. Išskleidžiamajame sąraše Išplėsti:

   1. Pasirinkite (Pasirinkti visus stulpelius), kad išvalytumėte visus stulpelius.

   2. Pasirinkite Produkto ID, Vieneto kaina ir Kiekis.

   3. Pažymėkite Gerai.

      

      Pastaba: "Power Query" galima išplėsti lenteles, susietas su stulpeliu, ir agreguoti susietos lentelės stulpelius prieš išplečiant duomenis temos lentelėje. Daugiau informacijos apie agregavimo operacijų atlikimą žr. Stulpelio duomenų agregavimas.

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. 

1. Dalyje Duomenų peržiūra pažymėkite šiuos stulpelius: 

   1. Pažymėkite pirmą stulpelį Užsakymo ID.

   2. Shift + spustelėkite paskutinį stulpelį Siuntėjas.

   3. Shift + spustelėkite stulpelius OrderDate, Order_Details.ProductID, Order_Details.UnitPrice ir Order_Details.Quantity.

2. Dešiniuoju pelės mygtuku spustelėkite pasirinkto stulpelio antraštę ir pasirinkite Š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.

1. Dalyje Duomenų peržiūra pasirinkite peržiūros viršutiniame kairiajame kampe esančią lentelės piktogramą ().

2. Spustelėkite Įtraukti pasirinktinį stulpelį.

3. Dialogo lango Pasirinktinis stulpelis lauke Pasirinktinio stulpelio formulė įveskite [Order_Details.Vieneto_kaina] * [Order_Details.Kiekis].

4. Lauke Naujas stulpelio pavadinimas įveskite Eilučių suma.

5. Pažymėkite Gerai.

5 veiksmas. OrderDate metų stulpelio keitimas

Atlikdami šį veiksmą, pakeisite stulpelį OrderDate, kad jame atsispindėtų užsakymo datos metai.

1. Dalyje Duomenų peržiūra dešiniuoju pelės mygtuku spustelėkite stulpelį OrderDate ir pasirinkite Transformuoti > metus.

2. Stulpelio OrderDate pervardijimas į Year:

   1. Dukart spustelėkite stulpelį OrderDate ir įveskite Year arba

   2. Right-Click stulpelyje Užsakymo_data pasirinkite Pervardyti ir įveskite Year.

6 veiksmas. Eilučių grupavimas pagal stulpelius ProductID ir Year

1. Dalyje Duomenų peržiūra pasirinkite Metai ir Order_Details.ProductID.

2. Right-Click vieną iš antraščių ir pasirinkite Grupuoti pagal.

3. Dialogo lange Grupuoti pagal :

   1. Teksto lauke Naujas stulpelio pavadinimas įveskite Total Sales.

   2. Išskleidžiamajame sąraše Operacijos pasirinkite Suma.

   3. Išskleidžiamajame sąraše Stulpelis pasirinkite Eilučių suma.

4. Pažymėkite Gerai.

   

7 veiksmas. Užklausos pervardijimas

Prieš importuodami pardavimo duomenis į "Excel", pervardykite užklausą:

• Srities 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 2 užduotyje 

Atliekant užklausų veiksmus "Power Query", užklausos veiksmai sukuriami ir pateikiami srityje Užklausos parametrai, sąraše Taikomi veiksmai. Kiekvienas užklausos veiksmas turi atitinkamą "Power Query" formulę, dar vadinamą "M" kalba. Daugiau informacijos apie "Power Query" formules žr. Sužinokite 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	Pridėta pasirinktinių	= 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])
Pakeisti į prasmingesnį pavadinimą, Lne Total	Pervardyti stulpeliai	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Stulpelio OrderDate pakeitimas, kad atspindėtų metus	Išgauti metai	= Table.TransformColumns(#"Sugrupuotos eilutės",{{"Year", Date.Year, Int64.Type}})
Pakeisti į  prasmingesni pavadinimai, OrderDate ir Year	Pervardyti 1 stulpeliai	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}})

"Power Query" leidžia sujungti kelias užklausas jas suliejant arba pridedant. Operacija Sulieti atliekama su bet kuria lentelės formos "Power Query" užklausa, neatsižvelgiant į duomenų šaltinį, iš kurio gaunami duomenys. Daugiau informacijos apie duomenų šaltinių sujungimą žr. Kelių užklausų sujungimas.

Atlikdami šią užduotį sujungsite užklausas Produktai ir Total Sales naudodami užklausą Sulieti ir Išplėsti , tada įkelsite užklausą Total Sales per Product į "Excel" duomenų modelį.

1 veiksmas. ProductID suliejimas su pardavimo sumos užklausa

1. "Excel" darbaknygėje eikite į užklausą Produktai darbalapio skirtuke Produktai .

2. Pažymėkite užklausos langelį, tada pasirinkite Užklausos > sulieti.

3. Dialogo lange Sulieti pasirinkite Produktai kaip pirminę lentelę ir pasirinkite Total Sales kaip antrinę arba susijusią užklausą, kurią norite sulieti. Total Sales taps nauju struktūriniu stulpeliu su išplėtimo piktograma.

4. Kad Total Sales atitiktų Products pagal ProductID, pasirinkite stulpelį ProductID iš lentelės Products ir stulpelį Order_Details.ProductID iš lentelės Total Sales.

5. Dialogo lange Privatumo lygiai:

   1. Pasirinkite Organizacijos, kad nustatytumėte abiejų duomenų šaltinių privatumo lygį.

   2. Pasirinkite Įrašyti.

6. Pažymėkite Gerai.

   Saugos pranešimas:  Privatumo lygiai neleidžia vartotojui netyčia sujungti duomenų iš kelių duomenų šaltinių, kurie gali būti privatūs arba organizacijos. Atsižvelgiant į užklausą, vartotojas gali netyčia siųsti duomenis iš privataus duomenų šaltinio į kitą duomenų šaltinį, kuris gali būti kenkėjiškas. "Power Query" analizuoja kiekvieną duomenų šaltinį ir klasifikuoja pagal apibrėžtą privatumo lygį: viešasis, organizacinis ir privatus. Daugiau informacijos apie privatumo lygius žr. Privatumo lygių nustatymas.

   

Rezultatas

Operacija Sulieti sukuria užklausą. Užklausos rezultate yra visi stulpeliai iš pirminės lentelės (Produktai) ir vienas lentelės struktūrinis stulpelis su susijusia lentele (Total Sales). Pasirinkite piktogramą Išplėsti , kad įtrauktumėte naujų stulpelių į pirminę lentelę iš antrinės arba susijusios lentelės.

2 veiksmas: sulieto stulpelio išplėtimas

Atlikdami šį veiksmą, galite išplėsti sulietą stulpelį pavadinimu NewColumn , kad sukurtumėte du naujus stulpelius produktų užklausoje: Year ir Total Sales.

1. Dalyje Duomenų peržiūra pasirinkite Išplėsti piktogramą () šalia NewColumn.

2. Išplečiamajame sąraše Išplėsti :

   1. Pasirinkite (Pasirinkti visus stulpelius), kad išvalytumėte visus stulpelius.

   2. Pasirinkite Metai ir Bendras pardavimas.

   3. Pažymėkite Gerai.

3. Pervardykite šiuos du stulpelius į Year ir Total Sales.

4. Norėdami sužinoti, kurie produktai ir kokiais metais produktai gavo didžiausią pardavimo kiekį, pasirinkite Rūšiuoti mažėjimo tvarka pagal bendrą pardavimą.

5. Pervardykite užklausą į Total Sales per Product.

Rezultatas

3 veiksmas: Produkto pardavimo sumos užklausos įkėlimas į „Excel“ duomenų modelį

Atliekant šį veiksmą, įkeliate užklausą į "Excel" duomenų modelį, kad sukurtumėte ataskaitą, sujungtą su užklausos rezultatu. Įkėlus duomenis į "Excel" duomenų modelį galima naudoti "Power Pivot" tolesnei duomenų analizei.

1. Pasirinkite Pagrindinis > Uždaryti & Įkelti.

2. Dialogo lange Duomenų importavimas būtinai pasirinkite Įtraukti šiuos duomenis į duomenų modelį. Norėdami gauti daugiau informacijos apie šio dialogo lango naudojimą, pasirinkite klaustuką (?).

Rezultatas

Turite užklausą Total Sales per Product , kuri sujungia duomenis iš Products.xlsx failo ir "Northwind OData" informacijos santraukos. Ši užklausa taikoma "Power Pivot" modeliui. Be to, užklausos pakeitimai modifikuoja ir atnaujina gaunamą lentelę duomenų modelyje.

Santrauka: "Power Query" veiksmai, sukurti 3 užduotyje

Atliekant užklausų suliejimo veiksmus "Power Query", užklausos veiksmai sukuriami ir pateikiami srityje Užklausos parametrai, sąraše Taikomi veiksmai. Kiekvienas užklausos veiksmas turi atitinkamą "Power Query" formulę, dar vadinamą "M" kalba. Daugiau informacijos apie "Power Query" formules žr. Sužinokite 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"})
Pervardyti du stulpelius	Pervardyti stulpeliai	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Total Sales rūšiavimas didėjimo tvarka	Surūšiuotos eilutės	= Table.Sort(#"Pervardyti stulpeliai",{{"Total Sales", Order.Ascending}})