Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
saunders
Helper II
Helper II

GetDatasetUsersAsAdmin - 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"

 

 

 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

Hello 

Could someone help me with this error.  

I need to have a list of the GettusersreportsasAdmin,GettusersdashboardasAdmin

acmilanboy_0-1644311384436.png

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.

Anonymous
Not applicable

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

acmilanboy_1-1644351724577.png

 

 

acmilanboy_0-1644351504923.png

 

 

ImkeF
Super User
Super User

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).

lbendlin
Super User
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.

 

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors