Power Query – Handling Null Values

This post is about handling null values in Power Query calculations. If you do a calculation in Power Query that involves a null value the answer returned is null. In the example below we get nulls for the first three rows because either Credit or Debit values are null. On the fourth row we get the answer 460 because Credit and Debit contain values.

= Table.AddColumn(#"Changed Type", "Total", each [Credit] - [Debit] )

This post gives you 2 methods on how handle them. I personally prefer method 2, but each to their own.

Method 1 – Replace Null Values

The most common method recommended is to replace null values with a value that works, so in the above example a 0. So before I add the above calculation I highlight both the Credit and Debit columns. Then I click replace values on the Transform ribbon.

The above works and is simple to explain and document. It does have the side effect that the average Credit and average Debit are now altered and filters to find just credits or debits will have to be different. It also takes 2 steps. The averages issue is the one that most people dislike.

Method 2 – Using the Coalesce operator with Null Values

Coalesce functions, DAX has one, return the first non-null value. In Power Query there is a coalesce operator ??. So Credit ?? 0 will return the credit value unless it is null and then will return 0.

So we can change our calculation to put ?? 0 after the column values and make it not return nulls. The Credit and Debit columns still have their original values for averages etc. It also is done within one step.

= Table.AddColumn(#"Changed Type", "Total", each [Credit] ?? 0 - [Debit] ?? 0 )

Conclusion

Power Query M is one of my favourite languages but its weird. So these posts are for purely selfish reasons to remind me how to do things. ?? is a really simple operator that solves so many problems and Microsoft give it 2 lines! https://learn.microsoft.com/en-us/powerquery-m/m-spec-operators. It could of course be chained to Column1 ?? Column2 ?? Column3 will give the the first non-null value from the three columns, and return null if they are all null.