Error: We cannot apply operator < to types Number and Date.

[Anonymous]

Hi!  - I get the following error when trying to perform a calculation in PowerQuery:

Error: We cannot apply operator < to types Number and Date.

Here is my formula:

if [Year] = 2020 and [Sample] = "AAA" and [Date] >= 5/1/2020 and [Occurance] >= 2 then [Sample2] else "Test"

Data Type for [Year] is Whole Number, [Sample] is Text, [Date] is Date and [Occurance] is Whole Number.

Could someone please help with this? Thank you in advance!! 

[HotChilli · Accepted solution]

Exactly what you wanted to prove.

Now, make sure that power query knows the date is a date.  Use this https://learn.microsoft.com/en-us/powerquery-m/sharpdate 

[Anonymous]

amazing, worked like a charm! thank you!!

[HotChilli]

Here's how to debug this:-

There looks like there is only one (attempted) date comparison so, as a test, remove everything apart from the date comparison.  Still getting the error? There is something wrong with the syntax. PQ thinks one is a date and one is a number, so attempt to fix that.

See if that moves you on a bit.

[Anonymous]

Gave this a shot but still the same error: "We cannot apply operator < to types Number and Date."