cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RacAgr
Helper I
Helper I

Thousand-Separator-and-Decimal-Separator

hello,

 

for the currency column, I'm having problems with decimal separator and thousand separator.

 

Decimal separator is a comma and thousand separator is a point. But PowerBi goes wrong

 

for example - powerbi shows 121,000.00 (thousand separator is a comma and Decimal separator is a point)

 

Output expected, 121.000,00 (Decimal separator is a comma and thousand separator is a point)

 

Thanks

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

The display of numeric felds (and dates, times etecetera) depends on your Regional Settings.

 

It can be adjusted in Power BI Desktop or in Windows (Land/Region). It looks like the Windows setting is leading, but you have to close and reopen your pbix file after adjusting the Regional Settings.

 

Regional Settings.png

 

In Power BI you can also adjust your language settings to influence the display format for published reports.

 

 

Specializing in Power Query Formula Language (M)

View solution in original post

12 REPLIES 12
Deepikasimha
Frequent Visitor

Hello,

 

We have a power bi report embedded int .net application. We have given ($)United States format for one column in the report. It shows $123,345,678(Right format) in few browsers and machines and $12,33,45,678(wrong format) in few browsers.

 

Can some one help me to fix this please.

 

Thanks,

Power BI Learner

Mahashankari
New Member

The Thousand Separator could not work, if the currency value contains more than 8 digits. Provide solution for this. 

Jasper_Eggink
Regular Visitor

Helle all,

 

Thanks to this blogpost I have found a sulution for this problem without changing the locale for the whole Power Bi file. The steps are:

 

  1. Go to the Power Query-editor and select the column
  2. Change the data type to "decimal number" in the transform menu
  3. Open the advanced editor and add the desired locale string to the "Table.TransformColumnTypes" function.

Example:

 

#"Type gewijzigd" = Table.TransformColumnTypes(#"Kolommen verwijderd",{{"sales.grand_total_month", type number}}, "en-US")

v-shex-msft
Community Support
Community Support

Hi @RacAgr,

 

Based on test, if I export these data to csv, it will remove custom format and keep the normal number. So I think you store these value with text format, right?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

these values are in decimal number then using currency 

Hi @RacAgr,

 

I can't reproduce your issue, It works well on my side.

10.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Output expected, 121.000,00 (Decimal separator is a comma and thousand separator is a point)

 

 

in your result, you get 121,000.00 (Decimal separator is a point and thousand separator is a comma), which i am able to get as well. but i need the output as shown in first line

MarcelBeug
Community Champion
Community Champion

The display of numeric felds (and dates, times etecetera) depends on your Regional Settings.

 

It can be adjusted in Power BI Desktop or in Windows (Land/Region). It looks like the Windows setting is leading, but you have to close and reopen your pbix file after adjusting the Regional Settings.

 

Regional Settings.png

 

In Power BI you can also adjust your language settings to influence the display format for published reports.

 

 

Specializing in Power Query Formula Language (M)

View solution in original post

Has something recently changed in this behaviour? I can't seem to get the xxx.xxx,xx formatting using the Locale setting.. 

I found the solution to my issue; PBI Desktop seems to be only looking at the "Region - Format" part in the Win10 - Control Panel - Clock and Region - Region settings e.g:

Format: Dutch (Netherlands). When customizing this format using the "Additional settings" button, these customizations are not taken into account by PowerBI Desktop; So, it's VERY important to pick the correct Format: selection and not customize an existing selection to the needs of your users. tmp_pbi.JPG

 Created a blogpost around my findings on this. 

 

vanessafvg
Super User I
Super User I

@RacAgr

 

this is the correct format as far as i know.

 

powerbi shows 121,000.00

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




any suggestions pls on how to achieve the expected output?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors