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!

Wednesday, October 12, 2016

SharePoint Resources

Overview of my SharePoint resources to keep up to speed with latest technology. (v1: 12-10-2016)

1) Using the website to track sites like:

1b) Twitter
2) Listen to the latest news @ The Microsoft CloudShow:

3) Follow online events & webcasts:

(Potential) partners:

4) Microsoft Mechanics

5) Microsoft Virtual Academy

6) Pluralsight training

7) Linked In Special Interest Groups
SharePoint Community:
SharePoint 2013:
SharePoint Enterprise Solutions architects:

8) The SharePoint network (previously SPYammer)

Thursday, June 11, 2015

Error removing SQL SnapShots

When trying to delete some Microsoft SQL Server 2012 (SP1) snapshots, that I created as part of a recovery procedure before upgrading a SharePoint farm to Service Pack 1, I ran against this error:
Drop database [snapshotDbName]
Msg 21, Level 21, State 1, Line 1
Warning: Fatal error 615 occurred at Jun 11 2015  2:51PM. Note the error and time, and contact your system administrator.

Or via the interface:

I knew I had enough rights because I was using the same account that created the snapshots. After trying setting the snapshot-parent-db to single user, etc. etc. I ran into this solution:

 alter database [YourDBName] set emergency

The result will be:
Msg 5093, Level 16, State 1, Line 1
The operation cannot be performed on a database snapshot.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

BUT, now you ARE able to delete the snapshot using the UI or:
drop database [bwdba_SS]

Don’t know why but .. it works because of the (emergency) herbs :)

Friday, August 01, 2014

Display page properties in SharePoint page, like “last modified date” & “modified by” using SPServices (& jQuery)

A colleague got the request to add the latest modification date & modifier name to a SharePoint page.
This is convenient for users visiting the page, to see when the page is changed and by who.

I didn’t have much time (as usual) for this assignment and different options ran through my mind. At first I played a little bit with a custom page-layout where I would stuck the Modify data & modifier properties into the aspx; I quickly ran into all kinds of trouble using this option (publishing features must be on, the test O365 SharePoint site began throwing errors at me, (even after activating built-in page layouts), this was out of scope for my assignment so I quickly went out looking for other technology that could help me out!

The SharePoint 2013 Client Side Object Model (CSOM) is one of those technology options. Another option is jQuery, SPServices is such a jQuery library. But how do you implement this? Lots of examples but little of them are in context / complete and therefore not very useable for beginners. This is one of the reasons I wanted to blog about my solution, to create a full working example that can get you started. Visit this blog on when to use the CSOM / when to use the jQuery libraries.
SPServices is an initiative from Marc. D. Anderson. Visit his site for loads of resources !

 I started my journey with the SPServices “framework” which you can find here:
This codeplex project is full of usable resources and is providing most of the content of my final script. To start off you will need to reference 2 links; one to include the JQuery classes and the other one to include the SPServices classes. SPServices provides you with easy to use code that in the back talks to the SharePoint object model. This way you can create effective functions that are not available in SharePoint 2013 out of the box. For example: (all examples from the documentation part of the codeplex project with detailed info available!)

Form Enhancements/Assistance
SPCascadeDropdowns; It allows you to easily set up cascading dropdowns on a list form. (What we mean by cascading dropdowns is the situation where the available options for one column depend on the value you select in another column.) This is not possible with SharePoint OOB components!

SPDisplayRelatedInfo; This function lets you display related information on forms when an option in a dropdown is chosen.

SPLookupAddNew; This function allows you to provide a link in forms for Lookup columns so that the user can add new values to the Lookup list easily.

SPRedirectWithID; This function allows you to redirect to a another page from a new item form with the new item's ID. This allows chaining of forms from item creation onward.

SPSetMultiSelectSizes; Sets the size of the boxes in a multi-select picker based on the values they contain.

SPArrangeChoices; Rearranges radio buttons or checkboxes in a form from vertical to horizontal display to save page real estate.

SPAutocomplete; The SPAutocomplete lets you provide values for a Single line of text column from values in a SharePoint list. The function is highly configurable and can enhance the user experience with forms.

SPUpdateMultipleListItems; SPUpdateMultipleListItems allows you to update multiple items in a list based upon some common characteristic or metadata criteria.

SPFilterDropdown; The SPFilterDropdown function allows you to filter the values available in a Lookup column using CAML against the Lookup column's source list.

SPComplexToSimpleDropdown; Converts a "complex" dropdown (which SharePoint displays if there are 20+ options) to a "simple" dropdown (select).

SPFindPeoplePicker; The SPFindPeoplePicker function helps you find and set People Picker column values.

SPFindMMSPicker; The SPFindMMSPicker function helps you find an MMS Picker's values.

SPGetCurrentSite; This utility function, which is also publicly available, simply returns the current site's URL. It mirrors the functionality of the WebUrlFromPageUrl operation.

SPGetCurrentUser; This function returns information about the current user. It is based on an insightful trick from Einar Otto Stangvik.

SPGetLastItemId; Function to return the ID of the last item created on a list by a specific user. Useful for maintaining parent/child relationships.

SPGetDisplayFromStatic; This function returns the DisplayName for a column based on the StaticName.          

SPGetStaticFromDisplay; This function returns the StaticName for a column based on the DisplayName.

SPScriptAudit; The SPScriptAudit function allows you to run an auditing report showing where scripting is in use in a site.

SPGetQueryString; The SPGetQueryString function returns an array containing the Query String parameters and their values.

SPListNameFromUrl; Returns the current list's GUID *if* called in the context of a list, meaning that the URL is within the list, like /DocLib or /Lists/ListName.

SPFilterNode; Can be used to find namespaced elements in returned XML, such as rs:data or z:row from GetListItems.   

SPXmlToJson; SPXslToJson is a function to convert XML data into JSON for client-side processing.

SPConvertDateToISO; Convert a JavaScript date to the ISO 8601 format required by SharePoint to update list items.

SPGetListItemsJson; SPGetListItemsJson combines several SPServices capabilities into one powerful function. By calling GetListItemChangesSinceToken, parsing the list schema, and passing the resulting mapping and data to SPXmlToJson automagically, we have a one-stop shop for retrieving SharePoint list data in JSON format. No manual mapping required!

SPDropdownCtl; The function finds a dropdown in a form based on the name of the column (either the DisplayName or the StaticName) and returns an object you can use in your own functions.

The solution:
First of all creating a script; Then adding the script to a page where you want to display the page’s properties. This way, users can still edit the page content without messing with the script code. The code can be inserted into a script editor webpart or in a content editor webpart, depending on your situation you can also upload the script to a resource library and link the content editor webpart to your script. This way users won’t see the script at all, you can modify the script on 1 location and without the need to edit your page.

The script:

The script I put together looks like this; I’ve added some comments in there to explain what’s going on:
<!-- Reference jQuery on the Google CDN (content delivery network), if you want to make an external link to the (for this script correct) jquery library -->
<script type="text/javascript" src="//"></script>
<!-- Alternatively Reference jQuery on the locale Site Assets , uncomment to activate and comment out the above one to deactivate the CDN method-->
<!--<script type="text/javascript" src="https://<yoursite>"></script>-->
<!-- Reference SPServices on cdnjs (Cloudflare)  if you want to make an external link to the (for this script correct) SPServices library -->
<script type="text/javascript" src="//"></script>
<!-- Alternatively Reference jQuery on the locale Site Assets, uncomment to activate and comment out the above one to deactivate the CDN method -->
<!--<script type="text/javascript" src=""></script>-->
<script language="javascript" type="text/javascript">
<!—A little helper to format the date like you would like it, in this case.. Dutch formatting-->
function SPConvertDate(t)
     var d = new Date(t.substr(0,4), parseInt(t.substr(5,2)) - 1, t.substr(8,2), t.substr(11,2), t.substr(14,2), t.substr(17,2));

     var month = ((d.getMonth()+1) < 10) ? "0" + (d.getMonth()+1) : (d.getMonth()+1);
     var date = (d.getDate() < 10) ? "0" + d.getDate() : d.getDate();
     var minutes = (d.getMinutes() < 10) ? "0" + d.getMinutes() : d.getMinutes();
     var amPm = (d.getHours() < 12) ? "am" : "pm";

     return date + "-" + month + "-" + d.getFullYear() + " " + d.getHours() + ":" + minutes+amPm;
<!—here starts the main programà
$(document).ready(function() {
<!—here you can populate the only 2 parameters; the list-name where the page resides and the page ID of the page you want to show the modified date for; I’m using the pageID to prevent disfunction of the script if someone decides to rename a page ..à

var ListName = "2AAC79F6-98B3-45D4-BD77-EDE404B99C39"
var PageID = "3"

<!—then we define our query that will filter all pages in the list to the one we would like to show. Of course you can modify this query and even show multiple pages information. Via these methods we can access all properties / metadata from a SharePoint page, you just need to know the column names and ways to extract it; another nice thing is that it’s a query, which is familiar to a lot of people!

var CamlQuery = "<Query><Where><Eq><FieldRef Name='ID'/><Value Type='Number'>" + PageID + "</Value></Eq></Where></Query>";
<!—Then we define which fields (metadata) we would like to extract and show in our page à
var CamlViewFields = "<ViewFields><FieldRef Name='Title' /><FieldRef Name='Modified' /><FieldRef Name='Author' /></ViewFields>";
<!—now let’s build and execute our query à
    operation: "GetListItems",
    async: false,
    listName: ListName,
    CAMLViewFields: CamlViewFields,
    CAMLQuery: CamlQuery,
    completefunc: function (xData, Status) {

<!—we’re getting a XML back, let’s get our data out of it and fill some variables with the dataà
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
                var Author = new String($(this).attr("ows_Author"));
                Author = Author.split(";#")[1]
                ModifiedDate = $(this).attr("ows_Modified")

<!—making a liHtml variable which in this specific case I turned into a DIV type later, if you want to display more than one pages’ info, you might want to construct an <li>info</li> kind of html
        var liHtml = "Latest update:&nbsp;" + SPConvertDate(ModifiedDate) + "&nbsp;<br>By:&nbsp;" + Author;
<!—and then display your html à
<h1>Page properties:</h1><br/>
<div id="tasksUL"/>

Ok, we’ve got our script.. Now what? Let’s insert it into a web part and see if it works, the end result:

First, we add a script editor or content editor web part to the page;

 Then add the script into it and save it.

Now we can add other content to the page, when the page is saved the info updates automatically!

You can download the script here:

Wednesday, June 25, 2014

TomTom Issue: Connecting to your computer

A bit of a different post, not about SharePoint this time !

I finally resolved an issue with my TomTom today and thought to share this solution with the world :)

My problem was that after a few seconds of starting my navigation in the car, it would display this message:
"Connecting to your computer".

And this message would stay there forever, until you click OK. Because this message would pop up after 15 / 40 seconds, you almost always are driving. So it was very annoying to pay attention to it and press the OK button while driving.

I logged a ticket with TomTom support, they suggested to reload the software on it, this didn't help. I decided to do some serious debugging and after a few moments I found the answer... The error / message was caused by plugging the TomTom into the Peugeot's own USB connection and not using the TomTom provided 12v ->USB connector:

Because I was using the Peugeot powered USB, TomTom thought it was connected to a computer; showing me the annoying message ! Using the normal 12v connection and TomTom USB converter that comes with the navigation device, no message; problemo solved :)


Wednesday, June 18, 2014

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 !