Power Query Masterclass
Learn by watching, then master by doing.
Core Concepts Visualized
Before you touch the simulators, click "Play Demo" on each card below to see exactly how data moves and transforms.
Unpivot Columns
Wide Data (Input)
| Product | Jan | Feb |
|---|---|---|
| Apple | 10 | 15 |
| Pear | 20 | 25 |
Tall Data (Output)
| Product | Attribute | Value |
|---|---|---|
| Click play to watch data move... | ||
Pivot Column
Tall Data (Input)
| Store | Metric | Value |
|---|---|---|
| A | Sales | $50 |
| A | Costs | $20 |
Wide Data (Output)
| Store | ... | ... |
|---|---|---|
| Click play to reshape... | ||
Group By (Summarize)
Detailed Data (Input)
| Region | Amount |
|---|---|
| North | 100 |
| North | 50 |
| North | 200 |
| Region | Total (Sum) |
|---|---|
| Click play to aggregate... | |
Case 1: The Expense Report
The Analytical Goal
You need to build a PivotTable to sum up "Total Q1 Expenses" by Store Location.
The Data Problem
"Month" is not a column you can filter by; the months are spread horizontally as headers. You must Unpivot the data, then Group By store.
APPLIED STEPS
Case 2: Logistics Report
The Analytical Goal
Calculate exact fleet costs grouped by Location AND Vehicle Type.
The Data Problem
The Location and Vehicle are mashed together into one string. You must Split the column first, Unpivot, and do an Advanced Group By.
APPLIED STEPS
Case 3: Spokane Hardware POS
The Analytical Goal
Find out exactly which products are selling the most by counting the total number of times each unique item was purchased today.
The Data Problem
The Point of Sale system lumps all items from a single receipt into one comma-separated cell. You must Split the text, Unpivot the resulting columns, and group them to Count Rows.
APPLIED STEPS
Case 4: River City Roasters
The Analytical Goal
You want a clean summary table with one row per store and separate columns for each inventory item to quickly compare stock levels across the chain.
The Data Problem
The inventory system exports data in a 'tall' attribute-value structure. You need to Pivot the 'Metric' column to turn its contents into wide column headers.