How to replace null values in Power Query (5 Cases)

Do you have null values in your Power Query table that you want to find and replace with more useful data?

In this article we go through how to:

These methods will show you how to find and replace nulls without having to add a custom column, which can add unnecessary complications to your data.

Replace null with 0

If you need to replace null values with 0 in Power Query, this is how.

Here is an example table of numbers containing null values:

To replace the null values with 0, right click on the column and click ‘Replace Values’ as shown below.

Then enter the value to find as null, and the value to replace as 0.

Once this has been applied, the null values will be replaced with zeros, as below.

This is the Power Query M code for replacing nulls with zeros in my table:

= Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Sample 1"})

Rather than following the steps above, you can create a new step and add this code. However, make sure that you edit the following so it works for your table:

• #”Changed Type” to whatever the previous step in your query is, i.e. #”Source”
• {“Sample 1”} to whatever the column header is in your query

Click here to find out how to replace values in multiple columns at once like this example.

Replace null with another column

If you need to replace nulls in a column with values from another column in Power Query, this is how.

Here is an example of a table with two columns.

The columns ‘Sample 2’ contains null values that we want to replace with the adjacent values in column ‘Sample 1’.

Firstly, right click on the column you want to find and replace nulls in, then click ‘Replace Values’.

Add in two placeholder values for now, for example, enter the value to find as null and enter the value to replace as 0.

Once you have done this, your null values should be replaced, as below.

Here is the M code for this step in my table:

= Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Sample 2"})

Now, we need to edit the M code to bring the values in from the first column.

To do this, replace the ‘value to replace’ part of the formula (in this case ‘0’) with ‘each [name of column to get values from] (in this case ‘Sample 1’).

Therefore, in this example we would have the following M code:

= Table.ReplaceValue(#"Changed Type",null,each [Sample 1],Replacer.ReplaceValue,{"Sample 2"})

This is the result:

You can also replace nulls with values from another column by creating a custom column and using a conditional statement.

We have also written an article on how to replace nulls or any value based on another column.

Replace null with today’s date

If you need to replace nulls in a column with today’s date in Power Query, this is how.

Here is an example of a table containing a date column with null values.

We want to find and replace these null values with today’s date.

Firstly, right click on the column you want to find and replace nulls in, then click ‘Replace Values’.

Add in two placeholder values for now. For example, enter the value to find as null and enter the value to replace as a random date i.e. 01/01/2022.

Once you have done this, your null values should be replaced, as below.

Here is the M code for this step in my table:

= Table.ReplaceValue(#"Changed Type",null,#date(2022, 1, 1),Replacer.ReplaceValue,{"Date"})

Now, we need to edit the M code to bring in today’s date.

To do this, replace the ‘value to replace’ part of the formula (in this case #date(2022, 1, 1)) with DateTime.LocalNow().

Therefore, in this example we would have the following M code:

= Table.ReplaceValue(#"Changed Type",null,DateTime.LocalNow(),Replacer.ReplaceValue,{"Date"})

This is the result:

Note: the date at the time of writing was 30th December 2022 and the date is in the format DD/MM/YYYY. The date will update in Power Query automatically as time passes.

Replace null with average

If you need to replace nulls in a column with the average of other values in the column, in Power Query, this is how.

Here is an example of a table containing a set of values and one null value.

We want to find and replace the null values with the average of the other values.

Firstly, right click on the column you want to find and replace nulls in, then click ‘Replace Values’.

Add in two placeholder values for now. For example, enter the value to find as null and enter the value to replace as 0.

Once you have done this, your null values should be replaced, as below.

Here is the M code for this step in my table:

= Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Sample"})

Now, we need to edit the M code to bring in the average value.

To do this, replace the ‘value to replace’ part of the formula (in this case 0) with List.Average(#”Previous Step”[Column Name] (in this case #”Changed Type”[Sample]).

Therefore, in this example we would have the following M code:

= Table.ReplaceValue(#"Changed Type",null,List.Average(#"Changed Type"[Sample]),Replacer.ReplaceValue,{"Sample"})

This is the result:

The average of the other values in the column in 3 and this will change depending on if any of the values change.

Replace null with value above

If you need to replace nulls in a column with the value directly above them, in Power Query, this is how.

Here is an example of a table containing a set of values with some null values.

We want to find and replace the null values with the values above them.

You need to right click on the column and select Fill, then Down.

If you wanted to replace null values with the values from below, then you would Fill Up instead.

This will then replace the null values with the next value from above.

Here is the result:

You can also pair this with custom columns if you have multiple columns you need to do this with.