Dataflow entities in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst at a retail company.

📋 Request: Your manager wants you to create a reusable data source that cleans and combines customer and sales data for multiple reports.

📊 Data: You have two raw data tables: Customers (CustomerID, Name, Region) and Sales (SaleID, CustomerID, Date, Amount).

🎯 Deliverable: Create a Power BI dataflow with entities that clean and merge these tables, then use the dataflow in a report showing total sales by region.

Sample Data

CustomerID	Name	Region
C001	Alice	North
C002	Bob	East
C003	Charlie	South
C004	Diana	West

SaleID	CustomerID	Date	Amount
S1001	C001	2024-01-10	250
S1002	C002	2024-01-15	450
S1003	C001	2024-02-05	300
S1004	C003	2024-02-20	150
S1005	C004	2024-03-01	500

1

Step 1: Create a new Power BI dataflow in the Power BI service.

Go to workspace > New > Dataflow > Define new entities.

Expected Result

A new dataflow ready to add entities.

2

Step 2: Add the Customers table as an entity in the dataflow.

Connect to the Customers data source, load the table, and remove any duplicate CustomerID rows.

Expected Result

Customers entity with unique CustomerID rows.

3

Step 3: Add the Sales table as another entity in the dataflow.

Connect to the Sales data source, load the table, and filter out any sales with Amount <= 0.

Expected Result

Sales entity with only valid sales records.

4

Step 4: Create a new entity that merges Customers and Sales entities on CustomerID.

Use Power Query merge: Customers join Sales on CustomerID, expand Sales columns Date and Amount.

Expected Result

Merged entity with CustomerID, Name, Region, Date, and Amount columns.

5

Step 5: Save and refresh the dataflow to load the entities.

Click Save and Refresh in dataflow settings.

Expected Result

Dataflow entities are refreshed and ready for use.

6

Step 6: In Power BI Desktop, connect to the dataflow and load the merged entity.

Get Data > Power BI dataflows > Select the merged entity.

Expected Result

Merged data available in Power BI Desktop.

7

Step 7: Create a report visual showing total sales by Region.

Create a bar chart: Axis = Region, Values = SUM of Amount.

Expected Result

Bar chart showing total sales per region.

Final Result

Sales by Region Report

Region   | Total Sales
---------------------
North    | ███████ 550
East     | ██████ 450
South    | ██ 150
West     | ████████ 500

(Bar chart with sales amounts)

✓North region has total sales of 550.

✓East region has total sales of 450.

✓West region has total sales of 500.

✓South region has the lowest sales at 150.

Bonus Challenge

Add a calculated column in the dataflow merged entity to categorize sales as 'High' if Amount > 400, else 'Low'.

Show Hint

Use Power Query conditional column feature to create the category.