cannot convert type text to number

[dkehoe32]

Hi,

Im trying to find the price impact by subtracting the "2017 AUP" from the "net unit price (int)(converted).  One of the values in 2017 AUP is "No PY AUP," which is giving me the error.  Every time I try to subtract it gives me the error that they couldnt convery "No PY AUP" fom type text to type number. 

Dylan

[jthomson]

Depends on what your desired outcome is. You could replace the value that's causing the error with a null, or you could put in some conditional logic whereby if the 2017 AUP value is No PY AUP then give it whatever value you need the calculation to be, else do the calculation as normal

[dkehoe32]

is there a way to convert it to a null set thats not in the query editor?

[jthomson · Accepted solution]

You could make a calculated column saying newcolumn=if(yourtable[2017 AUP]="No PY AUP",blank(),yourtable[2017 AUP]), but I'm not sure why you would want to do that outside of the query editor

[dkehoe32]

That works thanks for the help!

My query editor for some reason doesnt line up so if i were to edit it the query it wouldnt affect my table.