Connect to Multiple Data Sets Using Excel Power Query

The purpose of this tutorial is to merge data from two data sets. To do this we need the products table in excel format, which you can get here: Products – Master 

In a new excel spreadsheet we will first import the Products table from excel spreadsheet.

Next we will connect to the Northwind OData Feed and then import the columns we are interested in from the Order_Details table.

Import Excel products table

On the Data tab select

Select “Get data” in the left of the tab. From the  drop down select “From File” and then select excel

When the Navigator appear select the Products worksheet. Press Edit at the bottom.

You should see something like this below in the Query Editor

You should always check the top row of the columns just to make sure the columns are correct.  If they are not you may have to change these by using “Use first row as headers”.

Make top row as headers

If the first row has headers like Column 1, Column 2, etc then you will need to make the first row as headers. If the headers are okay then you can skip this section.

There are two places where you can select “Use first row as headers”.  You can do this from either the top bar or you can select the table icon at the top of the column.

Remove unwanted columns

Now we need to remove all the rows that are not of interest to us. We only want ProductID, ProductName, CategoryID, and QuantityPerUnit.

To select the columns to include click on the individual columns whilst holding down the Ctrl button.

Then select “Remove other columns”

Select Close & Load to finish the import

A new sheet is created in the Excel spreadsheet.

Connect to a OData Feed

Connect to data feed

On the Data tab select

Get Data drop down > From Other Sources > From OData Feed

Add your data source URL, in this case Northwind, and click OK

http://services.odata.org/Northwind/Northwind.svc

Select the  Order_Details table and then press Edit.

The Power Query Editor now opens.

Make top row as headers

 

In Power Query, a column containing a link to a related table has an Entry link or Table link. An Entry link navigates to a single related record. A Table link navigates to a related table. A link represents navigation properties in a data source within a relational model. For an OData feed, navigation properties represent an entity with a foreign key association. For a database, such as SQL Server, navigation properties represent foreign key relationships in the database (read more).

Next we have to expand the Order_details table that is related to the Products table.  This is to join the ProductID, UnitPrice and Quantity columns of the Order_Details into the Product table.

The Expand operation combines the columns from a related table into a subject table.

When the query runs rows from the related table (Order_Details) are combined into the rows from the subject table (Product).

Expand Product Column

In the Preview editor, click an Entry link in a column that is related to the subject table (Product).

You need to click on this icon    and then deselect everything except for the items you want.

Select ProductID, QuantityPerUnit and UnitPrice.

Once you click OK the columns are added as shown below.

Expand Order Column

Just like we did above we are going to expand and then select OrderID, OrderDate, Shipper, and anything else that might be of use to you.

Once done select OK

Calculate row revenue

Add a custom column to add a calculation to sum Product.UnitPrice * Quantity

Add custom column as shown below.

A pop-up will appear. Give your formula a name that makes sense. Add this formula

= [Quantity]*[Product.UnitPrice]

Change the data type of the new column to currency

Change Date to Year

Select OrderDate. In the Transform tab select Date and then navigate to Year.

Group rows by ProductID and Year

Click the Group By icon in the Transform tab.

Select OrderDate and ProductID.

Give the new column the name of Total sale and select the fields as shown below. Click OK.

Your query should now look like this:

Merge Queries

We are now going to merge both the Products and Order_Details data.

On the home tab select Merge Queries and then Merge Queries as New.

A dialog box will appear as show below. Select

You should now see something like this:

 

This article is based on an article on the Microsoft support site.

 

Leave a Reply

Your email address will not be published. Required fields are marked *