Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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!
I'm seeing this as well. I am trying to convert WW23'22 into WW23.22. For some it works but for others it doesn't. The column is text - I've never seen this behaviour.
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.
Hi Kalpavruksh,
do you mean to change the format in Power Query?
Because when I did it then I couldnt use replace function
Thanks
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!
This worked well, thank you!
Hi tex628,
your solution worked perfectly 🙂
Thank you
Lukas
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |