Creating Sales Report Using OData Feed With Power BI Designer

Introduction

In this article we will import data from multiple data sources, one from an Excel Workbook that contains information about the Products and the other from an OData feed that contains the information about Product Orders. We will perform the procedure to filter and remove the unnecessary data and then will combine the data from both of the sources to produce Total sales per Product with some interactive visualizations.

Use the following procedure.

Step 1: Connecting to products Excel workbook data

• Open Power BI Designer and select Excel from the Get Data dialog box and click Connect and select the Products.xlsx file from the open file dialog box.
  

• From the Navigator pane select Products table and click on Edit Query.

Step 2: Remove unnecessary column

• Now we will remove some of the unnecessary columns from the Products table. Except the ProductID, ProductName, UnitsinStock and QuantityPerUnit columns, remove all the other columns. Select these four columns and right-click on the header of any one of the columns and click on Remove Other Columns.
  
  
  

Step 3: Change Data Type of the column

• Let us now change the Data type of the UnitsInStock column to Whole Number datat ype by right-clicking on the column and selecting Whole Number from the Change Type option.
  
  

Step 4: Importing Order data from an OData feed

• Under the Home ribbon tab, click on Get Data and select the OData feed from the data source list and enter the following link into the OData Feed dialog box.
  
  Northwind OData feed
  

• Select the Orders table from the navigator pane and click on Edit Query.
  
  
  

Expand the Orders_Details table

• The table Order_Details is related to the Orders table. The Order_Details table contains several columns as shown below and we need to expand and combine these columns into the Orders table from the Order_Details table.
  
  
  

• Click on the Expand icon present at the Order_Details column header and select the ProductID, UnitPrice and Quantity columns as shown above and click on OK.

Step 5: Remove unnecessary columns

• Select the ShipCity, OrderDate, ShipCountry, Order_Details.ProductID, Order_Details.UnitPrice and Order_Details.Quantity columns and right-click on any one of the headers of the selected column and select the Remove Other Columns option.
  
  
  

Rename Columns

• Remove the Order_Details prefix from the Order_Details.ProductID, Order_Details.Quantity and Order_Details.UnitPrice columns by right-clicking on each of the column headers and and select the rename option.

Step 6: Creating a Custom Column

• Click on the Add Custom Column option available under the Add Column ribbon tab, a new Add Custom Column window will appear, enter [UnitPrice] * [Quantity] into the Custom column formula TextBox and add LineTotal in the New column name TextBox.
  

• Click OK.
  
  

• Right-click on the header of the LineTotal column and change the type to Decimal Number.
  
  
  

Step 7: Combine Products and Orders Query

• Click on the report button available at the bottom left-hand side that will start loading the data from our two queries.
  
  

• Click on the Manage button present under the Relationships group.
  
  
  

• Click on the New button that will open a Create Relationships window. Select the Products table from the From: Table drop down menu that will automatically select the Orders table in the To: Table drop down menu.
  

• From the From: Column list box select ProductID and from the To: Column list box select ProductID.
  

• Click OK.
  
  
  

• Click Close.
  
  

Step 8: Creating Visualizations

• Drag ProductName to the report canvas from the Fields list and ensure the UnitsInStock is under the Value section and ProductName is under the Axis section.
  
  
  

• Drag OrderDate to the report canvas from the Fields list and be sure the LineTotal is under the Values section and Orderdate is under the Axis section.
  
  
  

• Drag ShipCountry to the report canvas from the Fields list. Since we have selected a geographical field, a map will be generated automatically and be sure the visualization type for the map is Filled Map visualization.
  
  
  Based on the sum of LineTotals the outline of countries are shaded where the darker color represents the higher number of total orders in that country.

Step 9: Interacting with the visual reports

• Click on any of the color outlines of the country and other visuals will be filtered to show the UnitsInStock and LineTotal for that country. Here I am selecting the country Brazil that will change other two visuals for the country Brazil.
  
  

Summary

We are now ready with the sales report visualizations that helps to analyze the sales information for various countries after combining the data from two sources, one from the Products Excel workbook and the other from the OData feed.