Table.FillDown

Table.FillDown is a Power Query M function that propagates the value of a previous cell to null-valued cells below in specified columns. The function returns a new table with filled down values.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

The Table.FillDown function starts at the top of a column and scans downward. Whenever it encounters a null value, it replaces it with the first non-null value found above it.

Examples

Let’s take a look at a simple example where we use the Table.FillDown function.

Imagine you’ve imported data from an Excel Pivot Table into Power Query. Your dataset includes categories and subcategories, but there’s a catch: only the first row of each category group has the category name. The remaining rows under the same category are left blank (null). Here’s how your data might look:

As you can see, the Category column is missing values in rows where it should repeat the category name. This makes the data harder to work with.

Filling in the Gaps with Table.FillDown

To fix this issue, we’ll use the Table.FillDown function in Power Query. This function takes the value from the first non-null cell in a column and fills it down into the null cells below. Here’s how you can apply it:

1. Select the Column: In Power Query, right-click on the Category column to open a menu.
2. Fill Down: Hover over Fill, and then click on Down.

This action will fill the null cells in the Category column with the category name from the row above. Your updated table will look like this:

Now, the Category column is complete filled with values, making the data more readable and easier to analyze. It creates the following code:

Table.FillDown( Source, { "Category" } )

Filling Down Multiple Columns

If you have more than one column with missing values, you can apply Table.FillDown to multiple columns simultaneously. To do that, you simply adjust the function to include all the columns you want to fill down. For example:

Table.FillDown( Source, { "Category", "Subcategory" } )

In this example, we’re specifying both the Category and Subcategory columns. If the Subcategory column also had missing values, this function would fill them down just like it did for the Category column.

Complete Power Query M Code Example

For those interested in the whole picture, here’s the complete M code used in this example:

let
  Source = 
    #table(
  type table [Category = text, Subcategory = text, Sales = Int64.Type], 
  {
    { "Electronics", "Phones",           1200 }, 
    { null,          "Laptops",          1500 }, 
    { null,          "Tablets",          800  }, 
    { "Appliances",  "Washing Machines", 300  }, 
    { null,          "Refrigerators",    450  }, 
    { "Furniture",   "Chairs",           200  }, 
    { null,          "Tables",           600  }
  }
),
    FilledDown = Table.FillDown( Source, { "Category" } )
in
    FilledDown

Related functions

Other functions related to Table.FillDown are:

• Table.FillUp

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-filldown