Analyze sales data from Excel and an OData feed

It’s common to work with data stored in multiple sources—for example, product information in one database and sales information in another. With Power BI Desktop, you can bring data from these different sources together, transform it, and create compelling reports with interactive visualizations.

In this tutorial, you’ll:

• Import product data from an Excel workbook

• Import sales data from an OData feed

• Transform and combine the data

• Build a sales analysis report with interactive visuals

Later, you can apply the same techniques to other sources like SQL Server, CSV files, or cloud data.

Step 1: Import Excel Product Data

1. Download and save Products.xlsx.

2. In Power BI Desktop, go to Home > Get data > Excel.

3. Select the Products.xlsx file and choose Open.

4. In the Navigator, select the Products table, then click Transform Data.

5. In Power Query Editor, keep only the following columns:

   • ProductID

   • ProductName

   • QuantityPerUnit

   • UnitsInStock
     Use Remove Other Columns to simplify the dataset.

Step 2: Import OData Orders Data

1. In Power Query Editor, select New Source > OData feed.

2. Enter the Northwind OData URL:

    https://services.odata.org/V3/Northwind/Northwind.svc/
   

3. From the Navigator, select the Orders table.

Expand Related Table

• In the Orders table, locate the Order_Details column.

• Click the Expand icon and select:

  • ProductID

  • UnitPrice

  • Quantity

This flattens the nested data into your Orders table.

Step 3: Add a Custom Column

1. In Power Query Editor > Add Column, select Custom Column.

2. Name the column LineTotal.

3. Formula:

    [Order_Details.UnitPrice] * [Order_Details.Quantity]
   

4. Change the column’s data type to Fixed decimal number.

Step 4: Clean Up Orders Data

Keep only these columns:

• OrderDate

• ShipCity

• ShipCountry

• ProductID

• UnitPrice

• Quantity

• LineTotal

Rename the columns by removing the Order_Details. prefix, and reorder so LineTotal is easy to find.

Step 5: Review Query Steps

All transformations are recorded in Query Settings > Applied Steps. You can review or adjust them anytime. Advanced users can view the underlying M language in the Advanced Editor.

Step 6: Load Data into Power BI

When ready, select Close & Apply. Your queries now appear in the Fields pane in Report View.

Step 7: Manage Relationships

Power BI automatically detects a relationship between Products.ProductID and Orders.ProductID.

• Go to Modeling > Manage Relationships to confirm or edit.

• In Model view, you’ll see the relationship line between the two tables.

Step 8: Build Visualizations

Now use both datasets to create visuals:

1. Quantity by Product

   • Create a stacked column chart.

   • Use Orders.Quantity and Products.ProductName.

   • Sort by Quantity (descending).

2. Sales Over Time

   • Create a line/column chart.

   • Use Orders.LineTotal and Orders.OrderDate.

   • Expand the date hierarchy if needed.

3. Sales by Country

   • Add a Map visualization.

   • Use Orders.ShipCountry for location.

   • Add LineTotal to Size so circle size reflects sales.

Step 9: Interact with the Report

Power BI visuals are interactive. For example:

• Selecting Canada on the map filters the product and time charts.

• Selecting a product or a specific order date filters the other visuals automatically.

Final Report

Your finished report combines data from Excel and the Northwind OData feed. You can now:

• Analyze product sales across regions and time.

• Explore interactions between datasets.

• Publish your report to the Power BI service to share with others.