Concept
1. The problem
Branch target sheet (wide):
| Region | Apr | May | Jun |
|---|---|---|---|
| North | 150000 | 170000 | 190000 |
| South | 140000 | 80000 | 60000 |
| West | 100000 | 120000 | 90000 |
You can't put "Month" in a pivot, slicer or relationship — it isn't a column.
2. Unpivot Other Columns
Load it into Power Query → select Region (the column that should stay) → right-click → Unpivot Other Columns:
= Table.UnpivotOtherColumns(Source, {"Region"}, "Attribute", "Value")
Rename Attribute → Month, Value → Target:
| Region | Month | Target |
|---|---|---|
| North | Apr | 150000 |
| North | May | 170000 |
| North | Jun | 190000 |
| South | Apr | 140000 |
| … | … | … |
3 rows × 3 months = 9 rows.
Why "Other"? When July is added as a new column next month, it's unpivoted automatically. "Unpivot Columns" (selecting Apr–Jun) would ignore July.
3. Turn the month text into a date
"Apr" can't be grouped or joined to a calendar. Add Column → Custom: Date.FromText("1 " & [Month] & " 2026", "en-IN") → type Date. Better: get headers like 2026-04 in the source, or keep a small month-mapping table and merge (Lesson 5).
4. Two header rows (Region × Category)
| Electronics | Electronics | Furniture | Furniture | |
|---|---|---|---|---|
| Region | Apr | May | Apr | May |
- Transform → Transpose (rows become columns).
- Fill Down the category column.
- Merge the two header columns into one ("Electronics|Apr") → Transpose back → Use First Row as Headers.
- Unpivot Other Columns → Split Column by Delimiter "|" → Category and Month.
It looks long, but it's recorded once and works every month.
5. Remove totals before unpivoting
A "Total" column or row would become fake data (Month = "Total"). Remove the Total column (or filter Month ≠ "Total" after unpivot) and filter out total rows.
6. Pivot Column — the opposite
Sometimes you need wide (e.g. Target and Actual as two columns side by side). Select the Attribute column → Transform → Pivot Column → Values = Value, aggregation Sum (or "Don't Aggregate").
7. Unpivot in the capstone
targets.csv is already tidy (Month, Region, Target). If your company sends targets in the wide format, this lesson's steps convert it to the same shape so it can join the model in Module 4.
Common mistakes
Selecting the month columns and using "Unpivot Columns" (new months ignored). Leaving a Total column (doubles numbers). Keeping "Apr" as text, so it can't sort or join.