# 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:

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

Actually some values are not a numbers but nulls:

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:

1 2 3 4 5 |
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:

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__:

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:

1 2 3 |
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:

1 2 3 4 5 6 |
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:

1 2 3 4 5 6 |
if [Values] < 5 then // there is an error raises and propagates! then "A" else if [Values] = null then "C" else "B" |