Forum Discussion
Clean data to only Integers
Hello all
I have a data set that contains a mixture of integers and strings, all attempting to indicate a value (e.g 1, one, 01).
There are also some erroneous inputs, like e3.
What's the best practice for cleaning this data?
The data set is constantly updated, so I can't simply filter. I'm happy to mark the inavlid inputs as N/A but I need the data cleansed to only the Integers so I can run formulae.
hi, Anonymous
If you want to Conversion values like "Five" to integers, you need a conversion table like this:
then do these as below:
step1:
use this formula to add a column
try Number.From([Column1]) otherwise 0
Step2:
merge basic table with conversion table
expand the table
Step3:
add a conditional column
Result:
here is pbix, please try it.
https://www.dropbox.com/s/hy5kcg8b1sgidnb/Clean%20data%20to%20only%20Integers.pbix?dl=0
Best Regards,
Lin
9 Replies
Is it OK to remove the rows that would be errors?
Ideally I would like to convert the numbers as strings (written words) to integers without writing some huge IF statement that accounts for every number.
If that's not possble though, then yes, I would simply remove all the errors.
May be something like...
= try Number.From([Column]) otherwise 0
