📎 This article includes 1 downloadable practice file ↓

Budgets, SAP exports and Smart View sheets often come “wide”: one row per account and a column for each month. It looks nice, but it is terrible for analysis — you cannot filter by month, pivot tables get awkward and every formula needs twelve ranges. The fix is to unpivot.

Before and after

becomes

Steps

1. Click inside the data and choose Data → From Table/Range (tick “My table has headers”).
2. In Power Query, select the column(s) that describe the row — here Account.
3. Transform → Unpivot Columns → Unpivot Other Columns.
4. Rename Attribute to Month and Value to Amount. Set Amount to Decimal Number.
5. Close & Load.

Make months sortable

“Apr” sorts before “Jan” alphabetically. Add a custom column that turns the month name into a real date:

= Date.FromText("1 " & [Month] & " 2025")

Change its type to Date and sort by it. Now charts and timelines behave.

What you can do now

• A pivot table with Month in columns and Account in rows — in seconds.
• =SUMIFS(Amount, Account, "Travel", Month, "Feb") — one simple formula.
• Load it to the Data Model and build Power BI-style measures.

Want the reverse — a wide summary from a tidy table? That is exactly what a pivot table (or PIVOTBY in Microsoft 365) does.

Try it yourself: step by step

1. Download budget-wide.xlsx: 8 accounts, a cost centre column and 12 month columns.
2. Click any cell in the table → Data → From Table/Range → tick “My table has headers”.
3. Select Account and Cost Centre (hold Ctrl) → Transform → Unpivot Columns → Unpivot Other Columns.
4. Rename the columns to Month and Amount; set Amount to Whole Number. The preview shows 96 rows.
5. Close & Load, then insert a pivot table: Month in Columns, Account in Rows, Amount in Values — you have rebuilt the original view, but now it can be filtered and charted freely.