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

Šajā apmācībā varat izmantot Power Query vaicājumu redaktoru, lai importētu datus no lokāla Excel faila, kurā ir informācija par produktiem, un OData plūsmas, kurā ir informācija par produktu pasūtījumiem. Veiciet transformēšanas un apkopošanas darbības un apvienojiet datus no abiem avotiem, lai izveidotu atskaiti "Produktu un gadu pārdošanas kopsummas".   

Lai veiktu šo apmācību, ir nepieciešama darbgrāmata Produkti. Dialoglodziņā Saglabāt kā nosauciet failu par Produkti un pasūtījumi.xlsx.

Š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 tiek paaugstinātas par kolonnu galvenēm, noņemt dažas kolonnas un vaicājums tiek ielādēts 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 Power Query redaktors.

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

Pēc noklusējuma Power Query automātiski pievieno vairākas darbības kā ērtī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, kas izsecinātu katras kolonnas datu tipus. 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_IDun Vienību_skaits (izmantojiet kombināciju Ctrl+klikšķis vai Shift+klikšķis).

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

    Paslēpiet 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 darbības, kas izveidotas 1. uzdevumā

Kad līdzeklī Power Query veicat ar vaicājumiem saistītas darbības, rūts Vaicājumu iestatījumi 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 Power Query formulām skatiet rakstā Power Query formulu izveide 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 Dati>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).

Power Query kolonnā, 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āciju viens 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 datu 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 datu Order_Details kolonnai.

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

  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_cenaun Daudzums.

    3. Atlasiet Labi.

      Tabulas Pasūtījumu_dati saites izvēršana

      Piezīme.: Līdzeklī 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ījumsatlasiet šādas kolonnas:

    1. Atlasiet pirmo kolonnu Pasūtījuma_ID.

    2. Shift+Noklikšķiniet pēdējā kolonnā 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 ( Tabulas ikona ) 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.

Aprēķiniet katras tabulas Pasūtījumu_dati rindas kopsummu

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 unievadiet Gads.

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

  1. Sadaļā Datu priekšskatījumsatlasiet Gads un Order_Details.Produkta_ID.

  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.

    Grupēšana pēc dialoglodziņa apkopošanas darbībām

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.

Pārdošanas kopsummas

Kopsavilkums: Power Query darbības, kas izveidotas 2. uzdevumā 

Kad līdzeklī Power Query veicat ar vaicājumiem saistītas darbības, rūts Vaicājumu iestatījumi 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 Power Query formulām skatiet sadaļā Uzzināt par 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", "Sūtīt_izmantojot", "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(#"Expanded Order_Details", "Line Total", 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,{{"Custom", "Line Total"}})

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 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 rakstā Konfidencialitātes līmeņu iestatīšana.

    Dialoglodziņš Sapludināš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.

Beigu sapludināšana

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. Sadaļā Datu priekšskatījumsatlasiet Izvērst ikonu ( Izvērst ) blakus newcolumn.

  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

Tabulas izvēršanas saite

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 darbības, kas izveidotas 3. uzdevumā

Kad līdzeklī Power Query veicat ar sapludināšanas vaicājumiem saistītas 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 Power Query formulām skatiet sadaļā Uzzināt par 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ērsti pārdošanas kopsummas",{{"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}})

Papildinformācija

Power Query programmai Excel palīdzības

Vai nepieciešama papildu palīdzība?

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka jums varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta speciālistiem.

×