Home Power BI How to find all Power BI users in an organisation?

How to find all Power BI users in an organisation?

by Prathy Kamasani

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 🙂

You may also like

10 comments

Krishna Valapala 17 July 2019 - 3:08 pm

What kind of permissions do i need to get the users and groups in Power BI?

Reply
Mark Humphreys 7 June 2019 - 4:18 pm

Hi Prathy,

Do you know if this still works? I get an error within Power BI stating that access to the resource if forbidden. I think this relates to the Graph API.

Do you still run this report?

Any assistance would be greatly appreciated.

Mark

Reply
Mark Humphreys 7 June 2019 - 3:32 pm

Hi There,

I have just come across this blog and I receive the following error when trying to use the Graph API

‘Access to the resource is forbidden’

Does this template still work for you and if so how?

Reply
kumar 13 March 2019 - 3:42 pm

I am getting below error after enter the api , “We couldn’t authenticate with the credentials provided. Please try again”

Reply
stephane 21 February 2018 - 6:01 pm

Hi prathy
i’ve tried to use your power bi template by entering in requested ‘Graph API URL’ & ‘GRAPH API Version’ following parameter values : https://graph.microsoft.com/beta/users & https://graph.microsoft.com/beta/groups as mentioned on your post by using ‘Organizational account’ credentials settings on ODatafeed pop up windows after getting initially connected to my PowerBI service account but i get following error messages appearing after refresh attempts of all included datasets :

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (500) Internal Server Error. (A relative URI cannot be created because the ‘uriString’ parameter represents an absolute URI.)
OData Version: 4, Error: The remote server returned an error: (500) Internal Server Error. (A relative URI cannot be created because the ‘uriString’ parameter represents an absolute URI.)
OData Version: 3, Error: The remote server returned an error: (500) Internal Server Error. (A relative URI cannot be created because the ‘uriString’ parameter represents an absolute URI.)
=> Any idea or suggestions to provide me for troubleshooting this technical OData source resfresh failure ?
Many thanks in advance for your additional explanations and thank you very much for sharing all of these nice templates files and technical explanations from your blog posts.
Regards
Stephane

Reply
Prathy Kamasani 2 March 2018 - 11:07 am

Hi Stephane,

Not sure why you are getting that error. Power bi behaves funny with API’s; I would suggest trying again. Sorry for not being very helpful!

Prathy

Reply
stephane 20 March 2018 - 3:59 pm

Hi prathy
i’ve tried to use your power bi template by entering in requested ‘Graph API URL’ & ‘GRAPH API Version’ following parameter values : https://graph.microsoft.com/beta/users & https://graph.microsoft.com/beta/groups as mentioned on your post by using ‘Organizational account’ credentials settings on ODatafeed pop up windows after getting initially connected to my PowerBI service account but i get following error messages appearing after refresh attempts of all included datasets :

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (500) Internal Server Error. (A relative URI cannot be created because the ‘uriString’ parameter represents an absolute URI.)
OData Version: 4, Error: The remote server returned an error: (500) Internal Server Error. (A relative URI cannot be created because the ‘uriString’ parameter represents an absolute URI.)
OData Version: 3, Error: The remote server returned an error: (500) Internal Server Error. (A relative URI cannot be created because the ‘uriString’ parameter represents an absolute URI.)
=> Any idea or suggestions to provide me for troubleshooting this technical OData source resfresh failure ?
Many thanks in advance for your additional explanations and thank you very much for sharing all of these nice templates files and technical explanations from your blog posts.
Regards
Stephane

Reply
Prathy Kamasani 20 March 2018 - 3:59 pm

Hi Stephane,

Not sure why you are getting that error. Power bi behaves funny with API’s; I would suggest trying again. Sorry for not being very helpful!

Prathy

Reply
Ivan Bondarenko 23 November 2017 - 4:39 am

Awesome! Thanks for sharing!

Reply
Ivan Bondarenko 20 March 2018 - 3:59 pm

Awesome! Thanks for sharing!

Reply

Leave a Reply