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
DetailResult
| Region | Store | Sales |
|---|---|---|
| North | Leeds | 120 |
| North | York | 80 |
| South | Bath | 95 |
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.
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.
- beginner~6 minPro
Fill region labels down a printed report (retail)
The region is printed once per block. Fill it down so every store row has its region.
- beginner~6 minPro
Fill region labels down a printed report (franchise)
The region is printed once per block. Fill it down so every store row has its region.
- intermediate~8 minPro
Turn a report with subtotals into a clean table (retail)
Fill regions down and remove the Subtotal and Grand Total rows so only store rows remain.
- intermediate~8 minPro
Turn a report with subtotals into a clean table (franchise)
Fill regions down and remove the Subtotal and Grand Total rows so only store rows remain.
- beginner~6 minPro
Fill batch labels up a production sheet (bakery)
The batch number is printed on the last line of each batch block. Fill it upward so every line has its batch.
- beginner~6 minPro
Fill batch labels up a production sheet (brewery)
The batch number is printed on the last line of each batch block. Fill it upward so every line has its batch.
- intermediate~7 minPro
Fill down region and store labels together (retail)
The report prints Region once per region block and Store once per store block. Fill both down.
- intermediate~7 minPro
Fill down region and store labels together (pharmacies)
The report prints Region once per region block and Store once per store block. Fill both down.
- intermediate~7 minPro
Fill down labels whose blanks are empty text (volunteers)
Fill Down does nothing on this roster because the blank cells contain empty text, not null. Fix that first.
- intermediate~7 minPro
Fill down labels whose blanks are empty text (shifts)
Fill Down does nothing on this roster because the blank cells contain empty text, not null. Fix that first.
- intermediate~8 minPro
Turn a sideways report the right way round (new starters)
The report lists fields down the first column and one person per column. Make it one row per person.
- intermediate~8 minPro
Turn a sideways report the right way round (panel)
The report lists fields down the first column and one person per column. Make it one row per person.
- advanced~14 minPro
Combine two header rows into one (franchise)
A sheet has years on the first row (printed once, over merged cells) and quarters on the second. Build headers like "2026 Q1".
- advanced~14 minPro
Combine two header rows into one (charity shops)
A sheet has years on the first row (printed once, over merged cells) and quarters on the second. Build headers like "2026 Q1".
- intermediate~8 minPro
Predict which rows Fill Down will change (stock take)
Before filling Group down, list the rows that will receive a value, and leave out rows Fill Down can't fill.
- intermediate~8 minPro
Predict which rows Fill Down will change (menu)
Before filling Group down, list the rows that will receive a value, and leave out rows Fill Down can't fill.