
π 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
β¬ Download all 2 files (ZIP Β· 11 KB)
- π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.