Uzziniet, kā apvienot vairākus datu avotus (Power Query)

Šajā uzdevumā jūs importējat produktus no faila Produkti un Orders.xlsx (lejupielādēti un pārdēvēti augstāk) Excel darbgrāmatā, rindas paaugstināt par kolonnu galvenēm, noņemt dažas kolonnas un vaicājumu ielādēt darblapā.

1. darbība. Izveidojiet savienojumu ar Excel darbgrāmatu

1. Izveidojiet Excel darbgrāmatu.

2. Atlasiet Dati > Iegūt datus > no faila >No darbgrāmatas.

3. Dialoglodziņā Datu importēšana atrodiet lejupielādēto Products.xlsx un pēc tam atlasiet Atvērt.

4. Navigācijas rūtī veiciet dubultklikšķi uz tabulas Produkti. Tiek parādīts Vaicājumu redaktors Power Vaicājumu redaktors.

2. darbība. Izpētiet vaicājuma darbības

Pēc noklusējuma Power Query jūsu ērtībām automātiski pievieno vairākas darbības. Lai uzzinātu vairāk , izskatiet katru darbību sadaļā Lietotās darbības vaicājuma iestatījumu rūtī.

1. Ar peles labo pogu noklikšķiniet uz darbības Avots un atlasiet Rediģēt iestatījumus. Šī darbība tika izveidota, importot darbgrāmatu.

2. Ar peles labo pogu noklikšķiniet uz navigācijas darbības un atlasiet Rediģēt iestatījumus. Šī darbība tika izveidota, kad tabulu atlasījāt dialoglodziņā Navigācija.

3. Ar peles labo pogu noklikšķiniet uz darbības Mainīts tips un atlasiet Rediģēt iestatījumus. Šo darbību izveidoja Power Query ar kuru tiek izsecināti katras kolonnas datu tipi. Lai skatītu pilnu formulu, atlasiet lejupvērsto bultiņu formulu joslas labajā pusē.

3. darbība. Noņemiet citas kolonnas, lai rādītu tikai vajadzīgās kolonnas

Šajā darbībā tiek noņemtas visas kolonnas, izņemot Produkta_ID, Produkta_nosaukums, Kategorijas_ID un Vienību_skaits.

1. Datu priekšskatījumā atlasiet kolonnu Produkta_ID, Produkta_nosaukums, Kategorijas_ID un Vienību_skaits (izmantojiet kombināciju Ctrl+klikšķis vai Shift+klikšķis).

2. Atlasiet Noņemt kolonnas > Noņemt citas kolonnas.

   

4. darbība. Produktu vaicājuma ielāde

Šajā darbībā vaicājums Produkti tiek ielādēts Excel darblapā.

• Atlasiet Sākums > Aizvērt un & Ielādēt. Vaicājums tiek parādīts jaunā Excel darblapā.

Kopsavilkums. Power Query 1. uzdevumā izveidoto darbību aprakstu

Kad programmā Power Query veicat vaicājumu darbības, vaicājumu iestatījumu rūts sarakstā Lietotās darbības tiek izveidoti un uzskaitīti vaicājuma soļi. Katram vaicājumam ir atbilstoša Power Query formula, kas tiek dēvēta arī par "M" valodu. Papildinformāciju par formulām Power Query rakstā Formulu Power Query programmā Excel.

Uzdevums	Vaicājuma solis	Formula
Excel darbgrāmatas importēšana	Avots	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Atlasiet tabulu Produkti.	Naviģēt.	= Source{[Item="Products",Kind="Table"]}[Data]
Power Query automātiski nosaka kolonnu datu tipus	Mainīts tips	= Table.TransformColumnTypes(Products_Table,{{"Produkta_ID", Int64.Type}, {"Produkta_nosaukums", ierakstiet tekstu}, {"Piegādātāja_ID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Pārtraukts", ierakstiet logical}})
Noņemt citas kolonnas, lai rādītu tikai vajadzīgās kolonnas	Noņemtas citas kolonnas	= Table.SelectColumns(FirstRowAsHeader,{"Produkta_ID", "Produkta_nosaukums", "Kategorijas_ID", "Daudzums_vienību"})

Šajā uzdevumā dati tiek importēti Excel darbgrāmatā no parauga Northwind OData plūsmas http://services.odata.org/Northwind/Northwind.svc,izvērsiet Order_Details tabulu, noņemiet kolonnas, aprēķiniet rindas kopsummu, pārvērtiet Pasūtījuma_datums, grupējiet rindas pēc Produkta_ID un Gads, pārdēvējiet vaicājumu un atspējojiet vaicājuma lejupielādi Excel darbgrāmatā.

1. darbība. Savienojuma izveide ar OData plūsmu

1. Atlasiet Datu > Iegūt datus> no citiem avotiem >no OData plūsmas.

2. Dialoglodziņā OData plūsma ievadiet Northwind OData plūsmas URL.

3. Atlasiet Labi.

4. Navigācijas rūtī veiciet dubultklikšķi uz tabulas Pasūtījumi .

2. darbība. Izvērsiet tabulu Pasūtījumu_dati

Šajā darbībā tiek izvērsta tabula Pasūtījumu_dati, kas ir saistīta ar tabulu Pasūtījumi, lai apvienotu tabulas Pasūtījumu_dati kolonnas Produkta_ID, Vienības_cena un Daudzums tabulā Pasūtījumi. Izvēršanas darbība apvieno kolonnas no saistītas tabulas tēmas tabulā. Kad tiek palaists vaicājums, rindas no saistītās tabulas (Order_Details) tiek apvienotas ar primārās tabulas rindām (Pasūtījumi).

Kolonnā Power Query kurā ir saistīta tabula, šūnā ir vērtība Ierakstsvai Tabula. Tās tiek dēvētas par strukturētām kolonnām. Ieraksts norāda vienu saistīto ierakstu un atspoguļo relācijuviens pret vienu ar pašreizējiem datiem vai primāro tabulu. Tabula norāda saistītu tabulu un apzīmē relāciju viens pret daudziem ar pašreizējo vai primāro tabulu. Strukturēta kolonna apzīmē relāciju datu avotā, kuram ir relāciju modelis. Piemēram, strukturēta kolonna norāda entītiju ar ārējās atslēgas saistību OData plūsmā vai ārējās atslēgas relāciju SQL Server bāzē.

Kad ir izvērsta tabula Pasūtījumu_dati, tabulai Pasūtījumi tiek pievienotas trīs jaunas kolonnas un papildu rindas — pa vienai katrā ligzdotās vai saistītās tabulas rindā.

1. Datu priekšskatījumā ritiniet horizontāli līdz rūts Order_Details kolonnai.

2. Kolonnā Order_Details atlasiet izvēršanas ikonu ().

3. Nolaižamajā izvēlnē Izvēršana:

   1. Atlasiet (Atlasīt visas kolonnas), lai notīrītu visas kolonnas.

   2. Atlasiet Produkta_ID, Vienības_cena un Daudzums.

   3. Atlasiet Labi.

      

      Piezīme.: Šajā Power Query varat izvērst tabulas, kas ir saistītas no kolonnas, un apkopot saistītās tabulas kolonnas pirms tēmas tabulas datu izvēršanas. Lai iegūtu papildinformāciju par apkopošanas darbību veikšanu, skatiet sadaļu Datu apkopošana no kolonnas.

3. darbība. Noņemiet citas kolonnas, lai rādītu tikai vajadzīgās kolonnas

Šajā darbībā tiek noņemtas visas kolonnas, izņemot kolonnu Pasūtījuma_datums, Produkta_ID, Vienības_cena un Daudzums. 

1. Sadaļā Datu priekšskatījums atlasiet šādas kolonnas:

   1. Atlasiet pirmo kolonnu Pasūtījuma_ID.

   2. Shift+Noklikšķiniet uz pēdējās kolonnas Ekspediators.

   3. Turiet nospiestu taustiņu Ctrl un noklikšķiniet kolonnā Pasūtījuma_datums, Pasūtījumu_dati.Produkta_ID, Pasūtījumu_dati.Vienības_cena un Pasūtījumu_dati.Daudzums.

2. Ar peles labo pogu noklikšķiniet atlasītās kolonnas galvenē un atlasiet Noņemt citas kolonnas.

4. darbība. Aprēķiniet katras tabulas Pasūtījumu_dati rindas kopsummu

Šajā darbībā tiek izveidota kolonna Pielāgota kolonna, lai aprēķinātu katras tabulas Pasūtījumu_dati rindas kopsummu.

1. Datu priekšskatījumā atlasiet tabulas ikonu () priekšskatījuma augšējā kreisajā stūrī.

2. Noklikšķiniet uz Pievienot pielāgotu kolonnu.

3. Dialoglodziņa Pielāgota kolonna lodziņā Pielāgota kolonnas formula ievadiet [Order_Details.Vienības_cena] * [Order_Details.Daudzums].

4. Lodziņā Jaunas kolonnas nosaukums ievadiet Rindas kopsumma.

5. Atlasiet Labi.

5. darbība. Transformējiet gada kolonnu Pasūtījuma_datums

Šajā darbībā tiek transformēta kolonna Pasūtījuma_datums, lai atveidotu pasūtījuma datuma gadu.

1. Datu priekšskatījumā ar peles labo pogu noklikšķiniet uz kolonnas Pasūtījuma_datums un atlasiet Transformēt > Gads.

2. Pārdēvējiet kolonnu Pasūtījuma_datums par Gads:

   1. Veiciet dubultklikšķi uz kolonnas Pasūtījuma_datums un ievadiet Gads.

   2. Right-Click kolonnā Pasūtījuma_datums atlasiet Pārdēvēt un ievadiet Gads.

6. darbība. Grupējiet rindas pēc kolonnas Produkta_ID un Gads

1. Sadaļā Datu priekšskatījums atlasiet Gads un Order_Details.ProduktaID.

2. Right-Click kādu no galvenēm un atlasiet Grupēt pēc.

3. Dialoglodziņā Grupēt pēc:

   1. Tekstlodziņā Jaunas kolonnas nosaukums ievadiet Pārdošanas kopsummas.

   2. Nolaižamajā izvēlnē Darbība atlasiet Summa.

   3. Nolaižamajā izvēlnē Kolonna atlasiet Rindas kopsumma.

4. Atlasiet Labi.

   

7. darbība. Pārdēvējiet vaicājumu

Pirms pārdošanas datu importēšanas programmā Excel pārdēvējiet vaicājumu.

• Vaicājuma iestatījumu rūts lodziņā Nosaukums ievadiet Pārdošanas kopsummas.

Rezultāti: pēdējais vaicājums 2. uzdevumam

Pēc katras darbības veikšanas tiks iegūts vaicājums Pārdošanas kopsummas par Northwind OData plūsmu.

Kopsavilkums. Power Query 2. uzdevumā izveidotās darbības 

Kad programmā Power Query veicat vaicājumu darbības, vaicājumu iestatījumu rūts sarakstā Lietotās darbības tiek izveidoti un uzskaitīti vaicājuma soļi. Katram vaicājumam ir atbilstoša Power Query formula, kas tiek dēvēta arī par "M" valodu. Papildinformāciju par formulām Power Query skatiet rakstā Informācija Power Query formulām.

Uzdevums	Vaicājuma solis	Formula
Savienojuma izveide ar OData plūsmu	Avots	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
Select a table	Navigācija	= Source{[Name="Pasūtījumi"]}[Data]
Izvērst tabulu Pasūtījumu_dati	Izvērst tabulu Pasūtījumu_dati	= Table.ExpandTableColumn(Pasūtījumi, "Order_Details", {"Produkta_ID", "VienībasCena", "Daudzums"}, {"Order_Details.Produkta_ID", "Order_Details.Vienības_cena", "Order_Details.Daudzums"})
Noņemt citas kolonnas, lai rādītu tikai vajadzīgās kolonnas	Noņemtās_kolonnas	= Table.RemoveColumns(#"Izvērst Order_Details",{"Pasūtījuma_ID", "Klienta_ID", "Darbinieka_ID", "Nepieciešamais_datums", "Nosūtīšanas_datums", "Piegādes_indekss", "Transports", "Piegādes_vārds", "Piegādes_adrese", "Piegādes_reģions", "Piegādes_pasta_indekss", "Piegādes_valsts", "Klients", "Darbinieks", "Piegādes_reģions"})
Aprēķiniet katras tabulas Pasūtījumu_dati rindas kopsummu	Pievienots pielāgots	= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Izvērsts Order_Details", "Rindas kopsumma", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
Mainiet uz jēgpilnāku nosaukumu, ar nosaukumu Lne Total	Pārdēvētās kolonnas	= Table.RenameColumns(InsertedCustom,{{"Pielāgots", "Rindas kopsumma"}})
Transformēt kolonnu Pasūtījuma_datums, lai atveidotu gadu	Izvilkts gads	= Table.TransformColumns(#"Grupētās rindas",{{"Gads", Date.Year, Int64.Type}})
Mainīt uz  jēgpilnākus nosaukumus, OrderDate un Year	Pārdēvētās kolonnas 1	Tabula.Pārdēvēt_kolonnas (TransformedColumn,{{"Pasūtījuma_datums", "Gads"}})
Grupēt rindas pēc kolonnas Produkta_ID un Gads	Grupētās_rindas	= Table.Group(RenamedColumns1, {"Gads", "Order_Details.ProductID"}, {{"Pārdošanas apjoms", each List.Sum([Line Total]), type number}})

Power Query ļauj apvienot vairākus vaicājumus, sapludinot vai pievienojot tos. Sapludināšanas darbība tiek veikta jebkurā Power Query vaicājumā ar tabulāru formu neatkarīgi no datu avota, no kura ir iegūti dati. Papildinformāciju par datu avotu apvienošanu skatiet sadaļā Vairāku vaicājumu apvienošana.

Šajā uzdevumā tiek apvienoti produktu un pārdošanas kopsummu vaicājumi, izmantojot sapludināšanas vaicājumu un izvēršanas darbību, un pēc tam Excel datu modelī ielādējiet vaicājumu Produkta pārdošanas kopsummas.

1. darbība. Sapludiniet kolonnu Produkta_ID pārdošanas kopsummu vaicājumā

1. Excel darbgrāmatā naviģējiet uz vaicājumu Produkti darblapā Produkti .

2. Vaicājumā atlasiet šūnu un pēc tam atlasiet Vaicājums un > sapludināt.

3. Lai sapludinātu, dialoglodziņā Sapludināšana kā primāro tabulu atlasiet Produkti un kā sekundāro vai saistīto vaicājumu atlasiet Pārdošanas kopsummas. Pārdošanas kopsummas kļūs par jaunu strukturētu kolonnu ar izvēršanas ikonu.

4. Lai vaicājumus Pārdošanas kopsummas un Produkti saskaņotu pēc Produkta_ID, tabulā Produkti atlasiet kolonnu Produkta_ID un tabulā Pārdošanas kopsummas atlasiet Pasūtījumu_dati.Produkta_ID.

5. Dialoglodziņā Konfidencialitātes līmeņi:

   1. Kā konfidencialitātes līmeni abiem datu avotiem atlasiet Organizācijas.

   2. Atlasiet Saglabāt.

6. Atlasiet Labi.

   Drošības piezīme.:  Konfidencialitātes līmeņi neļauj lietotājiem nejauši apvienot datus no vairākiem datu avotiem, kas varētu būt privāti vai organizācijas. Atkarībā no vaicājuma lietotājs var nejauši nosūtīt datus no privāta datu avota uz citu datu avotu, kas varētu būt ļaunprātīgs. Power Query analizē katru datu avotu un klasificē to norādītajā konfidencialitātes līmenī: publisks, organizācijas un privāts. Papildinformāciju par konfidencialitātes līmeņiem skatiet sadaļā Konfidencialitātes līmeņu iestatīšana.

   

Rezultāts

Sapludināšanas darbība izveido vaicājumu. Vaicājuma rezultātā tiek iekļautas visas primārās tabulas (Produkti) kolonnas un viena tabulas strukturēta kolonna ar saistīto tabulu (Pārdošanas kopsummas). Atlasiet izvēršanas ikonu, lai primārajai tabulai pievienotu jaunas kolonnas no sekundārās vai saistītās tabulas.

2. darbība. Sapludinātas kolonnas izvēršanas darbība

Šajā darbībā tiek izvērsta sapludinātā kolonna ar nosaukumu Jauna_kolonna, lai vaicājumā Produkti izveidotu divas jaunas kolonnas: Gads un Pārdošanas kopsummas.

1. Datu priekšskatījumāatlasiet Izvērst ikonu () blakus kolonnai Jauna_kolonna.

2. Nolaižamajā sarakstā Izvērst:

   1. Atlasiet (Atlasīt visas kolonnas), lai notīrītu visas kolonnas.

   2. Atlasiet Gads un Pārdošanas kopsummas.

   3. Atlasiet Labi.

3. Pārdēvējiet šīs divas kolonnas par Gads un Pārdošanas kopsummas.

4. Lai uzzinātu, kuriem produktiem un kuros gados ir visaugstākais pārdošanas apjoms, atlasiet Kārtot dilstošā secībā pēc pārdošanas kopsummas.

5. Pārdēvējiet vaicājumu par Produkta pārdošanas kopsummas.

Rezultāts

3. darbība. Ielādējiet produkta pārdošanas kopsummu vaicājumu Excel datu modelī

Šajā darbībā vaicājums tiek ielādēts Excel datu modelī, lai izveidotu atskaiti, kas saistīta ar vaicājuma rezultātu. Ielādējot datus Excel datu modelī, varat izmantot Power Pivot, lai sīkāk analizētu datus.

1. Atlasiet Sākums > Aizvērt un & Ielādēt.

2. Pārliecinieties , vai dialoglodziņā Datu importēšana ir atlasīta izvēles rūtiņa Pievienot šos datus datu modelim. Lai iegūtu papildinformāciju par šī dialoglodziņa izmantošana, atlasiet jautājuma zīmi (?).

Rezultāts

Jums ir vaicājums Produkta pārdošanas kopsummas , kurā ir apvienoti dati no Products.xlsx northwind OData plūsmas. Šis vaicājums tiek lietots Power Pivot modelī. Turklāt vaicājuma izmaiņas modificē un atsvaidzina iegūto tabulu datu modelī.

Kopsavilkums. Power Query 3. uzdevumā izveidotās darbības

Kad programmā Power Query veicat sapludināšanas vaicājumu darbības, vaicājumu iestatījumu rūts sarakstā Lietotās darbības tiek izveidoti un uzskaitīti vaicājuma soļi. Katram vaicājumam ir atbilstoša Power Query formula, kas tiek dēvēta arī par "M" valodu. Papildinformāciju par formulām Power Query skatiet rakstā Informācija Power Query formulām.

Uzdevums	Vaicājuma solis	Formula
Sapludināt kolonnu Produkta_ID pārdošanas kopsummas vaicājumā	Avots (datu avots sapludināšanas darbībai)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Izvērst sapludinātu kolonnu	Izvērsti pārdošanas kopsummas	= Table.ExpandTableColumn(Avots, "Pārdošanas kopsumma", {"Gads", "Pārdošanas kopsumma"}, {"Pārdošanas apjoms.gads", "Pārdošanas kopsummas.Pārdošanas apjoms"})
Divu kolonnu pārdēvēšana	Pārdēvētās kolonnas	= Table.RenameColumns(#"Izvērstā pārdošanas kopsumma",{{"Pārdošanas_apjoms.Gads", "Gads"}, {"Pārdošanas_apjoms.Pārdošanas_apjoms", "Pārdošanas kopsumma"}})
Pārdošanas kopsummas kārtošana augošā secībā	Kārtotas rindas	= Table.Sort(#"Pārdēvētās kolonnas",{{"Pārdošanas kopsummas", Order.Ascending}})