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

[bchager6]

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!

[bchager6]

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

[watkinnc]

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

[watkinnc · 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 ""