Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
tex628
Community Champion
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!


Connect on LinkedIn

View solution in original post

9 REPLIES 9
dottyspog
Regular Visitor

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.

Kalpavruksh
Resolver I
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.

Anonymous
Not applicable

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
Community Champion
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!


Connect on LinkedIn
Anonymous
Not applicable

This worked well, thank you!

Anonymous
Not applicable

Hi tex628,

 

your solution worked perfectly 🙂

 

Thank you

Lukas

Anonymous
Not applicable

Try the SUBSTITUTE function if you haven't already.

 

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

 

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

Anonymous
Not applicable

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
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.