Table.FillUp Function in Power Query M (5 Easy Examples)

Table.FillUp fills empty cells in the chosen columns with the next non-null value found below them. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you have a column with gaps and you want each blank to borrow the value sitting beneath it, this is the function you reach for. It is the bottom-up partner to Table.FillDown.

Table of Contents

Syntax of Table.FillUp Function

Table.FillUp(table as table, columns as list) as table

where

table (required, table). The table you want to fill.
columns (required, list). A list of column names to fill, for example {"Region"} or {"Region","Revenue"}. Only these columns are touched.

Returns: a table where each null in the listed columns is replaced by the first non-null value below it. A null with nothing non-null below it stays null.

In plain terms, it scans each listed column from the bottom up and pushes values upward into the empty cells above them.

Example 1: Fill a single column upward

You have a warehouse stock list where one bin count is missing.

You want that gap filled with the count from the bin below it.

Here is the starting data:

Bin	Pallets
A1	4
A2
A3	7

Fill the Pallets column upward:

let
    Source = Excel.CurrentWorkbook(){[Name="WarehouseStock"]}[Content],
    Result = Table.FillUp(Source,{"Pallets"})
in
    Result

The empty A2 cell takes the 7 from A3 below it.

The result:

Bin	Pallets
A1	4
A2	7
A3	7

A1 already had a value, so it is untouched.

Example 2: Propagate one value across several blank rows

A shift log records who supervised each slot, but only when the supervisor changes.

You want every blank slot to show the next named supervisor below it.

Here is the starting data:

Slot	Supervisor
Opening	Maria
Mid-morning
Midday
Afternoon	Devon

Fill the Supervisor column upward:

let
    Source = Excel.CurrentWorkbook(){[Name="ShiftLog"]}[Content],
    Result = Table.FillUp(Source,{"Supervisor"})
in
    Result

Both blank rows look down, find Devon, and take that value.

The result:

Slot	Supervisor
Opening	Maria
Mid-morning	Devon
Midday	Devon
Afternoon	Devon

One value can jump up several rows at once to fill a whole run of blanks.

Example 3: Fill several columns at once

A pivot-table export left gaps in both the Region and Revenue columns.

You want to fill both columns upward in one step.

Here is the starting data:

Quarter	Region	Revenue
Q1		500
Q1	North
Q2	South	640
Q2		720

Pass both column names in the list:

let
    Source = Excel.CurrentWorkbook(){[Name="PivotExport"]}[Content],
    Result = Table.FillUp(Source,{"Region","Revenue"})
in
    Result

Each listed column is filled independently from the bottom up.

The result:

Quarter	Region	Revenue
Q1	North	500
Q1	North	640
Q2	South	640
Q2		720

The last Region cell stays blank because there is no value below it to pull up.

Example 4: A trailing blank has nothing below it

Daily meter readings have two gaps, including one on the very last row.

You want to see how Table.FillUp treats a blank with no value beneath it.

Here is the starting data:

Day	Reading
Mon	12
Tue
Wed	18
Thu

Fill the Reading column upward:

let
    Source = Excel.CurrentWorkbook(){[Name="MeterReadings"]}[Content],
    Result = Table.FillUp(Source,{"Reading"})
in
    Result

Tue borrows 18 from Wed, but Thu is the last row with nothing below it.

The result:

Day	Reading
Mon	12
Tue	18
Wed	18
Thu

The trailing Thu cell stays empty. Table.FillUp never wraps around to the top.

Example 5: Count the blanks left after filling

A course roster has several seats with no instructor assigned yet.

After filling upward, you want to count the rows that still have no instructor (the ones with nothing below them).

Here is the starting data:

Seat	Instructor
1
2
3	Lena
4

Fill, then count the remaining null cells:

let
    Source = Excel.CurrentWorkbook(){[Name="CourseRoster"]}[Content],
    Filled = Table.FillUp(Source,{"Instructor"}),
    Result = Table.RowCount(Table.SelectRows(Filled,each [Instructor] = null))
in
    Result

Result: 1

Seats 1 and 2 pull Lena up from seat 3, but seat 4 is the last row, so it stays null.

Things to keep in mind with Table.FillUp

It only fills genuine null cells, not blank text. An empty string "" or a space is a real value and gets skipped. Replace blanks with null first using Table.ReplaceValue or Replace Values with the “blank to null” option.
Asking for a missing column is different from a blank cell. A blank cell is a null value in an existing column, which is what Table.FillUp works on. To add a column that is missing entirely as null, see Table.SelectColumns.
A trailing null at the bottom stays null. There is nothing below it to pull up (Examples 3 and 4). Use Table.FillDown if a leading blank at the top is your problem instead.
Each column in the list fills on its own. A blank in Region looks only at Region values below it, never at another column.
A wrong column name throws. Expression.Error: The column 'Regoin' of the table wasn't found. Check the name and spelling against the actual headers.
The fill is row-order dependent. It uses the table’s current order, so sort the table the way you want before filling, since Table.FillUp has no awareness of groups.

Common questions about Table.FillUp

What is the difference between Table.FillUp and Table.FillDown?

Table.FillUp pulls values upward from the cell below, so blanks borrow from beneath them. Table.FillDown pushes values downward from the cell above, which is the more common need after expanding a grouped or merged column.

Can Table.FillUp fill only within groups, not across the whole column?

Not on its own. It fills straight up the entire column. To stop the fill from crossing group boundaries, group the table first with Table.Group, fill inside each group, then combine the results.

List of All Power Query Functions

Related Power Query Functions / Articles: