Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
This 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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |