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:

= if [Values] < 5 then "A" else "B"

1	= if [Values] < 5 then "A" else "B"

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:

null = null // true
null = 5 // false
null = true // false
null = false // false
null <> "a" // true

null = null // true

null = 5 // false

null = true // false

null = false // false

null <> "a" // true

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:

if logical_value then do_this else do_that

1	if logical_value then do_this else do_that

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:

null = SomeValue // true o false

1	null = SomeValue // true o false

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:

if null > 0 // null, and 'if null' returns an error: null is not logical
then do_this
else do_that // whole calculation returns an error

if null > 0 // null, and 'if null' returns an error: null is not logical

then do_this

else do_that // whole calculation returns 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:

if [Values] = null
then "C"
else 
    if [Values] < 5 
    then "A" 
    else "B"

if [Values] = null

then "C"

else

if [Values] < 5

then "A"

else "B"

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:

if [Values] < 5 then // there is an error raises and propagates!
then "A"
else 
    if [Values] = null 
    then "C" 
    else "B"

if [Values] < 5 then // there is an error raises and propagates!

then "A"

else

if [Values] = null

then "C"

else "B"

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

Comments

Zach Ehmann
February 24, 2021 - 17:08

Hello!

Thanks for writing this! It helped me understand why the null was causing the error. I also found another solution I wanted to share. In Power Query M in PowerBi, you can define variables within a function by beginning your formula with “let,” then defining your variables in sequence, and finally finishing with “in” and the name of the variable that actually contains your results.

So in this case, you could create an “adjusted” version of the column with the nulls that are causing your comparison logic to choke and substitute that variable’s results for the column itself. In my case, the final formula looked something like this:

let
ScoreAdjusted = if [Score] is null then Replace.Number([Score],null,0) else [Score],
Result =
if ScoreAdjusted >= 10 and [Other_Condition] = “Yes” then “Case 1”
else if [Third Condition] = “No” or ScoreAdjusted >= 12 then “Case 2”
else “Case 3”
in Result

The main benefit of this was that I didn’t have to reorganize my logic around declaring the null at the beginning!

Again, thanks for writing, and I hope this helps!
Edi
September 20, 2019 - 23:49

Hey

I wanna know when the cell is empty.

null not working in this case
- Maxim Zelensky
  October 9, 2019 - 17:18
  
  Hi Edi,
  cell in Excel could look like it is empty but can contain an empty string (i.e, “”). For the Power Query, it means it is not empty because it contains a value
Roman
July 3, 2019 - 23:12

This solved the puzzle right before I started developing anger. Thank you!
John
January 26, 2019 - 00:48

My issue is a little different. I need the value “null” actually returned to the table in the worksheet that calls this query. I’m doing a query against a DB2 table and right now in the query editor results I can see “null” for the fields with actual null data. But when I close the query and load the results to a table in a worksheet the field will show a blank instead of a null. Now I’m where I don’t really know if the field is null or contains ” which is a very big difference.
Rick de Groot
December 27, 2018 - 00:49

Very helpful Maxim. Thanks for sharing 🙂
- Maxim Zelensky
  December 27, 2018 - 15:10
  
  You are welcome! 🙂