Forum Discussion

kamiluc's avatar
kamiluc
Icon for Helper I rankHelper I
2 years ago
Solved

Clean a numeric column

Hi, I have numeric column where some values are appearing with two dots for decimals. I think at some stage in the data generation, there was ".00" added to all values, without the consideration tha...
Need Help
  • kamiluc's avatar
    kamiluc
    2 years ago

    While the solutions proposed by ValtteriN and 123abc probably work in a different scenario to mine, this is what I did in my case and it has worked:

     

    I created a column that counts how many dots exist in the column, and then another column that removes ".00" if the number of dots is equal to 2.

     

    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Count Dots", each Text.Length([MY_COLUMN]) - Text.Length(Text.Replace([MY_COLUMN], ".", ""))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Count Dots] = 2 then Text.Replace([MY_COLUMN],".00","") else [MY_COLUMN]),

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
2 years ago

Hi,

Here is one idea (you can combine the steps but here is step by step): You can split the column by delimer "." like this

= Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}, {"Column1.3", Int64.Type}})

Then combine the new columns together.

#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Column1.2", type text}, {"Column1.1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each [Column1.1]&"."&[Column1.2])

If you want a second 0 after the 1000 change the amount of decimals

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/