Thursday, November 22, 2018

Using Office 365 Groups PowerShell to manage Power BI Workspace members

Imagine the following scenario:
As the IT admin for an organization Office 365 management is part of your job.
You're introducing Microsoft Teams in the organization and people are getting enthusiastic, requesting loads and loads of new Microsoft Teams sites.

Power BI workspaces are used to create and share Power BI reports. Every Power BI workspace is creating an Office 365 group and every Office 365 group is creating a Power BI workspace. What a fantastic happy connected Microsoft Cloud world we live in... When everything stays connected and works that is..

I'm not sure how the Office 365 group was initiated.. via the Power BI workspace or via the Teams site. I do know I didn't want to be a team owner anymore. As an admin I guided the team, provisioned their team one-note, plan etc. Now the team started communicating and @mention the group name. I'm receiving team related as an administrator, the time had come to 'leave the team'. (Teams function in member management). This action triggers some Microsoft internal workflows and eventually it will remove my full membership from the Office 365 Group. This was no problem for me until one (Monday) morning I receive an incident: "The Power BI dataset is not refreshing".

No worries mate! I'll quickly login to the Power BI workspace and investigate the issue, maybe even trigger the refresh manually. Not! Because I left the Teams Team and lost the Office 365 Groups membership I was no longer part of the Power BI workspace members, result: Unable to find or manage the workspace. Of course there is the Office 365 groups management from the Azure AD portal. Quickly find the group, add myself as owner and wait. Wait a bit longer, wait several hours and surely after 2.5 hours I can see the teams team again. Unfortunately I still can't see the Power BI workspace. Trying to add/ remove myself again (as owner of the group), nothing works. My colleague who still is a member of the workspace sees my account listed as 'admin'! But I can't see/ access the workspace. When he sends me the link I get an access denied!

24 hours later, still no access and I need to think of something. Something clearly doesn't function in the inner workings of the Office 365 Groups Permission management, updating the Groups connected objects like Teams, PBI workspaces etc. etc.
I decide to use the Groups PowerShell module to execute the same permission change as I tried to execute in the Azure AD portal. Groups management is done via the Exchange Online PS module so I create a connection:

1: $UserCredential = Get-Credential

2: $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri -Credential $UserCredential -Authentication Basic -AllowRedirection

3: Import-PSSession $Session -DisableNameChecking

Then using the Add-UnifiedGroupLinks command I try to add myself as an Owner of the group, I receive a error: 

PowerShell indicates I'm not listed in the group membership at all! AND I first need to add myself as a member before I can be added as an owner. I know this works the same way in Teams but the Azure AD Portal does not warn about this at all. It allows you to immediately add an account as owner of the group. This might be the reason why it's not functional from the Azure AD Portal?!

Surely, a few minutes after executing the PowerShell comands above I gained full access to the Power BI workspace and am able to help them out again! (Unfortunately this also means I'll be receiving the Teams @mention group-name again.. Need to figure out how I can fix that :)

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 "."

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


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:
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!