Learn to combine multiple data sources (Power Query) (2024)

In this task, you import data into your Excel workbook from the sample Northwind OData feed at http://services.odata.org/Northwind/Northwind.svc,expand the Order_Details table, remove columns, calculate a line total,transform an OrderDate, group rows by ProductID and Year, rename the query,and disable query download to the Excel workbook.

Step 1: Connect to an OData Feed

1. Select Data > Get Data >From Other Sources > From OData Feed.

2. In the OData Feed dialog box, enter the URL for the Northwind OData feed.

3. Select OK.

4. In the Navigator pane, double click the Orders table.

Step 2: Expand an Order_Details table

In this step, you expand the Order_Details table that is related to the Orders table, to combine the ProductID, UnitPrice, and Quantity columns from Order_Details into the Orders table. The Expand operation combines columns from a related table into a subject table. When the query runs, rows from the related table (Order_Details) are combined into rows with the primary table (Orders).

In Power Query, a column containing a related table has the value RecordorTable in the cell. These are called structured columns. Recordindicatesa single related record and represents aone-to-one relationship with the current data or primary table. Tableindicatesa related table and represents a one-to-many relationship with the current or primary table. A structured column represents a relationship in a data source that has a relational model. For example,a structured column indicates an entity with a foreign key association in an OData feed or foreign key relationship in a SQL Server database.

After you expand the Order_Details table, three new columns and additional rows are added to the Orders table, one for each row in the nested or related table.

1. In Data Preview, scroll horizontally to the Order_Details column.

2. In the Order_Details column, select the expand icon ().

3. In the Expand drop-down:

   See Also

   Connect to a Power Query data source - Dynamics 365 Customer InsightsPower Query Folder connector - Power QueryMerge queries (Power Query) - Microsoft SupportCreate a Data Model in Excel

   1. Select (Select All Columns) to clear all columns.

   2. Select ProductID, UnitPrice, and Quantity.

   3. Select OK.

      

      Note:In Power Query, you can expand tables linked from a column and aggregate the columns of the linked table before expanding the data in the subject table. For more information about how to perform aggregate operations, see Aggregate data from a column.

Step 3: Remove other columns to only display columns of interest

In this step you remove all columns except OrderDate, ProductID, UnitPrice, and Quantity columns.

1. In DataPreview, select the following columns:

   1. Select the first column,OrderID.

   2. Shift+Click the last column,Shipper.

   3. Ctrl+Click the OrderDate, Order_Details.ProductID, Order_Details.UnitPrice, and Order_Details.Quantity columns.

2. Right-click on a selected column header, and select Remove Other Columns.

Step 4: Calculate the line total for each Order_Details row

In this step, you create a Custom Column to calculate the line total for each Order_Details row.

1. In Data Preview, select the table icon () at the top-left corner of the preview.

2. Click Add Custom Column.

3. In the Custom Column dialog box, in the Custom column formula box, enter [Order_Details.UnitPrice] * [Order_Details.Quantity].

4. In the New column name box, enter Line Total.

5. Select OK.

Step 5: Transform an OrderDate year column

In this step, you transform the OrderDate column to render the order date year.

1. In DataPreview, right-click the OrderDate column, and select Transform > Year.

2. Rename the OrderDate column to Year:

   1. Double-Click the OrderDate column, and enter Year or

   2. Right-Click on the OrderDate column, select Rename, and enter Year.

Step 6: Group rows by ProductID and Year

1. In Data Preview, select Year and Order_Details.ProductID.

2. Right-Click one of the headers, and select Group By.

3. In the Group By dialog box:

   1. In the New column name textbox, enter Total Sales.

   2. In the Operation drop-down, select Sum.

   3. In the Column drop-down, select Line Total.

4. Select OK.

   

Step 7: Rename a query

Before you import the sales data into Excel, rename the query:

• In the Query Settings pane, in the Namebox enter Total Sales.

Results: Final query for Task 2

After you perform each step, you will have a Total Sales query over the Northwind OData feed.

Summary: Power Query steps created in Task 2

As you perform query activities in Power Query, query steps are created and listed in the Query Settings pane, in the Applied Stepslist. Each query step has a corresponding Power Query formula, also known as the "M" language. For more information about Power Query formulas, see Learn about Power Query formulas.