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!

No Managed Metadata columns in Power Query

When using Power Query to retrieve data from an O365 SharePoint list, you run into trouble when that list data contains columns connected to the Term Store (managed metadata).

These columns are not retrieved by Power Query, not using the "SharePoint List" connection and also not using the "oData" connection.

So, the managed metadata column "organisatie eenheid" is in this list:

But not in the Power Query result sets (oData):

Or in the "From SharePoint List" resultset:

I've notified Microsoft of this issue here, let's wait and see if there are any workarounds or fixes !
Will keep you posted...

Update 24 June 2014:

Ben Martens from Microsoft searched around and asked the product team about this issue and unfortunately the conclusion is that right now, it's not possible to get MMD data extracted from SharePoint.
Both the ListData.mvc as OData connections won't bring back these columns from your SharePoint lists.
Sorry for you ...
Let's hope these services get modified by the SharePoint team and start exposing this data, there are many scenarios where people really want to work with the Managed Metadata ! (why use it if it's not supported !?!). Thanks Ben for this update !

Use English as your #0365 (sub) site's default language or else...(use oData)

Hi there,
a quick post on something I discovered yesterday during the creation of a proof of concept a #0365 site that provides it's list data to an Excel Power Query & Power Pivot report. (Which will be published on a Power BI site, that will be described on it's own blog post :) )

I was having trouble getting the data from the O365 SharePoint list into Excel. After making the connection with Power Query:

The "navigator" which should show me all available lists on a given SharePoint URL was empty:

I tried different security settings, checking the data connections, removing all data connections and adding them again, blaming the latest Power BI update, googling, nothing worked. And the strange thing was that I'd seen it work in an earlier POC I did...
Eventually I tried another site URL (on the same O365 web application), that one worked !
Now I had to find out what the difference was between the working and none working sites. Soon I realized it was the regional and language settings. I made the regions the same, no difference. But when I created a new site and gave English as the default language upon creation, Power BI could instantly connect to it. 

I created a new site to test because changing the default language in O365 SharePoint is not as easy, I think I'll need Power Shell to do this but of course I need to focus on my original POC first :) 

I quickly asked the community for feedback here: http://community.office365.com/en-us/f/172/p/226946/756295.aspx#756295

and here: http://community.office365.com/en-us/f/172/p/244657/756386.aspx#756386

I also found a post from Kasper de Jonge, a senior program manager at the Microsoft SQL Server BI division and a good friend; He said he'll take this issue up with the PQ team and just posted an update on this:

"Update: Yes it is a known issue which they are working on with SP, the workaround is to connect to the sharepoint API using OData, rather than using the From SharePoint option in PQ" 

Thanks Kasper !