Forum Discussion
Clean data to only Integers
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
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
That works at converting the numbers that are formatted nicely, but doesn't do written numbers unfortunately.
Wait... so you have some field with value like "Five" and need that converted to 5?
Hmm, can't think of way to do this, except with separate conversion table.
