cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
LukasBE Frequent Visitor
Frequent Visitor

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)decimals.PNG except those 2 in the screenshot below

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When I use replace function nothing changes (even when I tried to replace comma by dot or even any digit for another). It seems like there is forbiden (or locked) to make changes to this column.

 

The original file was JSON but I only made simple changes like convert to table and expand columns and added 1 new column.

 

Any ideas where is the problem?

Thank you

Lukas

1 ACCEPTED SOLUTION

Accepted Solutions
tex628 New Contributor
New Contributor

Re: Replace "." to "," does not work

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!

7 REPLIES 7
khlebak Frequent Visitor
Frequent Visitor

Re: Replace "." to "," does not work

Try the SUBSTITUTE function if you haven't already.

 

SUBSTITUTE( [total_price] , "." , "," )

 

https://docs.microsoft.com/en-us/dax/substitute-function-dax

LukasBE Frequent Visitor
Frequent Visitor

Re: Replace "." to "," does not work

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

khlebak Frequent Visitor
Frequent Visitor

Re: Replace "." to "," does not work

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.

Kalpavruksh Regular Visitor
Regular Visitor

Re: Replace "." to "," does not work

Hi,

 

Please make sure that the first you have converted the column to its necessary format e.g. number or decimal in this case. Then try to use replace function.

 

Kalpavruksh Technologies | Microsoft Gold Partner
Denmark | USA | India | Germany

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

LukasBE Frequent Visitor
Frequent Visitor

Re: Replace "." to "," does not work

Hi Kalpavruksh,

 

do you mean to change the format in Power Query?

Because when I did it then I couldnt use replace functionbi_error.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks

Lukas

tex628 New Contributor
New Contributor

Re: Replace "." to "," does not work

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!

LukasBE Frequent Visitor
Frequent Visitor

Re: Replace "." to "," does not work

Hi tex628,

 

your solution worked perfectly Smiley Happy

 

Thank you

Lukas