📎 This article includes 3 downloadable practice files ↓
Reports from other people often look like this: regions as merged cells on row 1, months on row 2, products down the side. Perfect for reading, useless for pivots.
North South
Product Jul Aug Jul Aug
Notebook 120 140 90 95The recipe
- Transpose so the two header rows become two columns.
- Replace blanks with null and Fill Down the region column (merged cells export as one value then blanks).
- Merge the two columns with a separator:
North|Jul. - Transpose back and Use First Row as Headers.
- Unpivot Other Columns (select Product first).
- Split the attribute column by “|” into Region and Month.
Header = Table.CombineColumns(FilledRegion, {"Column1", "Column2"},
each Text.Combine(List.RemoveNulls(_), "|"), "Key"),
Unpivoted = Table.UnpivotOtherColumns(Back, {"Product"}, "Key", "Qty"),
Split = Table.SplitColumn(Unpivoted, "Key", Splitter.SplitTextByDelimiter("|"), {"Region", "Month"})Result: 12 tidy rows (3 products × 2 regions × 2 months), ready for any PivotTable.
Practice
Unzip the source files (e.g. to D:\PQ\). Try to build the query with the ribbon first, then compare with the M solution and the expected result. Every solution was run by Excel’s own Power Query engine before publishing.
📎 Practice files for this article
⬇ Download all 3 files (ZIP · 8 KB)
- 🗂️Practice source files (zip)All messy CSVs for the course: invoices, payments, typed customer names, credit history, contacts, branch files, a two-row-header report and an ERP register.⬇ ZIP · 3 KB
- 📄Lesson 7 M solutionThe full query. Paste into Advanced Editor and change the Folder line.⬇ PQ · 1 KB
- 📗Expected resultWhat the query returned when Excel ran it, to compare with yours.⬇ XLSX · 5 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.