Power Query M - We cannot convert the value null to type Logical

Question

In Power BI I have an M Query that tests for the value in a column equaling or not equaling to null.

When I add the statement for [Sale.Revenue] <> null I get an error however it works fine for the [UserRole.Name] = null it works fine. Tested just by removing the statement and adding it back.

We cannot convert the value null to type Logical.

This seems like it should work but just can't figure it out.

add_user_role_group = Table.AddColumn(
    join_expand_sale, 
    "UserRole.Group1", 
    each (
      if [UserRole.Name] = null and
         [Sale.Revenue] <> null then
        "Group1"
      else if Text.Contains([UserRole.Name], "Manager") then
        "Group2"
      else
        "Undefined"
    )
  )

I am sure it is something glaringly obvious :/ Thanks for your thoughts on this.

Solution 1

One of your rows has a null value for both UserRole.Name and Sale.Revenue. You need to check for that explicitly, and then add it to the "Undefined" group.

What happened is that the first condition fails because Sale.Revenue is null. The second condition calls Text.Contains, which returns null when [UserRole.Name] is null (Text.Contains returns a nullable logical value). null is not true or false, so you get the error.

Solution 2

After a such journey, finaly I found Text.Length !!

You can solve your problem like this:

if Text.Length([UserRole.Name]) = 0 and
         Text.Length([Sale.Revenue]) > 0 then

I hope I have helped you. Reference: Power Query M - Text.Length

Comments

[Jay Killeen]

Thanks mate. I had a think about your answer and I first tested for UserRole.Name = null, started a new if statement inside of that and then dealt with the null revenue after the last else statement. Thank you for your insight.