Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Replace "." to "," does not work

Hi guys,   I dont know why but replace does not work in some columns. Most of the values in the column are in the correct form (decimal numbers are separated by comma) except those 2 in the sc...
Need Help
  • tex628's avatar
    tex628
    7 years ago

    Hi Lukas,

     

    The reason your replace is not working is because the dataformat is general. You are trying to replace string values while the column doesn't have defined format. If you first convert the column to text, then do the formatting you should get the proper result. 

    Assuming you're european this is what i advice you to do:
    - Format the column to text
    - Replace "." with ","
    - Format the column to decimal number. 

    Hope it works!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Khlebak,

     

    Thank for help,. It seems that it helped but it is strange.

     

    Just if I got it correctly:

    1. I created a new column "Total Price = SUBSTITUTE('[total_price];".";",")".

    2. Everything in this column was as Text so I changed it to the decimal number

    3. Here problems appear because when I summarize this column in the table, the numbers are much higher than the original

    4. Then I changed the formula and replaced all commas with dots and now it seems like it is working and showing right numbers

     

    Is it possible that in PowerQuery Editor the correct decimal separator is "," but in the Power BI Desktop it is "."? 

     

    Thanks

    Lukas

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I could be wrong but I believe that is controlled by the regional settings? I'm assuming PBI defaults to US locale, which would make it think "." is the correct separator. If you go to file > options > regional options you can adjust it for other regions.