Combine two header rows into column names
Build clean column names from a multi-row header without typing them.
~11 min read · advanced · Module 10: Fill down and report layouts
By Tahir Nas · Examples run in our simulator · Updated
Before this: Fill down and tidy report layouts, Combine text values and handle missing parts
Management reports love two-row headers: the year spans several columns in the first row, and the quarters sit underneath. Promote Headers can only use one row, so you'd get columns named 2025, Column3, 2026, Column5. Build the names yourself instead, from the data, so a new year next time needs no edit.
The shape
| Column1 | Column2 | Column3 | Column4 | Column5 |
|---|---|---|---|---|
| null | 2025 | null | 2026 | null |
| Region | Q1 | Q2 | Q1 | Q2 |
| North | 10 | 12 | 11 | 15 |
| South | 8 | 9 | 7 | 10 |
Build names from the header rows
Take the two header rows and transpose them, so each original column becomes a row with its year and quarter side by side. Fill the year down to cover its quarters, then join year and quarter with Text.Combine, which skips the missing year above Region:
let
Source = #table({"Column1", "Column2", "Column3", "Column4", "Column5"}, {
{null, "2025", null, "2026", null},
{"Region", "Q1", "Q2", "Q1", "Q2"},
{"North", 10, 12, 11, 15},
{"South", 8, 9, 7, 10}
}),
HeaderRows = Table.Transpose(Table.FirstN(Source, 2)),
YearsFilled = Table.FillDown(HeaderRows, {"Column1"}),
Names = List.Transform(Table.ToRows(YearsFilled), each Text.Combine(_, " ")),
Data = Table.Skip(Source, 2),
Renamed = Table.RenameColumns(Data, List.Zip({Table.ColumnNames(Data), Names}))
in
RenamedResult
| Region | 2025 Q1 | 2025 Q2 | 2026 Q1 | 2026 Q2 |
|---|---|---|---|---|
| North | 10 | 12 | 11 | 15 |
| South | 8 | 9 | 7 | 10 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
List.Zip pairs each current name with its new name, which is exactly the {{old, new}, …} list Table.RenameColumns expects. Because every name is computed, a 2027 block in next year's report gets named correctly with no change to the query.
Then go long
Wide year/quarter columns are still awkward to analyse. Unpivot everything except Region and split the attribute back into Year and Quarter:
let
Wide = #table({"Region", "2025 Q1", "2025 Q2", "2026 Q1"}, {{"North", 10, 12, 11}}),
Long = Table.UnpivotOtherColumns(Wide, {"Region"}, "Period", "Sales"),
Split = Table.SplitColumn(Long, "Period", Splitter.SplitTextByDelimiter(" "), {"Year", "Quarter"})
in
SplitResult
| Region | Year | Quarter | Sales |
|---|---|---|---|
| North | 2025 | Q1 | 10 |
| North | 2025 | Q2 | 12 |
| North | 2026 | Q1 | 11 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Where to find this in Excel
Home > Use First Row as Headers promotes one row only. Joining two header rows into one name is written in M in the Advanced Editor (View > Advanced Editor).
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Promote or demote column headers (Microsoft Learn); Use Power Query to transform data (the Power Query editor) (Microsoft Learn).
Common mistakes
- Promoting the first header row and renaming the rest by hand, so next year's columns break the query.
- Forgetting to fill the year across its quarters, producing names like "Q2" without a year.
- Transposing the full table instead of just the header rows.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Transform → Transpose and Fill → Down are menu commands; building the rename list from the header rows needs a small custom step in the formula bar.
- Merged cells in an Excel header arrive as one value followed by nulls, which is exactly the pattern Fill Down repairs.
- The simulator supports Table.Transpose, Table.ToRows and List.Zip as used here.
Table.TransposeTable.FillDownTable.ToRowsTable.RenameColumnsList.ZipNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Fill down and report layouts quiz