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]),

123abc's avatar
123abc
Community Champion
2 years ago

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.