Învățați să combinați mai multe surse de date (Power Query)

Î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â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 Date > Preluare > de datedin > fișier din registru de lucru.

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

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

Pasul 2: Examinați pașii de interogare

În mod implicit, Power Query adaugă automat mai mulți pași ca comoditate pentru dvs. Examinați fiecare pas de sub Pași parcurș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 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 au 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 coloane > Eliminare alte coloane.

   

Pasul 4: Încărcați interogarea 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: Power Query pași creați în Activitatea 1

Pe măsură ce efectuați activități de interogare în Power Query, pașii de interogare sunt creați și 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 formulelor 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",Kind="Table"]}[Date]
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 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

1. Selectați Date > Obțineți > de date din alte surse > din fluxul OData.

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 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. Atunci 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. Înregistrarea indică o singură înregistrare asociată și reprezintă orelaț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ă de date SQL Server.

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ă la coloana Order_Details .

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

3. În lista verticală Extindere:

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

   2. Selectați IdProdus, PrețUnitar și Cantitate.

   3. Selectați OK.

      

      Notă: În Power Query, puteți să extindeți tabelele legate dintr-o coloană și să agregați coloanele tabelului legat înainte de a extinde datele 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 Examinaredate, 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 Examinare date, selectați 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.Cantitate].

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

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

1. În Examinare date, faceți clic dreapta pe coloana OrderDate (DatăComandă ), apoi 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 în coloana DatăComandă , selectați Redenumire și introduceți An.

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.

   

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: Interogare finală pentru Activitatea 2

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

Rezumat: Power Query pași creați în Activitatea 2 

Pe măsură ce efectuați activități de interogare în Power Query, pașii de interogare sunt creați și 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";nul, [Implementare="2.0"])
Selectați un tabel	Navigare	= Sursă{[Nume="Comenzi"]}[Date]
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	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 la un nume mai semnificativ, Total Lne	Coloane redenumite	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Transformarea coloanei OrderDate pentru a reda anul	An extras	= Table.TransformColumns(#"Rânduri grupate",{{"Year", Date.Year, Int64.Type}})
Modificați în  mai multe nume 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.

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

1. În registrul de lucru Excel, navigați la interogarea Produse din 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 asociată 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 Salvați.

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.

   

Rezultat

Operațiunea Îmbinare creează o interogare. Rezultatul interogării conține toate coloanele din tabelul principal (Produse) și o singură coloană structurată tabel la 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 NewColumn pentru a crea două coloane noi în interogarea Produse : Year și Total Sales.

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

2. În lista verticală Extindere :

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

   2. Selectați An și Total vânzări.

   3. Selectați OK.

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

4. Pentru a afla ce produse și în ce an produsele au obținut 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

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 conectat la rezultatul interogării. După ce încărcați date în modelul de date Excel, puteți utiliza Power Pivot pentru a 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 întrebării (?).

Rezultat

Aveți o interogare Total vânzări per produs care combină datele din fișierul Products.xlsx și fluxul OData Northwind. Această interogare este aplicată la un model Power Pivot. În plus, modificările interogării modifică și reîmprospătează tabelul rezultat în modelul de date.

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

Pe măsură ce efectuați activități de interogare Îmbinare în Power Query, pașii de interogare sunt creați și 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 extins	= 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"}})
Sortarea totalului vânzărilor în ordine ascendentă	Rânduri sortate	= Table.Sort(#"Coloane redenumite",{{"Total vânzări", Order.Ascending}})