I'm trying to recreate an Excel formula in Power Query, but am unsuccessful. The formula today looks at a numeric value and categorizes it, for example, if the value is greater than 10,000, then category >$10,000 is the result in the cell. I am using Add column, Custom Column, see example formula that is giving Expression.Error: We cannot apply operator < to types Number and Text.
Details:
Operator=<
Left=10000
Right=-
if [YTD Revenue USD] <10000 then "<$10k"
else if [YTD Revenue USD] >500000 then ">$500k"
else if [YTD Revenue USD] >1000000 then ">$1M"
else ""
Hi Julia,
Check if the values in your [YTD Revenue USD] column are actually numbers. PQ is complaining that it's seeing text in that column.
Regards
Phil
Thank you Phil! I figured that was the issue, as my column comes in as type ABC, but it is a number field (revenue). When I try other data types, the zero value items return ERROR. No other data type works. I think this is because the data has a - in it for items with no revenue. I replaced the - with a 0 then I was able to change the data type without error and then add my formula. Whew!
Thanks for the quick reply!
No worries Julia. Yes as you discovered a - in a numeric field needs to be changed to something else, like 0
