Forum Discussion
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 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
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!
9 Replies
Try the SUBSTITUTE function if you haven't already.
SUBSTITUTE( [total_price] , "." , "," )
https://docs.microsoft.com/en-us/dax/substitute-function-dax
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
- Kalpavruksh
Resolver I
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.- tex628
Community Champion
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!
