Forum Discussion

kamiluc's avatar
kamiluc
Helper 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]),

kamiluc's avatar
kamiluc
Helper I
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]),