Forum Discussion
Expression Error: < to Types Date and Number
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!
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
3 Replies
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,
AngeliaThis solution worked seamelessly. Thank you for your help!
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
