Clean data to only Integers

[Anonymous]

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. 

[Anonymous]

In the query edior I duplicated the column, then changed the data type to Decimal Number, then used the "Replace Errors" function to make all the strings 0.

It's a bit wanton so would like to hear any other approaches.

[Greg_Deckler]

Is it OK to remove the rows that would be errors?

[Anonymous]

@Greg_Deckler

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.

[Chihiro]

May be something like...

= try Number.From([Column]) otherwise 0

[Anonymous]

That works at converting the numbers that are formatted nicely, but doesn't do written numbers unfortunately.

[Chihiro]

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.

[Anonymous]

Correct, I was hoping for a function to do it, but doesn't look like such exists. Coversion table it is!

[v-lili6-msft · Accepted solution]

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

[Anonymous]

@v-lili6-msft

Thank you for the very comprehensive answer