The Scenario: The "Dirty" Sales Export
You receive this data every month. It's a mess. Review Dataset 1 and 2, and notice how Dataset 3 introduces new Q2 columns. To progress, scroll down and click the hidden cards to see how Power Query destroys the competition.
Dataset 1: Messy Sales (Tab 1)
| Order_Info | Category | Region_ID | Jan_Sales | Feb_Sales | Mar_Sales |
|---|---|---|---|---|---|
| ORD101-smith | lapTOps | R01 | 1200 | 1500 | 1100 |
| ORD102-JONES | TabLEts | R02 | 800 | 950 | 900 |
| ORD103-davis | monitors | R01 | 450 | 500 | 480 |
Dataset 3: Updated Sales Table (Now Includes Q2)
| Order_Info | Category | Region_ID | Jan_Sales | Feb_Sales | Mar_Sales | Apr_Sales | May_Sales | Jun_Sales |
|---|---|---|---|---|---|---|---|---|
| ORD101-smith | lapTOps | R01 | 1200 | 1500 | 1100 | 1300 | 1400 | 1500 |
| ORD102-JONES | TabLEts | R02 | 800 | 950 | 900 | 900 | 850 | 950 |
| ORD103-davis | monitors | R01 | 450 | 500 | 480 | 500 | 520 | 510 |
Dataset 2: Lookup (Tab 2)
| Region_ID | Region_Name |
|---|---|
| R01 | North America |
| R02 | Europe |
| R03 | Asia Pacific |
Unlock the Solutions
Click the dark cards on the right to reveal the Power Query magic.
The Hard Way
Task 1: Split "Order_Info"
Separate "ORD101-smith" into Order ID and Salesperson.
Col 1: =LEFT(A2, FIND("-", A2)-1)
Col 2: =RIGHT(A2, LEN(A2)-FIND("-", A2))
Breaks if a hyphen is missing. You have to drag it down 10,000 rows.
Reveal Magic #1
Click to unmask the Power Query spell
Power Query
Task 1: Split "Order_Info"
Separate "ORD101-smith" into Order ID and Salesperson.
Zero formulas. Done in 3 clicks. Handles errors automatically.
The Hard Way
Task 2: Clean Category Text
Fix " lapTOps " to "Laptops".
=PROPER(TRIM(B2))
Creates redundant columns. You must copy/paste as values to delete the original.
Reveal Magic #2
Click to uncover the text cleanup spell
Power Query
Task 2: Clean Category Text
Fix " lapTOps " to "Laptops".
Right-Click Column → Transform → Capitalize Each Word
Cleans data in place. No messy helper columns needed.
The Hard Way
Task 3: Unpivot Months
Turn wide columns (Jan, Feb, Mar) into a database format (Month | Sales).
Extreme INDEX/MATCH/OFFSET array math, or manual Copy > Paste Special > Transpose for hours.
The breaking point where most people give up and cry.
Reveal Magic #3
The Ultimate Move
Power Query
Task 3: Unpivot Months
Turn wide columns (Jan, Feb, Mar) into database format.
Right-Click → Unpivot Other Columns
Takes 2 seconds. Transposes data instantly without touching a keyboard.
The Hard Way
Task 4: Get Region Name
Lookup Region Name from Dataset 2 based on Region_ID.
=VLOOKUP(C2, RegionMap!A:B, 2, FALSE)
VLOOKUPs crash Excel when dealing with hundreds of thousands of rows.
Reveal Magic #4
Click to uncover the merging spell
Power Query
Task 4: Get Region Name
Lookup Region Name from Dataset 2.
Select matching ID columns → OK
Acts like a SQL Join in the background. Highly memory efficient and fast.
The Hard Way
Task 5: Next Month's Data Arrives
The source adds Q2 columns (Apr, May, Jun). You have to update your report.
1. Re-insert columns manually.
2. Drag down ALL old formulas again.
3. Re-do the unpivot nightmare.
4. Check if pivot tables broke.
Hours wasted on repetitive structural updates.
The Final Secret
Click to reveal the biggest time-saver of all
Power Query
Task 5: Next Month's Data Arrives
The source data now contains Apr, May, and Jun columns.
PQ dynamically grabs new columns, cleans them, unpivots them, and merges them automatically.
Classified Data
Unlock all 5 Magic Secrets above to view the Executive Summary Showdown.
0/5 Unlocked
The Ultimate Verdict
| Feature | Excel Grid | Power Query |
|---|---|---|
| Automation | Manual copy-paste every month. | Fully Automated |
| Data Reshaping | Requires complex formulas / VBA. | 100% UI Button Driven |
| Speed (1M+ Rows) | Freezes, crashes, burns. | Blazing Fast (In-Memory) |
You are now ready to clean data.
In Excel, go to Data → Get Data to open the portal to the Power Query editor.