Forum Discussion
Solved
Remove text from column but leave numbers
Hi community! I have a data set where I have a column [Litres] which should be a numerical value, however the people inputting the data sometimes put text. I know I could use a data validation on...
Easiest thing is a new column as you suggest, but use try/otherwise and not if/then/else.
The formula is:
try Number.From([Column1]) otherwise null)If the conversion to number returns an error, try/otherwise will return null. You can change that to 0 or whatever in the formula.
Good day JNelson,
A slight tweak on edhans solution is to transform the column using edhans formula. This avoids creating a new column and having to delete the old column.
= Table.TransformColumns( #"Previous Step", {{"Column1", each try Number.From(_) otherwise null }} )
Hope this helps.
Thank you! That also works 🙂