Table.FillUp

Table.FillUp is a Power Query M function that propagates the value of the next cell to null-valued cells above in specified columns. The function returns a new table with filled up values.

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

Syntax

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

Description

The Table.FillUp function fills in missing (null) values in a column by searching from the bottom of the table upwards. Whenever it encounters a null value, it replaces it with the first non-null value found below it, even if that value is several rows down.

Examples

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

Imagine we’ve imported data from an Excel Pivot Table. The data contains categories and subcategories, but only the first row in each category group has the category name. The rest of the rows for that category are blank (null). When this data comes into Power Query, it looks like this:

As you can see, only the first occurrence of each category is filled, while the rest are left as null. This makes the data harder to read and work with.

Using Table.FillUp to Fill in the Gaps

To fix this, we can use the Table.FillUp function, which takes the value from below and fills it upwards into the empty (null) cells. In Power Query, you can do this by right-clicking on the Category column, selecting Fill, and then choosing Up. This operation will fill in those missing category names.

Here’s what the table looks like after applying the Table.FillUp function:

Now, all the null values in the Category column are replaced with the correct values from the rows below. It generates the follow code:

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

Applying Table.FillUp on Multiple Columns

If you want to fill up missing values in more than one column, you can expand the second argument of the Table.FillUp function to include multiple columns. For instance, the following code applies the fill up operation on both the Category and Subcategory:

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

This is just a theoretical example, since the Subcategory column in our example does not contain any null values.

Here’s the full Power Query M code used in this example:

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

Related functions

Other functions related to Table.FillUp are:

• Table.FillDown

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