📎 This article includes 2 downloadable practice files ↓
In this article
People love building sheets with months across the top: Region, Apr, May, Jun… It reads nicely, but it’s the wrong shape for analysis. You can’t easily filter by month, a pivot can’t group it, and adding October means changing every formula. Unpivot fixes it.
Wide vs long
| Wide (report shape) | Long (data shape) |
|---|---|
| Region | Apr | May | Jun | Region | Month | Amount |
| One row per region | One row per region per month |
| Good for reading | Good for pivots, charts, filters, Power BI |
Unpivot Other Columns
- Load
pq-04-months-wide.xlsx(From Workbook › select the sheet › Transform Data). - Select the Region column (the one to keep).
- Right-click › Unpivot Other Columns.
- Rename Attribute → Month and Value → Amount.
4 regions × 6 months = 24 rows.
Make Month a real date
“Apr” sorts alphabetically. Add a column that turns it into a date, for example Add Column › Custom Column: Date.FromText("1 " & [Month] & " 2026"), set the type to Date, then sort by it. Lesson 9 covers dates properly.
Pivot: the reverse
Select the Month column › Transform › Pivot Column › Values column: Amount › Aggregate: Sum. Useful when a system exports long data and you need a cross-tab to share. But do analysis on the long version.
Where beginners go wrong
| Mistake | Effect |
|---|---|
| Unpivoting a Total column too | Totals double; remove total rows/columns first |
| Unpivot Columns with months selected | New months ignored next month |
| Months as text | Charts sort Apr, Aug, Jul… |
More: unpivot months in Power Query.
Practice
Download the source file(s) and the expected-results workbook below. Build the query in Excel (Data › Get Data), load it to a sheet, and compare your row count and totals with the Checks sheet.
📎 Practice files for this article
- 📗pq-04-months-wide.xlsxSales by region with one column per month (Apr-Sep).⬇ XLSX · 5 KB
- 📗Expected resultsWhat your query should produce, with check totals.⬇ XLSX · 6 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.