Combinarea de date din mai multe surse de date (Power Query)

Important : Acest articol este tradus automat, consultați exonerarea de răspundere. Versiunea în limba engleză a acestui articol se poate găsi aici pentru referință.

Notă : Power Query este cunoscut sub numele de Obțineți și transformați în Excel 2016. Informațiile furnizate aici se aplică pentru ambele. Pentru mai multe informații, consultați Obținere și transformare în Excel 2016.

In acest tutorial, veți utiliza editorul de interogare Power Query pentru a importa date dintr-un fișier Excel local care conține informații despre produs, dintr-un OData flux care conține informații despre ordinea de produs. Când efectuați pași transformare și agregare și combinați date din ambele surse pentru a genera un raport Total vânzări per produs și anul .

Pentru a efectua acest tutorial, aveți nevoie de registrul de lucru Produse și comenzi. În caseta de dialog Salvare ca, denumiți fișierul Produse și comenzi.xlsx.

În acest tutorial

Activitatea 1: Importul de produse într-un registru de lucru Excel

Pasul 1: Conectarea la un registru de lucru Excel

Pasul 2: Promovarea primului rând ca antet de coloană de tabel

Pasul 3: Eliminarea altor coloane pentru a afișa numai coloanele de interes

Pașii Power Query creați

Pasul 4: Importul unei interogări de produse

Activitatea 2: Importul de date despre comenzi dintr-un flux OData

Pasul 1: Conectarea la un flux OData

Pasul 2: Extinderea unui tabel Order_Details

Extinderea linkului de tabel Order_Details

Pasul 3: Eliminarea altor coloane pentru a afișa numai coloanele de interes

Eliminarea coloanelor selectate

Pasul 4: Calcularea totalului de linie pentru fiecare rând Order_Details

Calcularea totalului de linie pentru fiecare rând Order_Details

Pasul 5: Transformarea unei coloane de an OrderDate

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

Pasul 7: Redenumirea unei interogări

Rezultatele finale ale interogării

Pașii Power Query creați

Pasul 8: Dezactivarea descărcării interogărilor într-un registru de lucru Excel

Dezactivarea descărcării unei interogări

Activitatea 3: Combinarea interogărilor Produse și Total vânzări

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

Pasul 2: Extinderea unei coloane de îmbinare

Extinderea linkului de tabel NewColumn

Pașii Power Query creați

Pasul 3: Încărcarea unei interogări Total vânzări per produs într-un Model de date Excel

Încărcarea interogării Total vânzări per produs în Modelul de date Excel

Interogarea finală Total vânzări per produs

Activitatea 1: Importul de produse într-un registru de lucru Excel

În această activitate, importați produse din fișierul Produse și comenzi.xlsx într-un registru de lucru Excel.

Pasul 1: Conectarea la un registru de lucru Excel

  1. Creați un registru de lucru Excel.

  2. În fila din panglică POWER QUERY, faceți clic pe Din fișier > Din Excel.

  3. În caseta de dialog Răsfoire Excel, răsfoiți la calea pentru Produse și comenzi.xlsx sau tastați-o pentru a importa sau a crea un link spre un fișier.

  4. În panoul Navigator, faceți dublu clic pe foaia de lucru Products sau faceți clic pe Products și pe Editare interogare. Când editați o interogare sau vă conectați la o sursă de date nouă, apare fereastra Editor interogare.

    Notă : Pentru un videoclip foarte rapid despre afișarea Editorului interogare, consultați finalul acestui articol.

Pasul 2: Promovarea primului rând ca antet de coloană de tabel

În panoul Previzualizare interogare, primul rând al tabelului nu conține numele coloanelor din tabel. Pentru a promova primul rând ca antet de coloană de tabel:

  1. Faceți clic pe pictograma tabel ( Pictograma tabel ) din colțul din stânga sus al previzualizării de date.

  2. Faceți clic pe Utilizare primul rând ca anteturi.

Promovarea primului rând ca antet de coloană de tabel

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 panoul Previzualizare interogare, selectați coloanele ProductID, ProductName, CategoryID și QuantityPerUnit (folosind Ctrl + clic sau Shift + clic).

  2. În panglica Editor interogare, faceți clic pe Eliminare coloane > Eliminare alte coloane sau faceți clic dreapta pe un antet de coloană și faceți clic pe Eliminare alte coloane.

    Ascunderea altor coloane

Pașii Power Query creați

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 limbajul pentru formule Power Query, consultați Aflați despre formulele Power Query.

Activitate

Pas interogare

Formulă

Conectarea la un registru de lucru Excel

Sursă

Source{[Name="Products"]}[Data]

Promovarea primului rând ca antet de coloană de tabel

FirstRowAsHeader

Table.PromoteHeaders

(Products)

Eliminarea altor coloane pentru a afișa numai coloanele de interes

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader, {"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Pasul 4: Importul unei interogări de produse

În acest pas, importați interogarea Produse în registrul de lucru Excel.

  1. În panglica Editor interogare, faceți clic pe Aplicare și închidere. Rezultatele apar într-o nouă foaie de lucru Excel.

Începutul paginii

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 de la http://services.odata.org/Northwind/Northwind.svc.

Pasul 1: Conectarea la un flux OData

  1. În fila din panglică POWER QUERY, faceți clic pe Din alte surse > Din flux OData.

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

  3. Faceți clic pe OK.

  4. În panoul Navigator, faceți dublu clic pe tabelul Orders sau faceți clic pe Orders și pe Editare.

Notă :  Atunci când treceți cu mouse-ul pe deasupra unui tabel, veți vedea o previzualizare flotantă a tabelului.

Treceți cu mouse-ul pe deasupra unei surse de date

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 din tabelul subiect (Orders).

În Power Query, o coloană care conține un link către un tabel asociat are un link Entry sau un link Table. Un link Entry navighează la o singură înregistrare asociată și reprezintă o relație unu-la-unu cu un tabel subiect. Un link Table navighează la un tabel asociat și reprezintă o relație unu-la-mai mulți cu un tabel subiect. Un link reprezintă proprietățile de navigare dintr-o sursă de date într-un model relațional. Pentru un flux OData, proprietățile de navigare reprezintă o entitate cu o asociere de cheie externă. Într-o bază de date, precum SQL Server, proprietățile de navigare reprezintă relații de cheie externă din baza de date.

Extinderea linkului de tabel Order_Details

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 panoul Previzualizare interogare, defilați la coloana Order_Details.

  2. În coloana Order_Details, faceți clic pe pictograma de extindere ( Extindere ).

  3. În lista verticală Extindere:

    1. Faceți clic pe Selectare totală coloane pentru a goli toate coloanele.

    2. Faceți clic pe ProductID, UnitPrice și Quantity.

    3. Faceți clic pe OK.

      Extinderea linkului de tabel Order_Details

      Notă :  În Power Query, puteți extinde tabele la care se face legătură dintr-o coloană și, de asemenea, puteți efectua operațiuni de agregare pe 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 activitatea anterioară, ați folosit Eliminare alte coloane. Pentru această activitate, eliminați coloanele selectate.

Eliminarea coloanelor selectate

  1. În panoul Previzualizare interogare, selectați toate coloanele:

    1. Faceți clic pe prima coloană (OrderID).

    2. Shift+clic pe ultima coloană (Shipper).

    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 faceți clic pe Eliminare 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.

Calcularea totalului de linie pentru fiecare rând Order_Details

  1. În panoul Previzualizare interogare, faceți clic pe pictograma tabel ( Pictograma tabel ) din colțul din stânga sus al previzualizării.

  2. Faceți clic pe Inserare coloană > Particularizată.

  3. În caseta de dialog Inserare coloană particularizată, în caseta de text Formulă coloană particularizată, introduceți [Order_Details.UnitPrice] * [Order_Details.Quantity].

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

  5. Faceți clic pe 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 panoul Examinare, faceți clic dreapta pe coloana OrderDate și faceți clic pe Transformare > Year.

  2. Redenumiți coloana OrderDate la Year:

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

    2. faceți clic dreapta pe coloana OrderDate, faceți clic pe Redenumire și introduceți Year.

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

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

  2. Faceți clic dreapta pe unul dintre anteturi și faceți clic pe 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. Faceți clic pe 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 în Excel, denumiți interogarea Total vânzări:

  1. În panoul Setări interogare, în caseta text Nume, introduceți Total vânzări.

Rezultatele finale ale interogării

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

Total vânzări

Pașii Power Query creați

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 limbajul pentru formule Power Query, consultați Aflați despre formulele Power Query.

Activitate

Pas interogare

Formulă

Conectarea la un flux OData

Sursă

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

InsertedColumns

Table.AddColumn

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

Transformarea coloanei OrderDate pentru a reda anul

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Custom", "Line Total"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}})

RenamedColumns1

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

Pasul 8: Dezactivarea descărcării interogărilor într-un registru de lucru Excel

Deoarece interogarea Total vânzări nu reprezintă raportul final Total vânzări per produs și an, dezactivați descărcarea interogării în registrul de lucru Excel. Atunci când opțiunea Încărcare în foaia de lucru este Dezactivată în panoul Setări interogări, rezultatul de date al acestei interogări nu este descărcat, dar interogarea încă poate fi combinată cu alte interogări pentru a construi rezultatul dorit. Aflați cum să combinați această interogare cu interogarea Produse în următoarea activitate.

Dezactivarea descărcării unei interogări

  1. În panoul Setări interogare, debifați Încărcare în foaia de lucru.

  2. În panglica Editor interogare, faceți clic pe Aplicare și închidere. În panoul Interogări registru de lucru, interogarea Total vânzări afișează Încărcarea este dezactivată.

    Dezactivarea descărcării unei interogări

Începutul paginii

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 un pas de interogare Îmbinare și Extindere.

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

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

  2. În fila de panglică INTEROGARE, faceți clic pe Îmbinare.

  3. Î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 coloană nouă extensibilă.

  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. Faceți clic pe Salvare.

  6. Faceți clic pe 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 Niveluri de confidențialitate.

    Caseta de dialog Îmbinare

După ce faceți clic pe OK, operațiunea Îmbinare creează o interogare. Rezultatul interogării conține toate coloanele din tabelul primar (Products) și o singură coloană care conține un link de navigare către tabelul asociat (Total vânzări). O operațiune Extindere adaugă coloane noi în tabelul principal sau subiect din tabelul asociat.

Îmbinare finală

Pasul 2: Extinderea unei coloane de îmbinare

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

Extinderea linkului de tabel NewColumn

  1. În panoul Previzualizare interogare, faceți clic pe pictograma de extindere NewColumn ( Extindere ).

  2. În lista verticală Extindere:

    1. Faceți clic pe Selectare totală coloane pentru a goli toate coloanele.

    2. Faceți clic pe Year și Total Sales.

    3. Faceți clic pe OK.

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

  4. Sortați descrescător după Total Sales pentru a afla ce produse și în care ani au obținut cel mai mare volum de vânzări.

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

Extindere link de tabel

Pașii Power Query creați

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 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 limbajul pentru formule 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"},"NewColumn")

Extinderea unei coloane de îmbinare

ExpandNewColumn

Table.ExpandTableColumn

(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})

RenamedColumns

Table.RenameColumns

(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})

SortedRows

Table.Sort

(RenamedColumns,{{"Total Sales", Order.Descending}})

Pasul 3: Încărcarea unei interogări Total vânzări per produs într-un Model de date Excel

În acest pas, dezactivați opțiunea Încărcare în foaia de lucru și încărcați o interogare în Modelul de date Excel, pentru a construi un raport legat de rezultatul interogării. Pe lângă încărcarea rezultatelor interogării într-o foaie de lucru Excel, Power Query vă permite să încărcați un rezultat de interogare într-un Model de date Excel. După ce încărcați datele în modelul de date Excel, puteți utiliza Power Pivot și Power View pentru analize de date suplimentare.

Încărcarea interogării Total vânzări per produs în Modelul de date Excel

  1. În panoul Setări interogare, debifați Încărcare în foaia de lucru și bifați Încărcare în modelul de date.

  2. Pentru a încărca interogarea în Modelul de date Excel, faceți clic pe Aplicare și închidere.

Încărcarea modelului de date Excel

Interogarea finală Total vânzări per produs

După ce efectuați fiecare pas, veți avea o interogare Total vânzări per produs care combină date din fișierul Products and Orders.xlsx și fluxul OData Northwind. Această interogare poate fi aplicată la un model Power Pivot. În plus, modificările aduse interogării în Power Query modifică și reîmprospătează tabelul care rezultă în modelul Power Pivot.

Începutul paginii

Notă : Editor interogare apare doar atunci când încărcați, editați sau creați o nouă interogare folosind Power Query. Următorul videoclip arată fereastra Editor interogare care apare după ce editați o interogare dintr-un registru de lucru Excel. Pentru a vizualiza Editor interogare fără a încărca sau a edita o interogare de registru de lucru existentă, din secțiunea Preluare date externe din fila Power Query de pe panglică, selectați Din alte surse > Interogare necompletată. Acest videoclip arată o modalitate de a afișa Editor interogare.

Cum se vede Editor interogare în Excel

Notă : Exonerare de răspundere pentru traducere automată: Acest articol a fost tradus de un sistem computerizat, fără intervenție umană. Microsoft oferă aceste traduceri automate pentru a ajuta utilizatorii vorbitori de alte limbi decât engleza să beneficieze de conținutul despre produsele, serviciile și tehnologiile Microsoft. Pentru că articolul a fost tradus de un computer, poate conține erori de vocabular, sintaxă sau gramatică.

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

Au fost utile aceste informații?

Vă mulțumim pentru feedback!

Vă mulțumim pentru feedback! Se pare că ar fi util să luați legătura cu unul dintre agenții noștri de asistență Office.

×