Š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 apkopojiet datus no abiem avotiem, lai iegūtu pārskatu "Produktu un gada pārdošanas kopsummas".
Lai veiktu šo apmācību, jums ir nepieciešama darbgrāmata Produkti . Dialoglodziņā Saglabāt kā nosauciet failu par Produkti un pasūtījumi.xlsx.
1. uzdevums. Importējiet produktus Excel darbgrāmatā
Šajā uzdevumā jūs importēsit produktus no faila Produkti un Orders.xlsx (lejupielādēts un pārdēvēts iepriekš) Excel darbgrāmatā, paaugstināsit rindas par kolonnu galvenēm, noņemsit dažas kolonnas un ielādēsit vaicājumu darblapā.
1. darbība. Izveidojiet savienojumu ar Excel darbgrāmatu
- Izveidojiet Excel darbgrāmatu.
- Atlasiet datus>Iegūstiet datus>no faila>no darbgrāmatas.
- Dialoglodziņā Datu importēšana atrodiet un atrodiet lejupielādēto Products.xlsx failu un pēc tam atlasiet Atvērt.
- Navigācijas rūtī veiciet dubultklikšķi uz tabulas Produkti. Tiek parādīts Power Query redaktors .
2. darbība. Vaicājuma darbību pārbaude
Pēc noklusējuma Power Query automātiski pievieno vairākas darbības, lai nodrošinātu jūsu ērtības. Lai uzzinātu vairāk, izpētiet katru darbību vaicājumu iestatījumu rūtī sadaļā Lietotās darbības.
- Ar peles labo pogu noklikšķiniet uz avota darbības un atlasiet Rediģēt iestatījumus. Šī darbība tika izveidota, importējot darbgrāmatu.
- Ar peles labo pogu noklikšķiniet uz navigācijas darbības un atlasiet Rediģēt iestatījumus. Šī darbība tika izveidota, atlasot tabulu dialoglodziņā Navigācija .
- 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āja katras kolonnas datu tipus. Atlasiet lejupvērsto bultiņu pa labi no formulu joslas, lai skatītu visu formulu.
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.
- Datu priekšskatījumā atlasiet kolonnas ProduktaID, Produkta_nosaukums, KategorijasID un Vienību_skaits (izmantojiet taustiņu kombināciju Ctrl+klikšķis vai Shift+klikšķis).
- Atlasiet Noņemt kolonnas>Noņemt citas kolonnas.
4. darbība. Ielādējiet produktu vaicājumu
Šajā darbībā vaicājums Produkti tiek ielādēts Excel darblapā.
- Atlasiet Sākums,>Aizvērt & Ielādēt. Vaicājums tiek parādīts jaunā Excel darblapā.
Kopsavilkums: Power Query 1. uzdevumā izveidotās darbības
Kad pievienojumprogrammā 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ļā Power Query formulu izveide programmā Excel.
| Uzdevums | Vaicājuma solis | Formula |
|---|---|---|
| Excel darbgrāmatas importēšana | Avots | = Excel.Workbook(File.Contents("C:\Produkti un Orders.xlsx"), null, true) |
| Atlasiet tabulu Produkti | Naviģēt. | = Source{[Item="Produkti",Kind="Tabula"]}[Dati] |
| Power Query automātiski nosaka kolonnu datu tipus | Mainīts tips | = Table.TransformColumnTypes( Products_Table,{{"Produkta_ID", Int64.Tips}, {"Produkta_nosaukums", tipa teksts}, {"Piegādātāja_ID", Int64.Tips}, {"Kategorijas_ID", Int64.Tips}, {"Daudzums_vienībā", tipa teksts}, {"Vienības_cena", tipa numurs}, {"Vienības_krājumā", Int64.Tips}, {"Pasūtītās_vienības", Int64.Tips}, {"Pārkārtošanas_līmenis", Int64.Tips}, {"Pārtraukta", tips loģisks}}) |
| 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ībā"}) |
2. uzdevums. Pasūtījuma datu importēšana no OData plūsmas
Šī uzdevuma ietvaros jūs importēsit datus Excel darbgrāmatā no parauga Northwind OData plūsmas http://services.odata.org/Northwind/Northwind.svc, izvērsīsit Order_Details tabulu, noņemsit kolonnas, aprēķināsit rindas kopsummu, transformēsiet Pasūtījuma_datumu, grupēsit rindas pēc kolonnas ProduktaID un Gads, pārdēvēsit vaicājumu un atspējosit vaicājuma lejupielādi Excel darbgrāmatā.
1. darbība. Izveidojiet savienojumu ar OData plūsmu
- Atlasīt datus>Iegūt datus>no citiem avotiem>no OData plūsmas.
- Dialoglodziņā OData plūsma ievadiet Northwind OData plūsmas URL.
- Atlasiet Labi.
- Navigācijas rūtī veiciet dubultklikšķi uz tabulas Pasūtījumi.
2. darbība. Order_Details tabulas izvēršana
Š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 rindās ar primāro tabulu (Pasūtījumi).
Pievienojumprogrammā Power Query kolonnai, kurā ir saistīta tabula, šūnā ir vērtība Ieraksts vai Tabula . Tās sauc par strukturētajām kolonnām. Ieraksts norāda vienu saistītu 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 atspoguļo relāciju viens pret daudziem ar pašreizējo vai primāro tabulu. Strukturēta kolonna apzīmē relāciju datu avotā, kam 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 Order_Details tabula ir izvērsta, tabulai Pasūtījumi tiek pievienotas trīs jaunas kolonnas un papildu rindas — pa vienai katrā ligzdotās vai saistītās tabulas rindā.
Datu priekšskatījumā ritiniet horizontāli līdz kolonnai Order_Details.
Kolonnā Order_Details atlasiet izvēršanas ikonu (
).Nolaižamajā izvēlnē Izvēršana:
Atlasiet (Atlasīt visas kolonnas), lai notīrītu visas kolonnas.
Atlasiet Produkta_ID, VienībasCena un Daudzums.
Atlasiet Labi.
Piezīme
Pievienojumprogrammā 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.
Datu priekšskatījumā atlasiet šādas kolonnas:
- Atlasiet pirmo kolonnu — Pasūtījuma_ID.
- Shift+Noklikšķiniet uz pēdējās kolonnas Piegādātājs.
- 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.
Ar peles labo pogu noklikšķiniet atlasītās kolonnas galvenē un atlasiet Noņemt citas kolonnas.
4. darbība. Aprēķiniet katras Order_Details rindas kopsummu
Šajā darbībā tiek izveidota kolonna Pielāgota kolonna, lai aprēķinātu katras tabulas Pasūtījumu_dati rindas kopsummu.
-
Datu priekšskatījumā atlasiet tabulas ikonu (
) priekšskatījuma augšējā kreisajā stūrī. - Noklikšķiniet uz Pievienot pielāgotu kolonnu.
- Dialoglodziņa Pielāgota kolonna lodziņā Pielāgotas kolonnas formula ievadiet [Order_Details.Vienības_cena] * [Order_Details.Daudzums].
- Lodziņā Jaunas kolonnas nosaukums ievadiet Rindas kopsumma.
- 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.
Programmā Datu priekšskatījums ar peles labo pogu noklikšķiniet uz kolonnas Pasūtījuma_datums un atlasiet Transformēt>gadu.
Pārdēvējiet kolonnu Pasūtījuma_datums par Gads:
- Veiciet dubultklikšķi uz kolonnas Pasūtījuma_datums un ievadiet Gads.
- 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
Sadaļā Data Preview, atlasiet Year un Order_Details.ProductID.
Right-Click vienu no galvenēm un atlasiet Grupēt pēc.
Dialoglodziņā Grupēt pēc:
- Tekstlodziņā Jaunas kolonnas nosaukums ievadiet Pārdošanas kopsummas.
- Nolaižamajā izvēlnē Darbība atlasiet Summa.
- Nolaižamajā izvēlnē Kolonna atlasiet Rindas kopsumma.
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: 2. uzdevuma pēdējais vaicājums
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 pievienojumprogrammā 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ļā Uzziniet 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"]}[Dati] |
| Izvērst tabulu Pasūtījumu_dati | Izvērst tabulu Pasūtījumu_dati | = Table.ExpandTableColumn(Pasūtījumi; "Order_Details", {"Produkta_ID", "Vienības_cena", "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", "Krava", "Piegādes_vārds", "Piegādes_adrese", "Piegādes_pilsēta", "Piegādes_reģions", "Piegādes_pasta_indekss", "Piegādes_valsts", "Klients", "Darbinieks", "Sūtītājs"}) |
| Aprēķiniet katras tabulas Pasūtījumu_dati rindas kopsummu | Pievienots pielāgots |
= Table.AddColumn(RemovedColumns, "Pielāgots", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Izvērsta Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) |
| Nosaukuma maiņa uz jēgpilnāku 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 | Izvilktais gads | = Table.TransformColumns(#"Grupētās rindas",{{"Gads", Datums.Gads, Int64.Tips}}) |
| Mainīt uz jēgpilnāki nosaukumi, Pasūtījuma_datums un Gads |
Pārdēvētas 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.Produkta_ID"}, {{"Pārdošanas apjoms", each List.Sum([Line Total]), type number}}) |
3. uzdevums. Produktu un pārdošanas kopsummu vaicājumu apvienošana
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 darbību Izvēršana , un pēc tam Excel datu modelī tiek ielādēts produkta pārdošanas kopsummu vaicājums.
1. darbība. Sapludiniet kolonnu Produkta_ID pārdošanas kopsummu vaicājumā
Excel darbgrāmatā naviģējiet uz vaicājumu Produkti darblapas cilnē Produkti .
Vaicājumā atlasiet šūnu un pēc tam atlasiet Vaicājuma>sapludināšana.
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.
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.
Dialoglodziņā Konfidencialitātes līmeņi:
- Kā konfidencialitātes līmeni abiem datu avotiem atlasiet Organizācijas.
- Atlasiet Saglabāt.
Atlasiet Labi.
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ātos ir visas kolonnas no primārās tabulas (Produkti) un viena strukturētā tabula kolonna līdz saistītajai tabulai (Pārdošanas kopsumma). 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šana
Šajā darbībā jūs izvēršat sapludināto kolonnu ar nosaukumu JaunaKolonna , lai vaicājumā Produkti tiktu izveidotas divas jaunas kolonnas: Gads un Pārdošanas kopsumma.
Datu priekšskatījumā atlasiet ikonu Izvērst (
) blakus JaunaKolonna.Izvēršanas nolaižamajā sarakstā:
- Atlasiet (Atlasīt visas kolonnas), lai notīrītu visas kolonnas.
- Atlasiet gadu un pārdošanas kopsummu.
- Atlasiet Labi.
Pārdēvējiet šīs divas kolonnas par Gads un Pārdošanas kopsummas.
Lai uzzinātu, kuriem produktiem un kuros gados ir bijis visaugstākais pārdošanas apjoms, atlasiet Kārtot dilstošā secībā pēc pārdošanas kopsummas.
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 pārskatu, kas ir saistīts ar vaicājuma rezultātu. Ielādējot datus Excel datu modelī, varat izmantot Power Pivot, lai turpinātu datu analīzi.
- Atlasiet Sākums,>Aizvērt & Ielādēt.
- Dialoglodziņā Datu importēšana noteikti atlasiet Pievienot šos datus datu modelim. Lai iegūtu papildinformāciju par šī dialoglodziņa lietošanu, atlasiet jautājuma zīmi (?).
Rezultāts
Jums ir produkta pārdošanas kopsummu vaicājums, kas apvieno datus no Products.xlsx faila un 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 pievienojumprogrammā Power Query veicat sapludināšanas vaicājumu darbības, vaicājuma soļi tiek izveidoti un uzskaitīti rūts Vaicājumu iestatījumi sarakstā Lietotās darbības . 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ļā Uzziniet 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(Produkti, {"Produkta_ID"}, #"Pārdošanas_apjoms", {"Order_Details.Produkta_ID"}, "Pārdošanas apjoms", JoinKind.LeftOuter) |
| Izvērst sapludinātu kolonnu | Izvērsts pārdošanas kopskaits | = Table.ExpandTableColumn(Source, "Pārdošanas_apjoms", {"Gads", "Pārdošanas_apjoms"}, {"Pārdošana.Gads", "Pārdošana.Pārdošanas_apjoms"}) |
| Divu kolonnu pārdēvēšana | Pārdēvētās kolonnas | = Table.RenameColumns(#"Izvērstais pārdošanas apjoms",{{"Pārdošana.Gads", "Gads"}, {"Pārdošana.Pārdošanas_apjoms", "Pārdošanas_apjoms"}}) |
| Pārdošanas kopsummas kārtošana augošā secībā | Kārtotās rindas | = Table.Sort(#"Pārdēvētās kolonnas",{{"Pārdošanas apjoms", Order.Ascending}}) |