Question

Filling Missing Values with the Previous Available Value in Power Query
You have a dataset where missing values are represented as nulls. You need to fill the missing values with the last available value in the column. What is the best approach in Power Query to achieve this efficiently?

Answer 1

This guide for filling missing values with the last available value in Power Query explains as follows:

1. Open Power Query Editor.

Select the dataset that contains null values.

Identify the column where missing values (nulls) are to be filled.

2. Sort the Data (if needed)

If the fill criterion requires the data to be in a certain order, first sort by Date, ID, or any other relevant field to be logically ordered.

3. Fill Down (best in more direct cases)

Select the column containing nulls.

Select the Transform Tab → Fill Down.

This will replace each null with the non-null value above it.

4. Using M Code for Advanced Control

If you need more flexibility or want to automate the process, use M code:

= Table.FillDown(Source, {"ColumnName"})

• Replace "ColumnName" with the actual column name.
• This efficiently fills nulls with the last known value.

5. Handling Edge Cases

• Leading Nulls (first row is null): These will remain null unless explicitly replaced

= Table.ReplaceValue(FilledDown, null, "DefaultValue", Replacer.ReplaceValue, {"ColumnName"})

• Multiple Columns: Use Table.FillDown(Source, {"Col1", "Col2"}) to apply fill-down on multiple columns simultaneously.