Turn section headings into a column
Move headings that are mixed into the data into their own column.
~9 min read · intermediate · Module 10: Fill down and report layouts
By Tahir Nas · Examples run in our simulator · Updated
Before this: Fill down and tidy report layouts, Conditional columns with if … then … else
Sometimes the group label isn't in a column of its own. A stock sheet lists Fruit, then the fruit items, then Vegetables, then the vegetables, all in the same Item column. A heading row is recognisable only by its shape: it has a name but no quantity.
Flag, copy, fill, filter
let
Sheet = #table({"Item", "Qty"}, {
{"Fruit", null},
{"Apples", 4},
{"Pears", 2},
{"Vegetables", null},
{"Leeks", 7}
}),
Heading = Table.AddColumn(Sheet, "Section", each if [Qty] = null then [Item] else null, type text),
Filled = Table.FillDown(Heading, {"Section"}),
Detail = Table.SelectRows(Filled, each [Qty] <> null),
Ordered = Table.ReorderColumns(Detail, {"Section", "Item", "Qty"})
in
OrderedResult
| Section | Item | Qty |
|---|---|---|
| Fruit | Apples | 4 |
| Fruit | Pears | 2 |
| Vegetables | Leeks | 7 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
- Flag: a conditional column copies the item name only on heading rows; every other row gets null.
- Fill: Fill Down spreads each heading over the rows below it.
- Filter: the heading rows have served their purpose, so remove them.
- Tidy: put the new column first.
Choosing the heading test
The test must be true for headings and false for every detail row, including unusual ones. 'Qty is null' fails if a real item can have an unknown quantity. Stronger tests combine several signals:
- The value is in upper case or ends with a colon.
- Several numeric columns are all null.
- The value matches a known list of section names (List.Contains).
let
Sheet = #table({"Item", "Qty", "Cost"}, {
{"FRUIT", null, null},
{"Apples", 4, 1.2},
{"Quinces", null, 3.5},
{"VEGETABLES", null, null},
{"Leeks", 7, 0.8}
}),
Heading = Table.AddColumn(Sheet, "Section", each if [Qty] = null and [Cost] = null and Text.Upper([Item]) = [Item] then Text.Proper([Item]) else null, type text),
Filled = Table.FillDown(Heading, {"Section"}),
Detail = Table.SelectRows(Filled, each [Section] <> Text.Proper([Item]))
in
DetailResult
| Item | Qty | Cost | Section |
|---|---|---|---|
| Apples | 4 | 1.2 | Fruit |
| Quinces | null | 3.5 | Fruit |
| Leeks | 7 | 0.8 | Vegetables |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
This pattern also works when headings are in a different column from the detail values: test that column instead. What matters is a reliable signal that distinguishes a heading row from a detail row.
Where to find this in Excel
- Excel for Windows
- Add Column > Conditional Column, then right-click the new column > Fill > Down
- What you see
- The conditional column picks out the heading rows and Fill Down copies each heading to the rows below it.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Add a conditional column (Microsoft Learn); Fill values in a column (Microsoft Learn).
Common mistakes
- A heading test that also matches detail rows with missing values.
- Filtering out heading rows before filling, so there's nothing to fill from.
- Leaving the heading rows in, so every section has an extra row with no quantity.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Add Column → Conditional Column builds the flag; Fill → Down and a filter finish the job, all without typing M.
- Excel sheets sometimes mark headings with bold formatting only. Formatting isn't visible to Power Query, so the test must use values.
- The simulator runs this pattern exactly as shown.
Next 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