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

2
votes

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.

2
powerbipowerquerym

2 Answers

8
votes

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.

3
votes

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