Why dataflows centralize data preparation in Power BI - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants you to explain and demonstrate how dataflows can centralize data preparation to improve reporting efficiency and consistency.

📊 Data: You have sales data from multiple stores in Excel files and customer data from a CRM system. These data sources need cleaning and combining before analysis.

🎯 Deliverable: Create a Power BI dataflow that cleans and combines these data sources centrally, then build a simple report using the dataflow to show monthly sales by store.

Sample Data

StoreID	StoreName	SaleDate	SalesAmount
101	North Store	2024-01-05	1500
102	East Store	2024-01-06	1200
101	North Store	2024-02-10	1800
103	West Store	2024-02-15	1100
102	East Store	2024-03-01	1300
103	West Store	2024-03-05	1400
101	North Store	2024-03-10	1600
104	South Store	2024-03-12	1700

1

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

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

Expected Result

A blank dataflow ready to add data entities.

2

Step 2: Connect to the Excel sales data files and import the sales table.

Select 'Add new entities' > Choose Excel files from OneDrive or SharePoint > Select sales table.

Expected Result

Sales data loaded into the dataflow as an entity.

3

Step 3: Clean the sales data by removing any rows with missing StoreID or SalesAmount.

In Power Query editor, filter out rows where StoreID or SalesAmount is null or blank.

Expected Result

Sales data contains only complete rows with StoreID and SalesAmount.

4

Step 4: Connect to the CRM system to import customer data as another entity.

Add new entity > Connect to CRM data source > Select customer table.

Expected Result

Customer data loaded into the dataflow as a separate entity.

5

Step 5: Create a relationship between sales and customer data using StoreID.

In dataflow, define StoreID as key to link sales and customer entities.

Expected Result

Dataflow entities linked for combined analysis.

6

Step 6: Save and refresh the dataflow to apply changes and prepare data centrally.

Click Save > Refresh dataflow to load cleaned and combined data.

Expected Result

Dataflow contains clean, combined data ready for reporting.

7

Step 7: In Power BI Desktop, connect to the dataflow as a data source.

Get Data > Power BI dataflows > Select the created dataflow.

Expected Result

Dataflow data loaded into Power BI Desktop.

8

Step 8: Create a report visual showing monthly sales by store using the dataflow data.

Create a line chart: Axis = SaleDate (month), Legend = StoreName, Values = SUM(SalesAmount).

Expected Result

Report shows monthly sales trends for each store.

Final Result

Monthly Sales by Store

StoreName  | Jan | Feb | Mar
-----------|-----|-----|-----
North Store|1500 |1800 |1600 
East Store |1200 |     |1300 
West Store |     |1100 |1400 
South Store|     |     |1700 

✓Dataflows centralize data cleaning and combining steps, so reports use consistent data.

✓Monthly sales trends by store are easy to update by refreshing the dataflow once.

✓Central data preparation saves time and reduces errors across reports.

Bonus Challenge

Extend the dataflow to include product category data and create a report showing sales by category and store.

Show Hint

Add a new entity for product categories, link it to sales by product ID, then update the report visuals.