Part 2 – Accessing SharePoint User Profile Data with Power BI

christineAdministrator Guides, Power BI, SharePointLeave a Comment

You can get some really cool analytics by on your organization by combining custom user properties in the SharePoint profile with Power BI. See this post for how to create your custom properties and push them to the user list (this is the list Power BI will connect to). To get better “buckets” of data, you will want to limit your users to values in a closed term set for “squishy” properties like skills or pre-set ones like schools that you can import directly from CSV. Open term sets get really cluttery really fast.

Connecting to the Data

Once you have your custom user properties created and replicated in the user list (this takes forever by the way, mine took 2+ weeks to replicate initially), connect to that user list in Power BI desktop. The way I like to do this is to connect to the data feed for the main site collection here (use the data type OData):

https://yourtenant.sharepoint.com/_vti_bin/listdata.svc

Then select the list “UserInformationList” and load the data. You can remove some of the junky columns from the query at this point if you want.

user information list power bi

Something you may notice is that the user list has quite a few non-people accounts – service accounts, test accounts, etc. You can remove these for the most part by removing users with a null Department value (right click the column, uncheck the null box in the list). We also use a custom property for “staff type” (contractor, PTSE, vendor, offboarded, etc) that is pushed to this user list from the profile, updated with PowerShell – it’s handy for both removing non-people here and from the people search. Offboarding is especially useful as “blocked” users still show up in the people search & user list – it allows you to remove former staff easily.

Here’s a snippet of some of the custom properties surfaced by Power BI from the user list: note there is some weird duplication going on and we need to do something about the multi-value columns.

power bi sharepoint profiles custom properties

How to split the values out of the multi-value columns

The first thing I do is duplicate the query (rename it afterwards by right clicking and clicking properties):

duplicate-query

Then remove all columns except the one I’m trying to split and the staff name column (this will be the key for the data relationship), and filter to remove null values.

Next split the column by “custom” and use “; ” as the delimiter (note the space after the semicolon). Enter the max number of values you can expect as the number of columns.

split column multi-value

split column properties

Now unpivot!

Select your split columns and right click -> unpivot. You may get an extra junk column added in the process, you can remove it. At this point you may notice that there are some duplicate rows – this seems to happen for me for all multi-value columns pushed to the user list (it’s like this in the list itself, too, not just Power BI). I submitted a support ticket for this, they said the fix might take them six months because it was so integrated into the platform, so I just remove the duplicates with Power BI and call it good.

Remove the duplicates

Select both the property column and the name column and right click -> remove duplicates. Make sure to select both columns or it’ll remove all but one of the unpivoted rows.

remove duplicates

Wrap it up

The name column is shared by both your new table and your original table, so Power BI automatically creates a relationship – super cool! I repeat the process above for all of my multi-value properties.

The relationship means Power BI knows your people in your certifications table are the same people from the main user list, which are the same people from the skills table… meaning your charts and tables can interact (clicking one can filter another, etc).

Here’s an example where I appended the tables for two custom properties – “Wants to Learn” and “Professional Skills.” Users can filter by department and click the skills to see who has the skill and who wants to learn it (filters not shown).

staffprof-skills

 

Scheduled refresh is free for SharePoint Online data.

Leave a Reply

Your email address will not be published. Required fields are marked *