We cannot convert the value null to type Logical

Power Query
1

Hi there,

I am trying to create a conditional column based on another column. When I created a conditional column, I got an error “Expression. Error: We cannot convert the value null to type Logical”. I am not sure where am I making the mistake. Below is the description of the columns:

First I created a column in power query which looks like as follow:

Customer Notification (days) =

if [InterruptionType] =“1” then null else
if Date.Year([#“Cancelled Date (NZST)”]) = 1900 and [InterruptionType] = “2” then List.Max({[#“Notice to Retail Consumer (days)”], [#“Notice to Direct Bill Consumer (days)”]})
else
if Date.Year([#“Cancelled Date (NZST)”]) = 1900 and [InterruptionType] = “3” and Date.Year([#“Notified Interruption Date Time (NZST)”]) <> 1900 then List.Max({[#“Notice to Retail Consumer (days)”], [#“Notice to Direct Bill Consumer (days)”]}) else null

and it looks like as follow:

image

Then I created a conditional column using the following logic:
image

However, it throws error of "Expression. Error: We cannot convert the value null to type Logical.

image

Could anyone help me where I made the mistake?

M code script can be found here:
script.txt (14.7 KB)

2

likely your data element has a blank which cannot be converted to use your logic in your m code… you will have to view your raw data

plus your print screen is so small that can’t be read it

just another note that might help in your coding is to put comments on what your coding is trying to accomplish

3

@leo_89 ,

I think the problem is that in PQ, if the field is nullable a value of null is neither true nor false. I can’t read your screenshots either and when I try to download your M code, it asks for a login, so I can only respond in general terms at this point. But one workaround is to use a logic structure that is equivalent to null, but evaluates to true or false, such as

text.length( [column name] ) = 0 as opposed to [column name] = null

  • Brian
4

@BrianJ I have uploaded the M code Script.

5

@leo_89 ,

Thanks. Can you also please upload your work in progress PBIX and underlying data file?

  • Brian
6

Hi @leo_89 ,

You can only compare null values for equality , and 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 the comparison is not the logical value true or false, but the null itself, and the if…then…else statement raises an error.

There are two ways you can deal with this:
1 - replace the null with some other value OR
2 - perform the null equation check first.

#"Added Custom11"
  = Table.AddColumn(
    #"Renamed Columns19", 
    "10 Day Notification Issued", 
    each 
      if [#"Customer Notification (days)"] = null then
        null
      else
      if [#"Customer Notification (days)"] < 10 then
        1
      else
        0
  )

I hope this is helpful.

2 Likes