Power QueryExercises

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
    Ordered

Result

SectionItemQty
FruitApples4
FruitPears2
VegetablesLeeks7

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

  1. Flag: a conditional column copies the item name only on heading rows; every other row gets null.
  2. Fill: Fill Down spreads each heading over the rows below it.
  3. Filter: the heading rows have served their purpose, so remove them.
  4. 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
    Detail

Result

ItemQtyCostSection
Apples41.2Fruit
Quincesnull3.5Fruit
Leeks70.8Vegetables

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Quinces has no quantity but a cost, so it's correctly kept as a detail row.

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