Power Query Lesson 4: Unpivot and Pivot

Power Query Lesson 4: Unpivot and Pivot featured illustration

πŸ“Ž This article includes 2 downloadable practice files ↓

⏱ 2 min read

πŸ“˜ Power Query Beginner Course Β· Lesson 4 of 10

In this article
  1. Wide vs long
  2. Unpivot Other Columns
  3. Make Month a real date
  4. Pivot: the reverse
  5. Where beginners go wrong
  6. Practice
  7. Continue learning

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.

Advertisement

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

  1. Load pq-04-months-wide.xlsx (From Workbook β€Ί select the sheet β€Ί Transform Data).
  2. Select the Region column (the one to keep).
  3. Right-click β€Ί Unpivot Other Columns.
  4. Rename Attribute β†’ Month and Value β†’ Amount.

4 regions Γ— 6 months = 24 rows.

πŸ’‘ Always use Unpivot OTHER Columns (selecting the columns to keep), not Unpivot Columns (selecting the months). When October appears next month, ‘other columns’ includes it automatically; a fixed list of months would miss it.

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.

Advertisement

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.

Advertisement

πŸ“Ž Practice files for this article

⬇ Download all 2 files (ZIP Β· 11 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.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free Β· AI can be wrong

Leave a Comment