4.3 Reshaping and combining with Power Query
You can join two sources and unpivot a report that was built for humans.
Before:01. Excel — First StepsUnlocks:06. Excel — Automation
Reshaping is the deeper half of Power Query: merging two sources on a key with a chosen join kind, appending a whole folder of files, and unpivoting a report laid out for human eyes back into rows a computer can use. It follows the basics topic because every operation here happens in the same applied-steps editor. Unpivot is the idea people find strangest and then love most: months spread across columns become one column of month and one of value. The common confusion is join kinds, where picking the wrong one silently drops or duplicates rows.
Work through these
Merge queries: join kinds and what each returns
Combining two tables on a shared key, with the join kind deciding what happens to rows that match nothing. Choosing the wrong kind silently drops or duplicates data.
Append queries and combining a folder of files
Stacking tables with the same shape, including every file in a folder at once. This is how a year of monthly reports becomes a single dataset.
Pivot and unpivot columns
Turning columns into rows is what makes a report designed for human eyes usable for analysis. It is the single most valuable transformation in this topic.
Grouping and aggregating in the query rather than the sheet
Summarising inside the query means less data lands in the sheet and the workbook stays fast. Doing the aggregation here rather than afterwards is usually the better order.
Power Query documentation · Docs
Practice file
- xl-06-messy-data.xlsx
A deliberately horrible export: merged headers, blank rows, mixed date formats, stray text in number columns. The Power Query exercise.
Opens in Excel. Every name, number and address in these files is made up. No account needed to download.
Sign in to keep your progress.
Free resources
We haven't checked most of these for screen reader use yet.
Links last checked 29 Aug 2026.
Stuck here?
Ask a mentor. A real person answers, and they can see exactly which topic you're on. Usually within a couple of working days.
Checking your session…
Topics shown in module order.