Expression Error: < to Types Date and Number

[j_t0418]

I'm trying to add the below custom column formula to a query of mine, but am recieivng  [Expression.Error] We cannot apply operator < to types Date and Number

if [SupplierText]="X" and [Date]>= 5/23/17 and [Date]<=6/8/17 and [Number]=null then 0 else if [SupplierText]="X" and [Date]>= 5/23/17 and [Date]<=6/8/17 and [TypeText]="X" then [Number] else if [SupplierText]="X" and [Date]>= 5/23/17 and [Date]<=6/8/17 and [TypeText]="Y" then 0 else if [SupplierText]="X" and [Date]>= 5/23/17 and [Date]<=6/8/17 and [TypeText]="Z" then 0 else [Number2]

Any insight would be much appreciated!

[v-huizhn-msft · Accepted solution]

Hi @j_t0418,

In your Query statement, 5/23/17  is recognised as text, please use #date(2017,5,23). Please use the following statement, and check if it works fine.

if [SupplierText]="X" and [Date]>= #date(2017,5,23) and [Date]<=#date(2017,6,8) and [Number]=null then 0 else if [SupplierText]="X" and [Date]>= #date(2017,5,23) and #date(2017,6,8) and [TypeText]="X" then [Number] else if [SupplierText]="X" and [Date]>= #date(2017,5,23) and #date(2017,6,8) and [TypeText]="Y" then 0 else if [SupplierText]="X" and [Date]>= #date(2017,5,23) and #date(2017,6,8) and [TypeText]="Z" then 0 else [Number2]

Best Regards,
Angelia

[j_t0418]

This solution worked seamelessly. Thank you for your help!

[Bennes]

I have similar issue, and the advice posted in this forum didn`t help.

#"Changed Type" = Table.TransformColumnTypes(TIMEFACTSDAY1,{{"DAY", type date}}),
#"Filtered Rows" = Table.SelectRows(TIMEFACTSDAY1, each not List.Contains({129,1142},[USER_ID]) or [DAY] <= #date(2018, 1, 1))
in
#"Filtered Rows"

"we cannot apply operator to types date and datetime"

Where could be the problem please?

Thanks