How to find all Power BI users in an organisation?

If you are the goto person for Power BI in your organisation, then I am sure at least once you may have been asked about Power BI users question, I get asked many times. Of course, I can go to Power BI Service, check each workspace and Power BI Apps and see who has access to what; not a productive way when I am working with many workspaces. Or as I blogged before, I can use Power BI API to get all users data, probably a better way when I am working with many workspaces. If I share content using Power BI Apps and given access using security group, It gets difficult to understand who has access to what, who is a member of what groups.

If you have enough rights, you can view users information and members of which groups in Office 365 portal, again not a very productive way.  However, by using Power BI and Graph API, we can create a report to show all users information. I will be able to see all users who have Power BI license. I can slice this data by Location, Team and many more. It’s not only Power BI; I can view all other licenses. For example, Power Apps and Microsoft Flow. Now I can easily see who has access and monitor users and license, If you want to know about Graph API and how to use it with Power BI, check out my earlier blog post – Access Microsoft Graph API using Power BI.

The Template:

By using Microsoft Graph API, I created a template, where you can see all users, their groups and their license details. You can download the template here – https://1drv.ms/u/s!Avm7gbgZtlMlqB12UF4WXFGzzgXk .

Using template:

When you open the Pbit file, you will see a pop up window with two parameters.

Enter these values Graph API URL = https://graph.microsoft.com/  and Graph API  Version =  beta. After a while, based on size, the template should get populated with data.  If you want to understand the logic behind the template, carry on reading …

Using Graph API to retrieve Users, Groups and SKU’s data:

Query Editor:

As I mentioned in my earlier blog, I am using OData source pointing Graph API with Organizational authorisation. I created two parameters called Graph API URL – https://graph.microsoft.com/  and Graph API  Version –  beta

I used two API methods to get the information I needed:

• https://graph.microsoft.com/beta/users
• https://graph.microsoft.com/beta/groups

Based on these, I created a couple of queries.

Users:

let
    Source = OData.Feed(#"Graph API URL" & #"Graph API Version" & "/users?$select=id, assignedLicenses, companyName, country, department, displayName, employeeId, givenName, jobTitle, mail, onPremisesDomainName, onPremisesSamAccountName, officeLocation, postalCode, state, streetAddress, surname, usageLocation, userType, memberOf, licenseDetails"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"id", "assignedLicenses", "companyName", "country", "department", "displayName", "employeeId", "givenName", "jobTitle", "mail", "onPremisesDomainName", "onPremisesSamAccountName", "officeLocation", "postalCode", "state", "streetAddress", "surname", "usageLocation", "userType", "memberOf", "licenseDetails"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Removed Other Columns",{"id", "assignedLicenses", "companyName", "department", "displayName", "jobTitle", "mail", "officeLocation", "memberOf", "licenseDetails"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"displayName", "User name"}, {"officeLocation", "Office location"}, {"department", "Department"}, {"jobTitle", "Job title"}})
in
    #"Renamed Columns"

User license details:

Using Users method, you get a lot of information about users, obviously depends upon your access level and information available. You can expand to License Details from Users, but I was getting an error, I think it was because of query folding with OData Source. Instead of expanding Users, I referenced Users query as a new query, filtered out empty rows then duplicated the column which has License information. When I duplicate column, I don’t get any error with expanding column. (Something to look in more detail at some other time)

let
    Source = Users,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"id", "licenseDetails"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.RowCount([licenseDetails])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> 0)),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "licenseDetails", "licenseDetails - Copy"),
    #"Expanded licenseDetails - Copy" = Table.ExpandTableColumn(#"Duplicated Column", "licenseDetails - Copy", {"id", "servicePlans", "skuId", "skuPartNumber"}, {"id.1", "servicePlans", "skuId", "skuPartNumber"}),
    #"Expanded servicePlans" = Table.ExpandListColumn(#"Expanded licenseDetails - Copy", "servicePlans"),
    #"Expanded servicePlans1" = Table.ExpandRecordColumn(#"Expanded servicePlans", "servicePlans", {"servicePlanId", "servicePlanName", "provisioningStatus", "appliesTo"}, {"servicePlanId", "servicePlanName", "provisioningStatus", "appliesTo"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded servicePlans1",{{"id.1", "SKU ID"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"licenseDetails", "Custom"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"skuPartNumber", "SKU Name"}, {"servicePlanName", "Service Plan Name"}, {"provisioningStatus", "Status"}})
in
    #"Renamed Columns1"

Users Group Details:

Then I imported Groups; I can use groups data from Member Of field from Users API. But I was getting some errors, to avoid those, I used Groups API.

let
    Source = OData.Feed(#"Graph API URL" & #"Graph API Version" & "/groups?$select=id, description, displayName, members, createdOnBehalfOf, owners"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"id", "description", "displayName", "members", "createdOnBehalfOf", "owners"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Other Columns", "members", "members - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "createdOnBehalfOf", "createdOnBehalfOf - Copy"),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Duplicated Column1", "owners", "owners - Copy"),
    #"Expanded createdOnBehalfOf - Copy" = Table.ExpandRecordColumn(#"Duplicated Column2", "createdOnBehalfOf - Copy", {"id", "deletedDateTime"}, {"CreatedOnBehalfOf.id", "CreatedOnBehalfOf.deletedDateTime"}),
    #"Expanded owners - Copy" = Table.ExpandTableColumn(#"Expanded createdOnBehalfOf - Copy", "owners - Copy", {"id", "deletedDateTime"}, {"Owners.id", "Owners.deletedDateTime"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded owners - Copy", {{"CreatedOnBehalfOf.id", null}, {"CreatedOnBehalfOf.deletedDateTime", null}, {"Owners.id", null}, {"Owners.deletedDateTime", null}}),
    #"Expanded members - Copy" = Table.ExpandTableColumn(#"Replaced Errors", "members - Copy", {"id", "deletedDateTime"}, {"id.1", "deletedDateTime"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded members - Copy",{{"id", "Group ID"}, {"id.1", "id"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"members", "createdOnBehalfOf", "owners"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"displayName", "Group Name"}})
in
    #"Renamed Columns1"

Then, I created another query with just ID column so I can create a Model with relationships.

ID = let
    Source = Users,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"id"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

Report design:

In Report, I went for four colours theme; I created four images which I used as background images. I also used Power BI Bookmarks feature to get check box effect.

Summary:

This model enables me to get an overview of Power BI Users. I can share this with key people in different teams to see only their groups, so they can see who are part of what group. If someone can not access reports, they can go, look for those users and see if they are part of the relevant security group or not. Works like a charm, when one user has several accounts and IT gave access to one than the other one.

It is kinda slow when you open the Model in the Power BI desktop the very first time. It was pretty quick on my personal account. Obviously, my account has less data. I am not sure exactly what was impacting the performance on Power BI Desktop, probably it was something related to lazy nature of M language and persistent caching.  I am using OData query parameters too, but that’s not helping much with performance. I will update the post if I find any solution. However, this works fine and pretty quick on Power BI Service. When I scheduled on PBI service, it was taking less than 2 min with my personal account.

Power BI Users template – https://1drv.ms/u/s!Avm7gbgZtlMlqB12UF4WXFGzzgXk .

Until next time,

Prathy 🙂