Clean a numeric column

[kamiluc]

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 that some values already had decimals. So I end up with values like "123.45.00".

I'm trying to get a transformation to get rid of the ".00" to make it "123.45", while other numbers like 1000.00 must remain the same.

The rule would be something like IF (count 2 dots) then (remove .00) else (do nothing).  Seem simple but I don't know how to write this.

Thanks in advance!

[kamiluc · Accepted solution]

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]

Thanks to both the responses so far. I've tried them both but I noticed something in the data I hadn't mentioned. Some values don't have decimals at all, like "123" or simply "0" and these seem to interfere with the proposed solutions.

[ValtteriN]

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/

[123abc]

In Power BI, you can clean a numeric column with values like "123.45.00" by using a combination of Power Query's custom column and conditional logic. Here are the steps to achieve this:

1. Load your data into Power BI.

2. In the Power Query Editor, select the table that contains the numeric column you want to clean.

3. Click on the "Add Column" tab and select "Custom Column."

4. In the "Custom Column" dialog, you can use the following M-language expression to clean the column:

if Text.PositionOf([YourNumericColumn], ".00") > 0 then
Number.From(Text.Replace([YourNumericColumn], ".00", ""))
else
[YourNumericColumn]

Make sure to replace [YourNumericColumn] with the actual name of the column you want to clean.

This expression checks if the ".00" pattern exists in the column and, if it does, removes it and converts the result back to a numeric value. If the pattern doesn't exist, it leaves the original value unchanged.

1. Click the "OK" button to create the custom column.

2. Now you have a new column that should contain the cleaned numeric values.

3. You can rename this new column to something like "CleanedNumericColumn" for clarity.

4. Close the Power Query Editor and load the data into your Power BI report.

Your numeric column with values like "123.45.00" should now be cleaned, and you'll have "123.45" in the new column while leaving other values like "1000.00" unchanged.

Remember to adjust the column names and expressions to match your specific dataset if needed.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.