Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    v-lili6-msft

    Thank you for the very comprehensive answer