Dashboard Mode - Why Power Query transforms messy data
Business Question
How can we clean and organize messy sales data to get clear monthly sales totals by product?
0
0
Why Power Query transforms messy data in Excel - Dashboard Impact
Sample Data
| Date | Product | Sales | Notes |
|---|---|---|---|
| 2024-01-05 | Apples | 100 | Delivered late |
| 01/15/2024 | Bananas | 200 | Good quality |
| 2024/02/10 | Apples | 150 | On time |
| Feb 20, 2024 | Bananas | 180 | Discount applied |
| 2024-03-01 | Apples | 120 | Returned items |
| 03/15/2024 | Bananas | 210 | Good quality |
| 2024-03-20 | Apples | 130 | On time |
Dashboard Components
- Cleaned Data Table: Shows the sales data with consistent date format and no extra notes.
Formula/Process: Use Power Query to change all date formats to yyyy-mm-dd, remove the Notes column, and trim spaces.
Result: Dates standardized, Notes removed. - Monthly Sales Summary: Pivot table showing total sales by Product and Month.
Formula: =SUMIFS(Sales, Product, product_name, Month, month_number) after extracting month from cleaned dates.
Result: Apples and Bananas sales totals for Jan, Feb, Mar. - KPI Cards: Total Sales for Apples and Bananas.
Formula: =SUMIFS(Sales, Product, "Apples") and =SUMIFS(Sales, Product, "Bananas")
Result: Apples total 500, Bananas total 590.
Dashboard Layout
+----------------------+----------------------+ | KPI Apples | KPI Bananas | +----------------------+----------------------+ | | | Monthly Sales Summary (Pivot) | | | +----------------------------------------------+ | | | Cleaned Data Table | | | +----------------------------------------------+
Interactivity
Adding a filter for Month lets you select a specific month. This updates the Monthly Sales Summary and KPI cards to show sales only for that month. The Cleaned Data Table always shows all cleaned data.
Self Check
If you add a filter for Month = February, which components update and what sales totals do you see for Apples and Bananas?
Answer: The Monthly Sales Summary and KPI cards update. Apples sales show 150, Bananas sales show 180 for February.
Key Result
A dashboard showing how Power Query cleans messy sales data and summarizes monthly sales by product.