Fill up, and gaps that aren't null
Fill labels in either direction and make sure the gaps are real nulls.
~8 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
Not every report puts the label first. Some systems print the detail lines and then a closing line that names the group, such as an invoice number at the end of its lines. Fill Up copies each non-null value upwards into the nulls above it.
let
Lines = #table({"Invoice", "Item", "Qty"}, {
{null, "Kite", 3},
{null, "Reel", 2},
{"INV-201", null, null},
{null, "Line", 4},
{"INV-202", null, null}
}),
Filled = Table.FillUp(Lines, {"Invoice"}),
Detail = Table.SelectRows(Filled, each [Item] <> null)
in
DetailResult
| Invoice | Item | Qty |
|---|---|---|
| INV-201 | Kite | 3 |
| INV-201 | Reel | 2 |
| INV-202 | Line | 4 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
When the gaps aren't null
Both fills replace null only. A CSV export or a sheet with formulas often leaves empty text instead, and then nothing fills:
Table.FillDown(#table({"Region"}, {{"North"}, {""}, {null}}), {"Region"})Result
| Region |
|---|
| North |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Convert empty text to null first. Replace Values with an empty search and null as the replacement does it in one step:
let
Source = #table({"Region", "Store"}, {{"North", "Leeds"}, {"", "York"}, {"South", "Bath"}, {"", "Wells"}}),
Nulls = Table.ReplaceValue(Source, "", null, Replacer.ReplaceValue, {"Region"}),
Filled = Table.FillDown(Nulls, {"Region"})
in
FilledResult
| Region | Store |
|---|---|
| North | Leeds |
| North | York |
| South | Bath |
| South | Wells |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Whitespace
A cell containing a space is neither null nor empty. Trim the column first, then replace empty text with null, then fill. Three small steps, each with an obvious purpose, are easier to review than one clever expression.
Check the result by counting distinct labels after filling. If a report has three invoices, the filled column should contain exactly three distinct invoice numbers and no nulls. Any null left over means a block had no label at all, which the report designer should hear about.
Which direction?
- Label printed before its rows: Fill Down.
- Label printed after its rows: Fill Up.
- Label on both a header and a footer line: fill from the header, and remove both marker lines by content.
Where to find this in Excel
- Excel for Windows
- Transform > Fill > Up (use Transform > Replace Values first to turn blanks into null)
- What you see
- Fill only replaces null, so blank text cells must become null before Fill Up changes them.
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
- Running Fill Down on a column whose gaps are empty text, and nothing changes.
- Choosing Fill Down for a report whose labels come after the detail, so each block gets the previous block's label.
- Forgetting to remove the marker rows after filling.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: right-click a column → Fill → Up.
- In Replace Values, leaving Value To Find empty searches for empty text; typing
nullin Replace With writes a real null. - The simulator behaves like the native functions: only null is filled.
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