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.

Practice

1. What is the main purpose of a Dataflow entity in Power BI?

easy

A. To prepare and store data centrally for reuse across reports

B. To create visual charts and graphs

C. To write DAX formulas for calculations

D. To manage user permissions in Power BI

Solution

1. Step 1: Understand what Dataflow entities do

   Dataflow entities are designed to prepare and store data centrally so it can be reused in multiple reports.

2. Step 2: Compare options with this purpose

   Only To prepare and store data centrally for reuse across reports matches this purpose; others describe different Power BI features.

3. Final Answer:

   To prepare and store data centrally for reuse across reports -> Option A

4. Quick Check:

   Dataflow entities = Central data prep and reuse [OK]

Hint: Dataflow entities store reusable data, not visuals or formulas [OK]

Common Mistakes:

• Confusing dataflows with report visuals
• Thinking dataflows manage permissions
• Mixing dataflows with DAX calculations

2. Which syntax correctly describes how to create a new entity in a Power BI Dataflow using Power Query online?

easy

A. Upload Excel files without any transformation

B. Write a DAX formula in the Dataflow editor

C. Use SQL commands directly in the Dataflow interface

D. Select 'New Entity' and use Power Query editor to define data transformations

Solution

1. Step 1: Identify how entities are created in Dataflows

   Entities are created by selecting 'New Entity' and using Power Query online to transform data.

2. Step 2: Check other options for correctness

   DAX formulas and SQL commands are not used directly in Dataflows; uploading files without transformation is incomplete.

3. Final Answer:

   Select 'New Entity' and use Power Query editor to define data transformations -> Option D

4. Quick Check:

   New Entity + Power Query = Correct syntax [OK]

Hint: Dataflow entities use Power Query, not DAX or SQL directly [OK]

Common Mistakes:

• Trying to write DAX in Dataflow creation
• Assuming SQL commands run in Dataflow editor
• Ignoring the need for data transformation steps

3. Given a Dataflow entity that filters sales data for the year 2023 using Power Query, what will be the output if the filter step is removed?

medium

A. All sales data including years other than 2023 will be included

B. Only sales data for 2023 will be shown

C. No data will be returned because filter is mandatory

D. The Dataflow will show an error and not load

Solution

1. Step 1: Understand the role of the filter step

   The filter step limits data to sales from 2023 only.

2. Step 2: Predict output without the filter

   Removing the filter means no restriction, so all sales data from all years will be included.

3. Final Answer:

   All sales data including years other than 2023 will be included -> Option A

4. Quick Check:

   Removing filter = All data included [OK]

Hint: No filter means no restriction on data rows [OK]

Common Mistakes:

• Assuming filter removal causes error
• Thinking filter is mandatory for dataflow
• Believing only 2023 data remains without filter

4. You created a Dataflow entity but it fails to refresh with an error about missing credentials. What is the best way to fix this?

medium

A. Delete and recreate the Dataflow entity

B. Change the entity name to a simpler one

C. Update the data source credentials in the Dataflow settings

D. Remove all transformations from the entity

Solution

1. Step 1: Identify cause of refresh failure

   Missing credentials error means Power BI cannot access the data source due to invalid or missing login info.

2. Step 2: Apply correct fix

   Updating the data source credentials in Dataflow settings restores access and allows refresh.

3. Final Answer:

   Update the data source credentials in the Dataflow settings -> Option C

4. Quick Check:

   Fix missing credentials by updating login info [OK]

Hint: Check and update credentials when refresh fails [OK]

Common Mistakes:

• Recreating entity unnecessarily
• Changing names does not fix credentials
• Removing transformations won't fix access issues

5. You want to create a Dataflow entity that combines customer data from two different sources and removes duplicates. Which approach is best?

hard

A. Write a DAX measure to merge and deduplicate data

B. Use Power Query to append both sources, then apply 'Remove Duplicates' step

C. Load both sources directly into Power BI without transformation

D. Create two separate entities and join them in Power BI Desktop

Solution

1. Step 1: Understand combining data in Dataflow

   Power Query in Dataflows can append multiple sources into one entity.

2. Step 2: Remove duplicates in Dataflow

   Applying 'Remove Duplicates' step in Power Query cleans the combined data before reuse.

3. Step 3: Evaluate other options

   Joining in Power BI Desktop or using DAX is less efficient; loading without transformation keeps duplicates.

4. Final Answer:

   Use Power Query to append both sources, then apply 'Remove Duplicates' step -> Option B

5. Quick Check:

   Append + Remove Duplicates in Dataflow = Best practice [OK]

Hint: Combine and clean data early in Dataflow with Power Query [OK]

Common Mistakes:

• Trying to merge data only in Power BI Desktop
• Ignoring duplicates before loading data
• Using DAX measures for data prep instead of Dataflow