Why clean data drives accurate reports in Power BI - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants a sales report that shows accurate total sales by product category and region for the last quarter.

📊 Data: You have a sales dataset with columns: OrderID, ProductCategory, Region, SalesAmount, and OrderDate. The data contains some missing values, duplicates, and inconsistent region names.

🎯 Deliverable: Create a clean and accurate sales report in Power BI showing total sales by product category and region for the last quarter.

Sample Data

OrderID	ProductCategory	Region	SalesAmount	OrderDate
1001	Electronics	North	250	2024-03-15
1002	Clothing	South	150	2024-03-20
1003	Electronics	north	300	2024-04-05
1004	Clothing	East		2024-04-10
1005	Home	West	400	2024-04-15
1006	Electronics	South	200	2024-04-20
1007	Home	West	400	2024-04-15
1007	Home	West	400	2024-04-15
1008	Clothing	East	180	2024-05-01
1009	Electronics	North	350	2024-05-05
1010	Home	South	500	2024-05-10

1

Step 1: Load the sales data into Power BI and open Power Query Editor.

Use 'Get Data' to import the dataset and click 'Transform Data' to open Power Query Editor.

Expected Result

Sales data loaded and visible in Power Query Editor for cleaning.

2

Step 2: Remove duplicate rows based on OrderID to avoid counting sales twice.

In Power Query, select the OrderID column, then choose 'Remove Duplicates'.

Expected Result

One duplicate row with OrderID 1007 is removed, leaving unique orders.

3

Step 3: Fix inconsistent region names by standardizing text case.

Select the Region column, then use 'Transform' > 'Format' > 'Capitalize Each Word'.

Expected Result

Regions like 'north' become 'North' for consistency.

4

Step 4: Handle missing SalesAmount values by replacing them with zero.

Select SalesAmount column, then 'Transform' > 'Replace Values', replace null or blank with 0.

Expected Result

Missing sales amounts are replaced with 0 to avoid calculation errors.

5

Step 5: Close and apply changes to load cleaned data into Power BI report view.

Click 'Close & Apply' in Power Query Editor.

Expected Result

Cleaned data is loaded into Power BI for report building, with all time periods.

6

Step 6: Apply a filter for the last quarter (Q1 2024) using the Filters pane.

In Report view, drag OrderDate to the Filters pane (Page level), set to 'Between' 2024-01-01 and 2024-03-31.

Expected Result

Report now shows data only for Q1 2024.

7

Step 7: Create a new measure to calculate total sales.

Total Sales = SUM('Sales'[SalesAmount])

Expected Result

Measure 'Total Sales' calculates sum of sales amounts.

8

Step 8: Build a matrix visual with Rows = ProductCategory, Columns = Region, Values = Total Sales.

Insert Matrix visual, drag ProductCategory to Rows, Region to Columns, and Total Sales measure to Values.

Expected Result

Matrix shows total sales by product category and region.

9

Step 9: Enable 'Show items with no data' for complete view of all categories and regions.

In Visualizations > Fields, click the down arrow next to ProductCategory (Rows) > 'Show items with no data'. Repeat for Region (Columns).

Expected Result

Matrix displays all product categories and regions with 0s where no sales in Q1.

Final Result

Sales Report - Total Sales by Product Category and Region (Q1 2024)

+----------------+---------+---------+---------+---------+
| ProductCategory|  East   |  North  |  South  |  West   |
+----------------+---------+---------+---------+---------+
| Electronics    |    0    |   250   |    0    |    0    |
| Clothing       |    0    |    0    |   150   |    0    |
| Home           |    0    |    0    |    0    |    0    |
+----------------+---------+---------+---------+---------+
| Total Sales    |    0    |   250   |   150   |    0    |
+----------------+---------+---------+---------+---------+

✓Removing duplicates prevented double counting of sales.

✓Standardizing region names ensured accurate grouping.

✓Replacing missing sales with zero avoided calculation errors.

✓Filtering to the last quarter focused the report on relevant data.

✓The Electronics category had the highest sales in the North region.

✓Clothing sales were mainly in the South region during the quarter.

Bonus Challenge

Create a Power BI report page that shows monthly sales trends by region with a line chart.

Show Hint

Use the cleaned data, create a Date table, relate it to sales, and use a line chart with Month on X-axis and Total Sales on Y-axis, with Region as legend.