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
Correctly Sum Two Or More Columns in Power Query and Power BI

Comments

  1. 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!

  2. 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

  3. This solved the puzzle right before I started developing anger. Thank you!

  4. 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.

  5. Very helpful Maxim. Thanks for sharing 🙂

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.