Power QueryExercises

Fill down and tidy report layouts

Repair report-style exports so every row stands on its own.

~9 min read · beginner · Module 10: Fill down and report layouts

By Tahir Nas · Examples run in our simulator · Published · Updated

Before this: Filter rows by value

Reports designed for people print a group label once, add subtotal lines, and finish with a grand total. Analysis needs the opposite: every row carries its own labels, and there are no pre-calculated totals mixed in with the detail.

Fill Down

Fill Down replaces each null with the nearest non-null value above it. It turns 'label printed once per block' into 'label on every row':

let
    Report = #table({"Region", "Store", "Sales"}, {
        {"North", "Leeds", 120},
        {null, "York", 80},
        {null, "Subtotal", 200},
        {"South", "Bath", 95},
        {null, "Subtotal", 95},
        {null, "Grand Total", 295}
    }),
    Filled = Table.FillDown(Report, {"Region"}),
    Detail = Table.SelectRows(Filled, each [Store] <> "Subtotal" and [Store] <> "Grand Total")
in
    Detail

Result

RegionStoreSales
NorthLeeds120
NorthYork80
SouthBath95

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

Order is everything: fill while rows are still in report order, before any sort or filter. A sort first would scatter the nulls under the wrong labels.

Remove summary rows by content

Filtering by the marker text works however many groups there are. Removing the last N rows breaks as soon as the report grows a region. If the marker text varies ("Subtotal", "Sub-total", "Total North"), test with Text.StartsWith or Text.Contains on a lowercased copy.

Check the result against the report

The report's own grand total is a free check. Sum the detail rows you kept and compare with the total you removed: here 120 + 80 + 95 = 295. If they differ, you either dropped a detail row or kept a subtotal.

Where to find this in Excel

Excel for Windows
Right-click the column header > Fill > Down
What you see
Each null below a value takes that value, until the next value appears.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Fill values in a column (Microsoft Learn).

Common mistakes

  • Sorting or filtering before Fill Down, so labels land on the wrong rows.
  • Removing subtotal rows by position (last N rows) instead of by their text.
  • Keeping the export's subtotals and then summing them again with the detail.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: right-click a column → Fill → Down / Up writes Table.FillDown / Table.FillUp.
  • Excel exports from PivotTables often use this layout; turning on Repeat All Item Labels in the PivotTable before exporting avoids the problem at source.
  • The simulator's Table.FillDown fills only null, like the native function.
Functions in this lesson:Table.FillDownTable.SelectRows

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.

Practise: Fill region labels down a printed report (retail)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.