Thursday, November 01, 2018

Thousand Separator and Decimal Separator in Power BI Desktop, Chrome, IE & Edge

Recently I got the question from one of the Power BI report users why the currency formatting was 'wrong' in all reports.
Not sure what changed, I went investigating and discovered it was about the way millions of euros were shown in the report. We in the Netherlands are used to separate thousands using a "dot" or "."
E.g.:

But Power BI keeps showing comma's for example - 1,234,567.89  thousand separator is a comma and decimal separator is a comma).

Very soon I found this post on the great Power BI Community stating to change the Locale setting in Power BI Desktop. This had no effect for me, even after restarting Power BI Desktop (a couple of times); No matter what Locale I choose, the display was containing commas.

Then I found a post stating to change the Locale setting in Windows 10 - Control Panel - Clock and Region - Region settings e.g:





















As you can see, somewhere during the installation of Windows 10 I choose the format "English (Netherlands)". The preview screen doesn't show the currency format, only the information around dates. So I clicked the "Additional settings" button in the bottom. Here you can change/ customize the Decimal symbol for both 'Numbers' and 'Currency'.  In Currency you can even choose the 'Digit grouping symbol':


The problem is ... when you start customizing this.. these changes are not reflected in Power BI Desktop reports. Only the selected 'Format' is reflecting in Power BI.


















E.g. when I change the 'Decimal Symbol' value from a comma to a dot in above screen, Power BI desktop will still show both comma's E 4,100,235 (Using the English (Netherlands) format):












But when I change the Region -> Format Locale to "Dutch (Netherlands)", Power BI Desktop (after restart) updates the changes immediately:




So my tip is to stay away from the "Additional settings" configuration to change decimal symbol information; these changes won't reflect into your Power BI Desktop reports. Use the desired "Format" to use the desired format options:




PS: I'm using the Windows 10 control panel -> regional settings to change this. There is also a regional settings configuration in Power BI Desktop. Changing settings here doesn't seem to be having any effect..

Update: Above results are all based on Power BI Desktop. When viewing the (published) report in the browser, the results are different again. It seems that Internet Explorer & Edge are taking the Windows 10 Local in account. Chrome uses it's own language settings which you can change using the url: "chrome://settings/languages"

















When Dutch is at the top: E 4.100.235
When English is at the top: E 4,100,235

Thanks
Jeroen

UPDATE 22-11-2018
Yesterday I had to export some SQL tables into another database (creating an export for the auditors). I had done this before and thought it would be a nice and easy job until it bombed out with the error:

Culture is not supported:
**tablename**
3072 (0x0c00) is an invalid culture identifier

A quick google revealed: Win 10 region settings and a little light bulb went on. I remembered creating this blogpost and changing the region settings to experiment with the thousands indicator. After setting the location back to US and language to US the SSIS job (which executes the export data wizard in the SQL Management tool) ran perfectly again without any problems!


2 comments:

  1. Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles Power Bi certification

    ReplyDelete
  2. Your article is a wonderful help. Thanks!

    ReplyDelete