[Expression.Error] We cannot convert the value null to type Logical.

[Anonymous]

Hi All...

I've read through a few similar threads but didn't find a solution to my problem. Hoping someone can assist.

I'm using the following code in Power Query to assign a fiscal year (FY) to a row when certain date conditions are met within the startdate and enddate columns for a project.

each if[startdate] <> null or [enddate] <> null and [active] = "1" and [live] = "1" and [latest] = "Green" and [startdate] > #date(2020,3,31) and [enddate] < #date(2021,4,1) then "FY21" else ""

I would like to exclude rows from the expression when the startdate or enddate is null.

I receive the following error with the code I wrote when there is a null value in either the startdate or enddate column: [Expression.Error] We cannot convert the value null to type Logical.

Kudos to anyone who can help!

[Rebecca_JC]

I had the same issue and none of the other answers online solved it. This worked for me:

My initial conditional column (or formula) had specific tests to return different specific values for faculty, such as "Faculty-Research", "Faculty-Visiting" etc. based on the Rank Title field and then a catch-all Else clause to return "Staff" for all other values. The problem was that the staff had null values for Rank Title and the formula did not know how to deal with that (I thought the else clause would have taken care of them, but it did not). So my solution was to use: if Rank Title equals null Then "Staff" as the first clause (getting rid of the issue at the beginning of the formula instead of trying to cover it with the Else clause at the end).

It also might work to change all the null values in Rank Title to "".

[Anonymous]

Thanks, guys. What works best for my use case is to replace the null values with "".

[Anonymous]

But if you are actually trying to exclude the rows, you should just select the rows:

Table.SelectRows(PriorStepName, each not is null ([startdate]) and not is null ([enddate]) and [active] = "1" and [live] = "1" and [latest] = "Green" and [startdate] > #date(2020,3,31) and [enddate] < #date(2021,4,1))

--Nate

[Anonymous · Accepted solution]

null values can trip you up when used in if statements, and also with equality operators. I would try:

each if [startdate] = null then "" else if [enddate] = null then "" else if [active] = "1" and [live] = "1" and [latest] = "Green" and [startdate] > #date(2020,3,31) and [enddate] < #date(2021,4,1) then "FY21" else ""

--Nate

[AlexisOlson]

I think it may be trying to compute the "null and [active]" part. You can probably resolve this by adding parentheses to clarify the order of operations.

At minimum, add these ones:

each if ( [startdate] <> null or [enddate] <> null ) and [active] = "1" and [live] = "1" and [latest] = "Green" and [startdate] > #date(2020,3,31) and [enddate] < #date(2021,4,1) then "FY21" else ""