CODEWITHSUNDEEP
powerbipowerquerym
Jay Killeen
Jay Killeen

Reputation: 2922

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

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.

Upvotes: 3

Views: 78755

Answers (3)

Rick de Groot - gorilla.bi
Rick de Groot - gorilla.bi

Reputation: 406

Your issue is in the Text.Contains formula. You create an if statement that expects an expression that returns either true or false.

When the Text.Contains formula contains a null value, it returns 'null' as answer, and not true or false. You can adjust your code:

Text.Contains([UserRole.Name], "Manager")

To

Text.Contains([UserRole.Name]??"", "Manager")

The ?? is the COALESCE operator. In case it finds a null value, it now treats it as "". Instead of returning null it now returns true or false.

More on text functions in this article: https://gorilla.bi/power-query/text-functions/

Enjoy Power Query,

Rick

Upvotes: 2

Alex Benincasa Santos
Alex Benincasa Santos

Reputation: 860

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

Upvotes: 3

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

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.

Upvotes: 10

Related Questions