Forum Discussion

JNelson's avatar
JNelson
Icon for Helper II rankHelper II
2 years ago
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...
Need Help
  • edhans's avatar
    edhans
    2 years ago

    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.

     

JNelson's avatar
JNelson
Icon for Helper II rankHelper II
2 years ago

Thank you so much! Works perfectly

collinsg's avatar
collinsg
Icon for Solution Sage rankSolution Sage
2 years ago

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.