In acest tutorial, puteți utiliza Editor Power Query din Power Query pentru a importa date dintr-un fișier Excel local care conține informații despre produs și dintr-un flux OData, care conține informații despre comenzile pentru produse. Efectuați pașii de transformare și agregare și combinați date din ambele surse pentru a produce un raport "Total vânzări per produs și an".
Pentru a efectua acest tutorial, aveți nevoie de registrul de lucru Produse . În caseta de dialog Salvare ca, denumiți fișierul Produse și comenzi.xlsx.
Activitatea 1: Importul de produse într-un registru de lucru Excel
În această activitate, importați produse din fișierul Produse și Orders.xlsx (descărcat și redenumit mai sus) într-un registru de lucru Excel, promovați rânduri ca anteturi de coloană, eliminați unele coloane și încărcați interogarea într-o foaie de lucru.
Pasul 1: Conectarea la un registru de lucru Excel
- Creați un registru de lucru Excel.
- Selectați Date>Preluați date>din fișier>din registrul de lucru.
- În caseta de dialog Import date , răsfoiți la fișierul Products.xlsx descărcat și găsiți-l, apoi selectați Deschidere.
- În panoul Navigator , faceți dublu clic pe tabelul Produse . Apare Editor Power Query.
Pasul 2: Examinați pașii de interogare
În mod implicit, Power Query adaugă automat câțiva pași pentru a vă ajuta. Examinați fiecare pas de sub Pașii parcurși din panoul Setări interogare pentru a afla mai multe.
- Faceți clic dreapta pe pasul Sursă și selectați Editare setări. Acest pas a fost creat atunci când ați importat registrul de lucru.
- Faceți clic dreapta pe pasul de navigare și selectați Editare setări. Acest pas a fost creat atunci când ați selectat tabelul din caseta de dialog Navigare .
- Faceți clic dreapta pe pasul Tip modificat , apoi selectați Editare setări. Acest pas a fost creat de Power Query, care a dedus tipurile de date ale fiecărei coloane. Selectați săgeata în jos din partea dreaptă a barei de formule pentru a vedea formula completă.
Pasul 3: Eliminarea altor coloane pentru a afișa numai coloanele de interes
În acest pas veți elimina toate coloanele, cu excepția ProductID, ProductName, CategoryID și QuantityPerUnit.
- În Previzualizare date, selectați coloanele IDProdus, NumeProdus, IDCategorie și CantitatePerUnit (utilizați Ctrl+Clic sau Shift+Clic).
- Selectați Eliminare coloane>Eliminare alte coloane.
Pasul 4: Încărcarea interogării de produse
În acest pas, încărcați interogarea Produse într-o foaie de lucru Excel.
- Selectați Pornire>,Închidere & Încărcare. Interogarea apare într-o nouă foaie de lucru Excel.
Rezumat: Pașii Power Query creați în Activitatea 1
Când efectuați activități de interogare în Power Query, sunt creați pași de interogare și aceștia sunt listați în panoul Setări interogare, în lista Pași parcurși. Fiecare pas de interogare are o formulă Power Query corespunzătoare, numită și limbaj „M”. Pentru mai multe informații despre formulele Power Query, consultați Crearea de formule Power Query în Excel.
| Activitate | Pas interogare | Formulă |
|---|---|---|
| Importul unui registru de lucru Excel | Sursă | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true) |
| Selectați tabelul Produse | Navigare | = Sursă{[Element="Produse",Tip="Tabel"]}[Date] |
| Power Query detectează automat tipurile de date coloană | Tip modificat | = 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}}) |
| Eliminarea altor coloane pentru a afișa numai coloanele de interes | S-au eliminat alte coloane | = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
Activitatea 2: Importul de date despre comenzi dintr-un flux OData
În această activitate, importați date în registrul de lucru Excel din fluxul OData Northwind eșantion la http://services.odata.org/Northwind/Northwind.svc, extindeți tabelul Order_Details, eliminați coloane, calculați un total de linie, transformați o DatăComandă, grupați rândurile după ProductID și Year, redenumiți interogarea și dezactivați descărcarea interogării în registrul de lucru Excel.
Pasul 1: Conectarea la un flux OData
- Selectați date>Preluați date>din alte surse>din fluxul OData.
- În caseta de dialog Flux OData, introduceți Adresă URL pentru fluxul OData Northwind.
- Selectați OK.
- În panoul Navigator , faceți dublu clic pe tabelul Comenzi .
Pasul 2: Extinderea unui tabel Order_Details
În acest pas, extindeți tabelul Order_Details care este legat de tabelul Orders, pentru a combina coloanele ProductID, UnitPrice și Quantity din Order_Details în tabelul Orders. Operațiunea Extindere combină coloane dintr-un tabel asociat într-un tabel subiect. Când rulează interogarea, rândurile din tabelul asociat (Order_Details) sunt combinate în rânduri cu tabelul principal (Comenzi).
În Power Query, o coloană care conține un tabel asociat are valoarea Înregistrare sau Tabel în celulă. Acestea se numesc coloane structurate. Înregistrare indică o singură înregistrare corelată și reprezintă o relație unu-la-unu cu datele curente sau cu tabelul primar. Tabel indică un tabel asociat și reprezintă o relație unu-la-mai-mulți cu tabelul curent sau principal. O coloană structurată reprezintă o relație dintr-o sursă de date care are un model relațional. De exemplu, o coloană structurată indică o entitate cu o asociere de cheie străină într-un flux OData sau o relație de cheie străină într-o bază de date SQL Server.
După ce extindeți tabelul Order_Details , trei noi coloane și rânduri suplimentare sunt adăugate la tabelul Comenzi , câte una pentru fiecare rând din tabelul imbricat sau asociat.
În Examinare date, defilați pe orizontală la coloana Order_Details .
În coloana Order_Details , selectați pictograma de extindere (
).În lista verticală Extindere:
Selectați (Selectare totală coloane) pentru a goli toate coloanele.
Selectați IDProdus, PrețUnitar și Cantitate.
Selectați OK.
Notă
În Power Query, puteți să extindeți tabelele legate dintr-o coloană și să agregați coloanele din tabelul legat înainte de extinderea datelor din tabelul subiect. Pentru mai multe informații despre cum se efectuează operațiunile de agregare, consultați Agregarea datelor dintr-o coloană.
Pasul 3: Eliminarea altor coloane pentru a afișa numai coloanele de interes
În acest pas veți elimina toate coloanele, cu excepția coloanelor OrderDate, ProductID, UnitPrice și Quantity.
În Examinare date, selectați următoarele coloane:
- Selectați prima coloană, ID comandă.
- Shift+faceți clic pe ultima coloană, Expeditor.
- Ctrl+clic pe coloanele OrderDate, Order_Details.ProductID, Order_Details.UnitPrice și Order_Details.Quantity.
Faceți clic dreapta pe un antet de coloană selectată și selectați Eliminare alte coloane.
Pasul 4: Calcularea totalului de linie pentru fiecare rând Order_Details
În acest pas, creați o Coloană particularizată pentru a calcula totalul de linie pentru fiecare rând Order_Details.
- În Previzualizare date, selectați pictograma tabel (
) din colțul din stânga sus al previzualizării. - Faceți clic pe Adăugare coloană particularizată.
- În caseta de dialog Coloană particularizată, în caseta de formule Coloană particularizată, introduceți [Order_Details.PrețUnitar] * [Order_Details.Cantitate].
- În caseta Nume coloană nouă , introduceți Total linie.
- Selectați OK.
Pasul 5: Transformarea unei coloane de an OrderDate
În acest pas, veți transforma coloana OrderDate pentru a reda anul din data comenzii.
În Examinare date, faceți clic dreapta pe coloana OrderDate și selectați Transform>Year.
Redenumiți coloana OrderDate la Year:
- Faceți dublu clic pe coloana OrderDate și introduceți An sau
- Right-Click în coloana DatăComandă , selectați Redenumire și introduceți An.
Pasul 6: Gruparea rândurilor după ProductID și Year
În Previzualizare date, selectați Year și Order_Details.ProductID.
Right-Click unul dintre anteturi și selectați Grupare după.
În caseta de dialog Grupare după:
- În caseta text Nume nou de coloană, introduceți Total vânzări.
- În lista verticală Operațiune, selectați Sumă.
- În lista verticală Coloană, selectați Total linie.
Selectați OK.
Pasul 7: Redenumirea unei interogări
Înainte de a importa datele de vânzări în Excel, redenumiți interogarea:
- În panoul Setări interogare , în caseta Nume , introduceți Total vânzări.
Rezultate: Interogarea finală pentru Activitatea 2
După ce efectuați fiecare pas, veți avea o interogare Total vânzări prin fluxul OData Northwind.
Rezumat: Pașii Power Query creați în Activitatea 2
Când efectuați activități de interogare în Power Query, sunt creați pași de interogare și aceștia sunt listați în panoul Setări interogare, în lista Pași parcurși. Fiecare pas de interogare are o formulă Power Query corespunzătoare, numită și limbaj „M”. Pentru mai multe informații despre formulele Power Query, consultați Aflați despre formulele Power Query.
| Activitate | Pas interogare | Formulă |
|---|---|---|
| Conectarea la un flux OData | Sursă | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"]) |
| Selectați un tabel | Navigare | = Source{[Name="Comenzi"]}[Data] |
| Extinderea tabelului Order_Details | Extindere Order_Details | = Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"}) |
| Eliminarea altor coloane pentru a afișa numai coloanele de interes | RemovedColumns | = Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"}) |
| Calcularea totalului de linie pentru fiecare rând Order_Details | S-a adăugat particularizat |
= 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]) |
| Schimbarea într-un nume mai semnificativ, Total Lne | Coloane redenumite | = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}}) |
| Transformarea coloanei OrderDate pentru a reda anul | Anul extras | = Table.TransformColumns(#"Rânduri grupate",{{"Year", Date.Year, Int64.Type}}) |
| Modificați în nume mai semnificative, OrderDate și Year |
Coloane redenumite 1 |
Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}}) |
| Gruparea rândurilor după ProductID și Year | GroupedRows | = Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}}) |
Activitatea 3: Combinarea interogărilor Produse și Total vânzări
Power Query vă permite să combinați mai multe interogări, prin îmbinarea sau adăugarea lor. Operațiunea de Îmbinare se efectuează pe orice interogare Power Query cu o formă tabelară, independent de sursa din care provin datele. Pentru mai multe informații despre combinarea surselor de date, consultați Combinarea mai multor interogări.
În această activitate, combinați interogările Produse și Total vânzări utilizând o interogare Îmbinare și o operațiune de extindere , apoi încărcați interogarea Total vânzări per produs în Modelul de date Excel.
Pasul 1: Îmbinarea ProductID într-o interogare Total vânzări
În registrul de lucru Excel, navigați la interogarea Produse din fila Foaie de lucru Produse .
Selectați o celulă din interogare, apoi selectațiÎmbinareinterogare>.
În caseta de dialog Îmbinare , selectați Produse ca tabel principal și selectați Total vânzări ca interogare secundară sau asociată de îmbinat. Total vânzări va deveni o nouă coloană structurată cu o pictogramă de extindere.
Pentru a potrivi Total vânzări cu Produse după ProductID, selectați coloana ProductID din tabelul Produse și coloana Order_Details.ProductID din tabelul Total vânzări.
În caseta de dialog Niveluri de confidențialitate:
- Selectați Organizațional pentru nivelul de izolare de confidențialitate pentru ambele surse de date.
- Selectați Salvați.
Selectați OK.
Notă
Nivelurile de confidențialitate împiedică un utilizator să combine neintenționat date din mai multe surse de date, care ar putea fi private sau organizaționale. În funcție de interogare, un utilizator ar putea trimite accidental date din sursa de date private la o altă sursă de date care ar putea fi rău intenționată. Power Query analizează fiecare sursă de date și o clasifică în nivelul definit de confidențialitate: Public, Organizațional și Privat. Pentru mai multe informații despre nivelurile de confidențialitate, consultați Setarea nivelurilor de confidențialitate.
Rezultat
Operațiunea Îmbinare creează o interogare. Rezultatul interogării conține toate coloanele din tabelul primar (Produse) și o singură coloană structurată de tabel în tabelul asociat (Total vânzări). Selectați pictograma Extindere pentru a adăuga coloane noi la tabelul principal din tabelul secundar sau asociat.
Pasul 2: Extinderea unei coloane îmbinate
În acest pas, extindeți coloana îmbinată cu numele ColoanăNouă pentru a crea două coloane noi în interogarea Produse : An și Total vânzări.
În Previzualizare date, selectați pictograma Extindere (
) lângă ColoanăNouă.În lista verticală Extindere :
- Selectați (Selectare totală coloane) pentru a goli toate coloanele.
- Selectați Year și Total Sales.
- Selectați OK.
Redenumiți aceste două coloane în Year și Total Sales.
Pentru a afla ce produse și în care ani au obținut cel mai mare volum de vânzări, selectați Sortare descendentă după Vânzări totale.
Redenumiți interogarea în Total vânzări per produs.
Rezultat
Pasul 3: Încărcarea unei interogări Total vânzări per produs într-un Model de date Excel
În acest pas, încărcați o interogare într-un Model de date Excel pentru a construi un raport legat de rezultatul interogării. După ce încărcați datele în modelul de date Excel, puteți utiliza Power Pivot pentru a continua analiza datelor.
- Selectați Pornire>,Închidere & Încărcare.
- În caseta de dialog Import date , asigurați-vă că selectați Adăugați aceste date la modelul de date. Pentru mai multe informații despre utilizarea acestei casete de dialog, selectați semnul de întrebare (?).
Rezultat
Aveți o interogare Total vânzări per produs care combină date din fișierul Products.xlsx și fluxul OData Northwind. Această interogare se aplică la un model Power Pivot. În plus, modificările aduse interogării modifică și reîmprospătează tabelul care rezultă în modelul de date.
Rezumat: Pașii Power Query creați în Activitatea 3
Când efectuați activități de interogare de îmbinare în Power Query, sunt creați pași de interogare și aceștia sunt listați în panoul Setări interogare, în lista Pași parcurși. Fiecare pas de interogare are o formulă Power Query corespunzătoare, numită și limbaj „M”. Pentru mai multe informații despre formulele Power Query, consultați Aflați despre formulele Power Query.
| Activitate | Pas interogare | Formulă |
|---|---|---|
| Îmbinarea ProductID în interogarea Total vânzări | Sursa (sursă de date pentru operațiunea Îmbinare) | = Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter) |
| Extinderea unei coloane de îmbinare | Total vânzări extinse | = Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"}) |
| Redenumirea a două coloane | Coloane redenumite | = Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}}) |
| Sortați total Vânzări în ordine crescătoare | Rânduri sortate | = Table.Sort(#"Coloane redenumite",{{"Total Sales", Order.Ascending}}) |