GetDatasetUsersAsAdmin - Too Many Requests

[saunders]

Hi all,

I'm trying to get all user access rights for datasets in our tenant via Power Query and PBI REST API but I get the following error below:
Error: Data Source Error : DataSource.Error: Web.Contents failed to get contents from 'https://api.powerbi.com/v1.0/myorg/admin/datasets/<datasetid>/users' (429): Too Many Requests

We only have about 2000 datasets in our tenant.

Any help much appreciated!

My code is below:

let
  // Get access token
  body = "grant_type=password&resource=https://analysis.windows.net/powerbi/api&client_id=<client id>&username=<user name>&password=<password>,
  Data = Json.Document(Web.Contents("https://login.microsoftonline.com/common/oauth2/token/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])),
  access_token = Data[access_token],

  // Get dataset users function
  GetUsers = (datasetId as text) =>
  let
      Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/",
      [
      RelativePath = "admin/datasets/" & datasetId & "/users",
      Headers=[Authorization="Bearer " & access_token]
      ] )),
      value = Source[value]
  in
      value,

  // Main query
  // Get datasets
  Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/",
        [ 
            RelativePath = "admin/datasets",
            Headers=[Authorization="Bearer " & access_token]
        ] )),
  value = Source[value],
  #"Converted to table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to table", "Column1", {"id", "name", "addRowsAPIEnabled", "configuredBy", "isRefreshable", "isEffectiveIdentityRequired", "isEffectiveIdentityRolesRequired", "targetStorageMode", "createReportEmbedURL", "qnaEmbedURL", "upstreamDatasets", "schemaMayNotBeUpToDate", "users"}, {"id", "name", "addRowsAPIEnabled", "configuredBy", "isRefreshable", "isEffectiveIdentityRequired", "isEffectiveIdentityRolesRequired", "targetStorageMode", "createReportEmbedURL", "qnaEmbedURL", "upstreamDatasets", "schemaMayNotBeUpToDate", "users"}),
  #"Removed columns" = Table.RemoveColumns(#"Expanded Column1", {"qnaEmbedURL", "createReportEmbedURL", "upstreamDatasets", "users"}),
  #"Invoked custom function" = Table.AddColumn(#"Removed columns", "Invoked custom function", each GetUsers([id])),
  #"Expanded Invoked custom function" = Table.ExpandListColumn(#"Invoked custom function", "Invoked custom function"),
  #"Expanded Invoked custom function 1" = Table.ExpandRecordColumn(#"Expanded Invoked custom function", "Invoked custom function", {"datasetUserAccessRight", "emailAddress", "displayName", "identifier", "graphId", "principalType"}, {"datasetUserAccessRight", "emailAddress", "displayName", "identifier", "graphId", "principalType"})
in
  #"Expanded Invoked custom function 1"

[Anonymous]

Hello 

Could someone help me with this error.  

I need to have a list of the GettusersreportsasAdmin,GettusersdashboardasAdmin

The code for the getusersreportsasmin in my querry view is : (ReportId as any) => let
Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/",
[
RelativePath = "admin/reports/" & ReportId & "/users",
Headers = [Authorization= "Bearer" & #"Get Acces Token"()]
])),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"reportUserAccessRight
", "emailAddress", "displayName", "identifier", "graphId", "principalType", "userType"}, {"reportUserAccessRight", "emailAddress", "displayName", "identifier", "graphId", "principalType", "userType"})
in
#"Expanded Column1"

[lbendlin]

you are missing a space after Bearer.

[Anonymous]

I  still have the same error when I use invoked function for the report users as admin.

[ImkeF · Accepted solution]

Hi @saunders ,

the API doc states a max. of 200 requests per hour. So you'd have to split it up into 10 chunks to retrieve those values: Admin - Datasets GetDatasetUsersAsAdmin - REST API (Power BI Power BI REST APIs) | Microsoft Docs
You might consider using Power Automate for it instead, so you can export the results of the chunks into a central location. Setting up a custom connector in there is also much easier than in Power Query.

[saunders]

Hi Imke, just to follow up -

I'm using the same Power Query code to return the report users and that query is successful.  The number of reports in our tenant is 2,367 and the number of datsets is 1,959.  Very strange that the query works with reports but not with datasets.  Querying the GetDatasetUsersAsAdmin results in Too Many Requests.  

I'll log a ticket with MS support so they can investigate further.

Thanks.

[saunders]

Thanks Imke, I missed that API limit in the doc. So if I have 2000 datasets, my code will try to query the endpoint 2000 times because it sends a query for each dataset id. Obviously with a limit of 200 requests per hour, this will take 10 hours if my code waits in between each request.

Do you know how I can restructure my code to avoid making a call to the API for each and every dataset id?  I'm not even sure how I would do this is in Python or Power Automate because surely the code still needs to make a call for every dataset id.

In any case thanks again!

[lbendlin]

@ImkeF would you mind showing how the authentication would work in Power Automate? Or would you need to register an app?

[ImkeF]

Hi @lbendlin ,
yes, of course, you would have to register an app to access this data.

[lbendlin]

That's the beauty of Miguel's solution - no app required. Of course with the drawback that AAD is not supported on the PowerBI service (as far as I know? - would be happy to be proven wrong).

[lbendlin]

If your org supports AAD you might be much better off using the Power BI Rest API custom connector.  Works very well for us, at least on the desktop.

GitHub - migueesc123/PowerBIRESTAPI: A Microsoft Power BI Data Connector or Power Query Connector fo...

[saunders]

Thanks for the suggestion, I didn't know about that custom connector.  It doesn't have the endpoint I need yet but I think it will be useful.  Thanks again!

[lbendlin]

You can clone the repository and add your own code. Maybe even submit a PR for them to consider.