Forum Discussion
Helper IIGetDatasetUsersAsAdmin - Too Many Requests
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"
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.
12 Replies
- ImkeF
Community Champion
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.
saundersHi 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
Super User
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.
Hello
Could someone help me with this error.
I need to have a list of the GettusersreportsasAdmin,GettusersdashboardasAdminThe 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.
