Table.FillDown Function in Power Query M (4 Easy Examples)

If you have a column where a value appears once and the cells below it are blank, Table.FillDown copies that value down into the empty cells. In this article I’ll show you how to use it with simple, practical examples.

Table of Contents

Syntax of Table.FillDown Function

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

where

table (required, table). The table you want to fill values down in.
columns (required, list). The names of the columns to fill, given as a list of text values, like {"Region"}.

Returns: a table where each null cell in the chosen columns is replaced by the nearest non-null value above it.

In plain terms, it walks down each listed column and carries the last real value into every blank cell beneath it.

Example 1: Fill down a single column

Say you have a list of sales reps where the Region is only entered on the first row of each group.

The blank cells below it really mean “same region as above”, so you want to fill them in.

Here is the starting data:

Region	Sales Rep
West	Aarav Mehta
	Diego Ramirez
	Karen Foster
East	Priya Nair
	Brandon Lee

Fill the Region column down:

let
    Source = Excel.CurrentWorkbook(){[Name="Example1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Region"})
in
    #"Filled Down"

Each blank Region picks up the value from the row above it.

The result fills every gap:

Region	Sales Rep
West	Aarav Mehta
West	Diego Ramirez
West	Karen Foster
East	Priya Nair
East	Brandon Lee

Example 2: Fill down more than one column at once

You can list several columns and fill them all in a single step.

Here both Region and Manager are entered once per group, with blanks underneath.

Here is the starting data:

Region	Manager	Order
North	Sandeep Roy	ORD-101
		ORD-102
		ORD-103
South	Megan Clark	ORD-104
		ORD-105

Pass both column names in the list:

let
    Source = Excel.CurrentWorkbook(){[Name="Example2"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Region","Manager"})
in
    #"Filled Down"

Each column fills independently, top to bottom.

The result fills both columns:

Region	Manager	Order
North	Sandeep Roy	ORD-101
North	Sandeep Roy	ORD-102
North	Sandeep Roy	ORD-103
South	Megan Clark	ORD-104
South	Megan Clark	ORD-105

Example 3: A leading blank stays empty

Table.FillDown can only copy a value from above, so a null with nothing above it has nothing to pull from.

Here the first Quarter cell is blank before any real value appears.

Here is the starting data:

Quarter	Product
	Notebook
Q1	Stapler
	Marker
Q2	Folder

Fill the Quarter column down:

let
    Source = Excel.CurrentWorkbook(){[Name="Example3"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Quarter"})
in
    #"Filled Down"

The blank under Q1 fills, but the top blank has no value above it.

The result leaves the first cell empty:

Quarter	Product
	Notebook
Q1	Stapler
Q1	Marker
Q2	Folder

The top row stays blank because there is no earlier value to carry down.

Example 4: Fill grouped labels before further analysis

You often get this when cleaning up a report where a category label only prints once per block.

Here the Department is listed once, and you want it on every row so you can group or filter by it later.

Here is the starting data:

Department	Employee	Hours
Marketing	Olivia Grant	38
	Rohit Sharma	41
	Jason Webb	36
Finance	Neha Kapoor	40
	Tyler Brooks	39
	Emily Carter	42

Fill the Department column down:

let
    Source = Excel.CurrentWorkbook(){[Name="Example4"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Department"})
in
    #"Filled Down"

Every employee row now carries its department name.

The result tags each row with its department:

Department	Employee	Hours
Marketing	Olivia Grant	38
Marketing	Rohit Sharma	41
Marketing	Jason Webb	36
Finance	Neha Kapoor	40
Finance	Tyler Brooks	39
Finance	Emily Carter	42

With the column filled, every row stands on its own and is ready to group or filter. From here you can also select the columns you want to keep for the final report.

Things to keep in mind with Table.FillDown

Only null cells get filled, never blank text. A cell holding an empty string "" counts as a value, so it stops the fill and is not replaced. Convert empty strings to null first if you want them filled.
Fill before you trim. If your goal is to drop empty rows after cleaning, remove null values in Power Query once the real labels are in place, not before.
A leading null stays null. With no value above it, the function has nothing to carry down (Example 3). Use Table.FillUp if the value to copy sits below instead.
Column names are case-sensitive and must match exactly. A wrong name throws Expression.Error: The column 'Regoin' of the table wasn't found. Check spelling and casing against the headers.
The list takes text, not column references. Write {"Region"}, not {[Region]}. The second form throws an error because the argument is a list of column names.
Row order drives the result. Fill happens in the table’s current order, so sort the rows the way you want before filling, since a later sort will not re-fill anything.

Common questions about Table.FillDown

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

Table.FillDown copies values downward into the blanks below, while Table.FillUp copies them upward into the blanks above. Use whichever matches where the real value sits.

Can Table.FillDown replace blank cells with a fixed value instead?

No, it only copies the nearest value from above. To put a set value into blanks, use Table.ReplaceValue with null as the value to find. If you are building the table from scratch, you can also avoid the gaps by handling missing fields when you create the table from records.

List of All Power Query Functions

Related Power Query Functions / Articles: