Comparing ‘null’ values in Power Query

Recently I needed to do the very simple thing in Power Query. I have the column of numbers and need to check if the values in this column are less than N and then put a corresponding text value in the new column. The function for the new column is something like this:

Actually some values are not a numbers but nulls:

Data contains nulls and comparison return an error

And this simple calculation returns an error for these values!

Why? There is the catch, which is hidden in the depth of documentation (actually on the page 67 of the “Power Query Formula Language Specification (October 2016)” PDF which you can obtain there.

To make long story short, there is a distilled excerpt from the documentation:

You can compare null  values for equality, but the null is only equal to null:

But if you would like to compare null with any other value with a relational operator (such as <, >, <=, >=), then the result of comparison is not the logical value like true true or false, but the null itself. In the Section “6.7 Relational operators” there is a small note about it:

If either or both operands are null , the result is the null  value.

So, what is the catch? The if…then…else statement uses some logical value after the if word:

When we compare (almost any) values of the same type, we receive the logical result: true  or false . But not in the case of null  – we receive a logical value as the result of comparison only when we compare null for the equality:

BUT when we compare the null for relation to the SomeValue, then the result is not logical (it is null), and the if…then…else statement raises an error:

So, how to avoid this error if your data can contain nulls and replacement of the null with other value is not an option for you?

From the statements above it is obvious that we need to check value for equality with null, and if it is not equal null, then perform a relational comparison.

You need to construct the correct comparison criteria, adding checking for the nulls in the first place. In my case it should look like this:

Be sure to perform the check for null in the first place! Because if…then…else construct performs consecutive calculation of criterias, and if we put the relational comparison in the first place, then the error raises and propagates to the end of the calculation chain:

Put the null equality check in the frist place, otherwise there could be an error

 

Share this
ISO Week in Power Query / M language and Power BI

Leave a Reply

Your email address will not be published / Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.