Power Query Fill Down: Handle Nulls, Empty Text, and Group Boundaries

State what a blank label is allowed to inherit

This walkthrough targets the Windows desktop Power Query Editor in Microsoft 365 Excel. The BatchRows Table contains two independent batches. Within one batch, a blank label means continue the preceding label. At the start of a batch, a blank label means the source has not supplied one yet. These two situations look similar in a worksheet but cannot be filled with the same global operation.

row_no records the source sequence. It is part of the sample contract and must be unique here. Sorting alphabetically by label before filling would destroy the relationship between headings and detail lines. If your source has no row number, add an index while the source is still in its meaningful order and preserve it through later steps. Do not invent an order after records have already been rearranged.

Distinguish null from text that only looks empty

Table.FillDown acts on null-valued cells. Empty text and spaces can look blank but are text values, so a label that appears empty may stop the fill or remain unfilled. For this example, null, empty text, and whitespace-only text are all missing labels. Nonempty labels are kept exactly as provided, including meaningful internal spaces.

The CSV fixture uses empty fields and cannot guarantee which blank representation every import route supplies. Inspect the query before filling. The provided M normalizes the label field using the explicit rule above. The separate blank-states.txt file describes additional checks with actual M null, an empty string, a string of spaces, and zero. Those tokens are descriptions, not instructions to type the word null into every empty source cell.

Use the simple menu only for a single continuous group

If the entire source is one continuous report with no independent batch resets, the graphical workflow is short. Select only the categorical field that carries forward. Leave numeric amounts, row identifiers, and unrelated columns unselected. Filling every column would risk turning absent measurements into copies of the preceding measurement.

1. Create BatchRows from batch-rows-input.csv as an Excel Table with headers, then use Data > From Table/Range.
2. Set row_no to Whole Number and label to Text. Confirm the declared blank normalization has occurred before filling.
3. For a single-group test, filter batch to B1, sort row_no ascending, select label, and choose Transform > Fill > Down.
4. Verify row 1 remains missing, row 3 becomes North, and row 5 becomes South. The five B1 value cells must remain 5, 10, 20, 30, and 40.
5. For the full two-batch source, use the grouped query below instead of removing the B1 filter and filling globally.

Fill each batch independently with a complete query

This query groups by batch, sorts each group by row_no, fills only label, combines the resulting tables, and restores the overall row sequence. The final sort is explicit because a grouping operation should not be treated as an output-order guarantee. Load the result to a new worksheet and retain BatchRows as the source.

The batch identifier must describe the true reset boundary. If a report uses the same batch name for several unrelated blocks, add a unique block identifier first. Grouping all occurrences of an ambiguous name together could allow values to cross a boundary that the name does not capture. For the sample, B1 and B2 each occur in a single independent block, so batch is sufficient.

let
    Source = Excel.CurrentWorkbook(){[Name="BatchRows"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"row_no", Int64.Type}, {"batch", type text}, {"label", type text}, {"value", Int64.Type}}),
    Normalized = Table.TransformColumns(Typed, {{"label", each if _ = null or Text.Trim(_) = "" then null else _, type nullable text}}),
    Groups = Table.Group(Normalized, {"batch"}, {{"rows", each Table.FillDown(Table.Sort(_, {{"row_no", Order.Ascending}}), {"label"})}}),
    Combined = Table.Combine(Groups[rows]),
    Output = Table.Sort(Combined, {{"row_no", Order.Ascending}})
in
    Output

Compare the grouped result with the unsafe global result

A global fill would place South in row 6 because row 5 carries South and row 6 follows it in the complete table. That is incorrect under the sample contract: row 6 belongs to B2, whose first supplied label is East in row 7. The grouped result leaves row 6 unresolved. This difference will not show up in the numeric grand total, which remains 215 in both cases.

The zero in row 8 is a measurement, not a missing label. It stays zero while its label becomes East. Similarly, a literal label such as None could be a meaningful category; do not convert every unusual word to null because it looks like a placeholder. Agree on source-specific placeholder values separately and test the count of affected records.

Validate and maintain the transformation

Compare all eight output records with batch-rows-expected.csv. Confirm two unresolved labels, three newly filled labels, unchanged row identifiers, and unchanged value total 215. These checks serve different purposes: the unresolved count catches fabricated labels, row identifiers catch lost detail, and the total catches accidental edits to value.

On refresh, add a new B2 row 9 with a missing label and value 4. It should inherit East, producing nine rows and value total 219. Then add a new batch B3 row 10 with a missing label and value 6: that label must remain missing. If it inherits East, inspect whether the fill moved outside the grouping step or whether B3 was incorrectly assigned the previous batch ID.