Table.FillDown

How Table.FillDown Works

Table.FillDown is a function that can be applied to any column in a table in Power Query. Its purpose is to populate null or empty cells in that column with the last non-null value in that column. This can be useful when analyzing data that has gaps or missing values, and you want to fill those gaps with data from the most recent non-null value.

For example, let’s say you have a table with a column that contains the following values:

If you apply the Table.FillDown function to this column, it will fill in the null values with the last non-null value in that column. The resulting column will look like this:

The M Code Behind Table.FillDown

The M code behind Table.FillDown is relatively simple. It uses a function called List.Accumulate to iterate over each row in the column and fill in the null values with the last non-null value. Here’s the M code:

Let’s break this code down:

– Table: This is the name of the table that you want to apply the FillDown function to. In this case, it’s the entire table.

– {“Column A”}: This is the name of the column that you want to apply the FillDown function to. You can specify multiple columns here if you want to apply the function to more than one column.

The List.Accumulate function is used to iterate over each row in the specified column and fill in the null values with the last non-null value. Here’s the code that Table.FillDown uses internally to accomplish this:

Let’s break this code down as well:

– List.Positions(Column): This creates a list of the positions of each row in the column.

– null: This is the initial value of the state variable.

– (state, current) => … : This is a lambda function that takes two arguments, state and current. The state variable keeps track of the last non-null value encountered, and the current variable represents the current row in the column.

– if Column{current} <> null then Column{current}: This checks if the current row is not null. If it’s not null, it sets the state variable to the value of the current row.

– else state: If the current row is null, it sets the state variable to the last non-null value encountered.

Table.FillDown then applies this function to each row in the column, filling in the null values with the last non-null value.

The Table.FillDown function in Power Query is a powerful tool for filling in null or empty values in a column with the last non-null value. It uses a relatively simple M code that iterates over each row in the column and fills in the null values with the last non-null value using the List.Accumulate function. Understanding the M code behind Table.FillDown can help you use it more effectively and troubleshoot any issues that may arise.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us