Creating dataflows in Power BI - Business Scenario Walkthrough

Scenario Mode

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

📋 Request: Your manager wants you to create a reusable dataflow that combines sales and product data to support multiple reports.

📊 Data: You have two data sources: Sales data with columns Date, ProductID, Quantity, and SalesAmount; Product data with columns ProductID, ProductName, and Category.

🎯 Deliverable: Create a dataflow in Power BI that merges sales and product data, cleans it, and makes it available for report building.

Sample Data

Date	ProductID	Quantity	SalesAmount
2024-01-01	101	5	100
2024-01-02	102	3	60
2024-01-03	101	2	40
2024-01-04	103	4	80
2024-01-05	102	1	20

ProductID	ProductName	Category
101	Notebook	Stationery
102	Pen	Stationery
103	Water Bottle	Accessories

1

Step 1: Connect to the Sales data source in Power BI dataflows.

Use Power Query to connect to the Sales data table with columns Date, ProductID, Quantity, SalesAmount.

Expected Result

Sales data is loaded into the dataflow for transformation.

2

Step 2: Connect to the Product data source in Power BI dataflows.

Use Power Query to connect to the Product data table with columns ProductID, ProductName, Category.

Expected Result

Product data is loaded into the dataflow for transformation.

3

Step 3: Merge Sales and Product tables in the dataflow using ProductID as the key.

In Power Query, perform a Merge Queries operation: Sales left join Product on ProductID.

Expected Result

A combined table with sales and product details is created.

4

Step 4: Remove unnecessary columns and rename columns for clarity.

Keep columns Date, ProductID, ProductName, Category, Quantity, SalesAmount. Rename columns if needed.

Expected Result

Cleaned combined table ready for use.

5

Step 5: Save and refresh the dataflow to make it available for reports.

Save the dataflow with a descriptive name like 'SalesProductDataflow' and refresh it.

Expected Result

Dataflow is ready and accessible for report building.

Final Result

---------------------------------------------
| Date       | ProductID | ProductName | Category    | Quantity | SalesAmount |
|------------|-----------|-------------|-------------|----------|-------------|
| 2024-01-01 | 101       | Notebook    | Stationery  | 5        | 100         |
| 2024-01-02 | 102       | Pen         | Stationery  | 3        | 60          |
| 2024-01-03 | 101       | Notebook    | Stationery  | 2        | 40          |
| 2024-01-04 | 103       | Water Bottle| Accessories | 4        | 80          |
| 2024-01-05 | 102       | Pen         | Stationery  | 1        | 20          |
---------------------------------------------

✓The dataflow successfully combines sales and product information.

✓It enables easy access to enriched data for multiple reports.

✓Data is clean and ready for analysis without repeated preparation.

Bonus Challenge

Add a calculated column in the dataflow that computes UnitPrice as SalesAmount divided by Quantity per row.

Show Hint

In Power Query, create a custom column with the formula: [SalesAmount] / [Quantity].