Conectați-vă cu Microsoft
Conectați-vă sau creați un cont.
Salut,
Selectați un alt cont.
Aveți mai multe conturi
Alegeți contul cu care doriți să vă conectați.

In acest tutorial, puteți utiliza Editorul interogare 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 comanda de produs. Efectuați pașii de transformare și agregare și combinați date din ambele surse pentru a produce un raport "Vânzări totale 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.

În această activitate, importați produse din fișierul Produse și Orders.xlsx (descărcate și redenumite mai sus) într-un registru de lucru Excel, promovați rândurile 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

  1. Creați un registru de lucru Excel.

  2. Selectați Data> Get Data >(Obțineți date din fișierul sau din > din registrul de lucru).

  3. În caseta de dialog Import date, răsfoiți și găsiți fișierul Products.xlsx-ați descărcat, apoi selectați Deschidere.

  4. În panoul Navigator, faceți dublu clic pe tabelul Products. Apare Editorul Power Query.

Pasul 2: Examinați pașii de interogare

În mod implicit, Power Query adaugă automat mai mulți pași ca o comoditate pentru dvs. Examinați fiecare pas de sub Pași aplicați,din panoul Setări interogare pentru a afla mai multe.

  1. 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.

  2. Faceți clic dreapta pe pasul Navigare și selectați Editare Setări. Acest pas a fost creat atunci când ați selectat tabelul din caseta de dialog Navigare.

  3. Faceți clic dreapta pe pasul Tip modificat și 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.

  1. În Previzualizare date, selectați coloanele ProductID,ProductName,CategoryIDși QuantityPerUnit (utilizați Ctrl+clic sau Shift+clic).

  2. Selectați Eliminare > Eliminare alte coloane.

    Ascunderea altor coloane

Pasul 4: Încărcarea interogării de produse

În acest pas, încărcați interogarea Produse într-o foaie Excel lucru.

  • Selectați Pornire > Închidere & Încărcare. Interogarea apare într-o nouă foaie Excel lucru.

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 Interogare Setări,în lista Pași aplicaț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 Excel lucru

Sursă

= Excel. Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)

Selectați tabelul Produse

Navigare

= Source{[Item="Products",Kind="Table"]}[Data]

Power Query detectează automat tipurile de date de 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"})

În această activitate, importați date în registrul de lucru Excel din fluxul OData Northwind eșantion de 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: Conectare la un flux OData

  1. Selectați Data > Get Data > Data > Other Sources > From OData Feed.

  2. În caseta de dialog Flux OData, introduceți Adresă URL pentru fluxul OData Northwind.

  3. Selectați OK.

  4. În panoul Navigator, faceți dublu clic pe tabelul Orders (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 primar(Comenzi).

În Power Query, o coloană care conține un tabel asociat are valoarea Înregistraresau 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 principal. 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 chei străine într-un flux OData sau o relație de cheie străină într-o bază SQL Server date.

După ce extindeți tabelul Order_Details, trei noi coloane și rânduri suplimentare sunt adăugate la tabelul Orders, câte una pentru fiecare rând din tabelul imbricat sau asociat.

  1. În Examinare date, defilați pe orizontală Order_Details coloană.

  2. În coloana Order_Details, selectați pictograma de extindere (Extindere).

  3. În lista verticală Extindere:

    1. Selectați (Selectare toate coloanele) pentru a goli toate coloanele.

    2. Selectați ProductID, UnitPriceși Quantity.

    3. Selectați OK.

      Extinderea linkului de tabel Order_Details

      Notă: În Power Query, puteți să extindeți tabele la care se face legătură dintr-o coloană și să agregț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

  1. În Previzualizare date,selectați următoarele coloane:

    1. Selectați prima coloană, ORDERID.

    2. Shift+clic pe ultima coloană, Expeditor.

    3. Ctrl+clic pe coloanele OrderDate, Order_Details.ProductID, Order_Details.UnitPrice și Order_Details.Quantity.

  2. 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.

  1. În Previzualizare date, selectați pictograma tabel (Pictograma tabel) din colțul din stânga sus al previzualizării.

  2. Faceți clic pe Adăugare coloană particularizată.

  3. În caseta de dialog Coloană particularizată, în caseta Formulă coloană particularizată, introduceți [Order_Details.PrețUnitar] * [Order_Details.Quantity].

  4. În caseta Nume nou de coloană, introduceți Total linie.

  5. Selectați OK.

Calcularea totalului de linie pentru fiecare rând Order_Details

Pasul 5: Transformarea unei coloane de an OrderDate

În acest pas, veți transforma coloana OrderDate pentru a reda anul din data comenzii.

  1. În Previzualizare date,faceți clic dreapta pe coloana OrderDate și selectați Transformare > An.

  2. Redenumiți coloana OrderDate la Year:

    1. Faceți dublu clic pe coloana OrderDate și introduceți An sau

    2. Right-Click coloana OrderDate, selectați Redenumireși introduceți Year.

Pasul 6: Gruparea rândurilor după ProductID și Year

  1. În Previzualizare date,selectați Year și Order_Details.ProductID.

  2. Right-Click unul dintre anteturi și selectați Grupare după.

  3. În caseta de dialog Grupare după:

    1. În caseta text Nume nou de coloană, introduceți Total vânzări.

    2. În lista verticală Operațiune, selectați Sumă.

    3. În lista verticală Coloană, selectați Total linie.

  4. Selectați OK.

    Caseta de dialog Grupare după pentru operațiunile de agregare

Pasul 7: Redenumirea unei interogări

Înainte de a importa datele de vânzări Excel, redenumiți interogarea:

  • În panoul Interogare Setări, în caseta Nume introduceți Total vânzări.

Rezultate: Interogare finală pentru Activitatea 2

După ce efectuați fiecare pas, veți avea o interogare Total vânzări prin fluxul OData Northwind.

Total vânzări

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 Interogare Setări,în lista Pași aplicaț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;nul; [Implementare="2,0"])

Selectați un tabel

Navigare

= Source{[Name="Orders"]}[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 particularizatul

= Table.AddColumn(RemovedColumns, "Custom", fiecare [Order_Details.UnitPrice] * [Order_Details.Quantity])

= Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

Modificarea î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}})

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 operațiunea Extindere, apoi încărcați interogarea Total vânzări per produs în modelul de date Excel date.

Pasul 1: Îmbinarea ProductID într-o interogare Total vânzări

  1. În registrul Excel lucru, navigați la interogarea Produse de pe fila foaie de lucru Produse.

  2. Selectați o celulă din interogare, apoi selectați Interogare > Îmbinare.

  3. În caseta de dialog Îmbinare, selectați Produse ca tabel principal și selectați Total vânzări ca interogare secundară sau corelată de îmbinat. Total vânzări va deveni o nouă coloană structurată cu o pictogramă de extindere.

  4. 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.

  5. În caseta de dialog Niveluri de confidențialitate:

    1. Selectați Organizațional pentru nivelul de izolare de confidențialitate pentru ambele surse de date.

    2. Selectați Salvare.

  6. Selectați OK.

    Notă de securitate:  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.

    Caseta de dialog Îmbinare

Rezultat

Operațiunea Îmbinare creează o interogare. Rezultatul interogării conține toate coloanele din tabelul primar (Produse) și o singură coloană tabel structurată î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.

Îmbinare finală

Pasul 2: Extinderea unei coloane îmbinate

În acest pas, extindeți coloana îmbinată cu numele NewColumn pentru a crea două coloane noi în interogarea Products:Year și Total Sales.

  1. În Examinare date, selectați Extindere pictogramă (Extindere) lângă NewColumn.

  2. În lista verticală Extindere:

    1. Selectați (Selectare toate coloanele) pentru a goli toate coloanele.

    2. Selectați Year și Total Sales.

    3. Selectați OK.

  3. Redenumiți aceste două coloane în Year și Total Sales.

  4. Pentru a afla ce produse și în ce ani au primit cel mai mare volum de vânzări, selectați Sortare descendentă după Total vânzări.

  5. Redenumiți interogarea în Total vânzări per produs.

Rezultat

Extindere link de tabel

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-Excel modelde date nou, pentru a construi un raport conectat la rezultatul interogării. După ce încărcați datele în modelul Excel date,puteți utiliza Power Pivot pentru a vă continua analiza datelor.

  1. Selectați Pornire > Închidere & Încărcare.

  2. Î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 este aplicată la un model Power Pivot. În plus, modificările aduse interogării modifică și reîmprospătează tabelul rezultat în modelul de date.

Rezumat: Pașii Power Query creați în Activitatea 3

Când efectuați activități de interogare Îmbinare în Power Query, sunt creați pași de interogare și aceștia sunt listați în panoul Interogare Setări,în lista Pași aplicaț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

Expanded Total Sales

= 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 totalul vânzărilor în ordine ascendentă

Rânduri sortate

= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})

Consultați și

Ajutor Power Query Excel

Aveți nevoie de ajutor suplimentar?

Extindeți-vă competențele
Explorați instruirea
Fiți primul care obține noile caracteristici
Alăturați-vă utilizatorilor Insider Microsoft Office

Au fost utile aceste informații?

Cât de mulțumit sunteți de calitatea limbajului?
Ce v-a afectat experiența?

Vă mulțumim pentru feedback!

×