Exported reports often have a category label in the first row of a group. Blank cells appear below it for all rows belonging to that category. This reads well in a formatted report. However, it breaks every formula and PivotTable that relies on each row being self-contained. Fill Down in Power Query solves this instantly. It replaces every null with the last non-null value above it. The label propagates through all rows in each group. Fill Up does the reverse.
This guide covers Fill Down and Fill Up in full, including when each applies, multiple-column fills, and six worked examples. It also covers unwanted fills at group boundaries. These include classic category-label propagation, fixing merged-cell exports, and building running totals using Fill Down on a cumulative column.
What Do Fill Down and Fill Up Do?
Fill Down scans top to bottom. Specifically, when it finds a null, it copies the last non-null value from above. It continues until it reaches another non-null value or the end of the column. This propagates category labels, department names, or grouping values through all rows in each group.
Fill Up works in the opposite direction. It scans bottom to top and copies the next non-null value below into null cells above it. This is less common but useful when a label appears at the bottom of each group rather than the top. Both operations only affect null values. Non-null cells are never changed.
| Operation | Direction | Typical source | Result |
|---|---|---|---|
| Fill Down | Top to bottom | Category in first row, nulls below | Category repeated in every row of the group |
| Fill Up | Bottom to top | Label in last row, nulls above | Label repeated in every row above the group footer |
How to Apply Fill Down in Power Query
Examples 1–4: Fill Down and Fill Up in Practice
A report exported from an accounting system has the Department name in the first row of each department group, with null cells below for all other rows in the group. Fill Down on the Department column makes every row self-contained and PivotTable-ready.
Excel reports with merged cells export as a single value in the first cell of each merged region, with null in all remaining cells. Power Query un-merges automatically on import — but the null cells remain. Fill Down on those columns restores the full data. This also applies to PDF or web table imports where merged table headers create the same null pattern.
Some reports place a category label at the bottom of each group instead of the top. An expense report might list all transactions first, then a "Category: Travel" label at the end of each group. Fill Up propagates this bottom label upward through the null cells above it.
Fill Down fills every null, including into the next group if there is no boundary marker. This causes the last value in one group to bleed into rows that belong to the next group. The fix is to add a sentinel value at group boundaries before filling — typically by using a Conditional Column to mark summary or subtotal rows and then filtering them out after the fill.
Examples 5–6: Fill Down with Calculations and Multiple Columns
A complex export may have several columns using the label-in-first-row pattern. Ctrl-clicking all affected headers fills them all in one step rather than repeating the operation column by column.
A bank transaction table has an Opening Balance in row 1 and null in all subsequent Balance rows. Adding each transaction amount to the prior balance is a running total calculation. In Power Query, this requires a recursive approach — or more practically, adding the cumulative sum as a list operation and then expanding it as a column.
Common Issues and How to Fix Them
Fill Down fills across group boundaries incorrectly
If Fill Down crosses into the next group, the groups lack a non-null boundary between them. Fill Down only stops when it encounters another non-null value. Ensure each group starts with a non-null category value, not just the first group. Remove rows that are entirely null in the fill column before applying Fill Down.
Fill Down is not working — blank cells are not being filled
Power Query Fill Down replaces null values only, not empty strings. If the source cells appear blank but are actually empty strings (""), Fill Down will skip them. Add a Replace Values step first: go to Transform → Replace Values, replace "" (empty string, leave the value-to-find box empty) with null. After that, Fill Down will treat those cells as fillable nulls.
Frequently Asked Questions
-
How do I fill down in Power Query?+Click the column header you want to fill. Go to Transform → Fill → Down. Every null cell in the column is replaced with the last non-null value above it. Non-null cells are unchanged. You can also Ctrl-click multiple column headers before applying Fill Down to fill all selected columns simultaneously. The M function is Table.FillDown(table, {"ColumnName"}).
-
What is the difference between Fill Down and Fill Up?+Fill Down scans top to bottom and replaces each null with the last non-null value seen above it. Fill Up scans bottom to top and replaces each null with the next non-null value below it. Fill Down is used when category labels appear at the top of each group. Fill Up is used when labels appear at the bottom of each group (for example, subtotal rows or footer labels). Both operations only affect null cells.
-
Why is Fill Down not filling my blank cells?+Fill Down only replaces null values. If your blank-looking cells contain empty strings ("") instead of null, Fill Down skips them. Fix this by adding a Replace Values step before Fill Down: Transform → Replace Values → leave "Value To Find" empty, set "Replace With" to null. After this step, the empty strings become true nulls and Fill Down works correctly.
-
How do I prevent Fill Down from crossing into the next group?+Fill Down stops when it reaches a non-null value. If your data has separator or subtotal rows that are completely null in the fill column, those rows cause Fill Down to continue into the next group. Remove blank separator rows before applying Fill Down (Home → Remove Rows → Remove Blank Rows). Alternatively, ensure the first row of every group has the correct non-null category value so Fill Down has a boundary to stop at.