Normalizing Wide Cross-Tab Reports with Power Query Unpivot

Question

A client provides an expense budget where Column A is Department, and Columns B through M contain months ('Jan', 'Feb', ..., 'Dec') with dollar amounts. You cannot build a proper Pivot Table because the months are spread across 12 separate columns. Explain how to normalize this in Power Query using Unpivot.

Accepted Answer

Optimal Solution:
Power Query M-Code:
let
    Source = Excel.CurrentWorkbook(){[Name="WideBudget"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Department"}, "Month", "Expense")
in
    Unpivoted

Technical Explanation:
In Power Query, right-click the 'Department' column and select 'Unpivot Other Columns'. Power Query collapses all other month columns into two normalized attributes: 'Month' (text) and 'Expense' (number).

Department (Col A)	Jan (Col B)	Feb (Col C)	Mar (Col D)
Marketing	10000	12000	15000
Engineering	40000	42000	45000

Department	Month (Attribute)	Expense (Value)
Marketing	Jan	10000
Marketing	Feb	12000
Marketing	Mar	15000
Engineering	Jan	40000
Engineering	Feb	42000
Engineering	Mar	45000

	A	B	C	D	E
1	Department (Col A)	Jan (Col B)	Feb (Col C)	Mar (Col D)
2	Marketing	10000	12000	15000	Target [Enter Formula]
3	Engineering	40000	42000	45000
4
5
6
7