Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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"
Solved! Go to 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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
you are missing a space after Bearer.
I still have the same error when I use invoked function for the report users as admin.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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!
@ImkeF would you mind showing how the authentication would work in Power Automate? Or would you need to register an app?
Hi @lbendlin ,
yes, of course, you would have to register an app to access this data.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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).
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.
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!
You can clone the repository and add your own code. Maybe even submit a PR for them to consider.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
55 | |
43 | |
28 | |
22 |