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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
timalbers
Advocate V
Advocate V

Power BI REST API - Auto refresh access token

Hi all,


I have built a dataflow which retrieves data from the Power BI REST API.

One of its use cases is to get all users who have access to the reports of our organization. 

 

These are the steps that are executed in the flow to achieve it:

  1. I created a function that returns an access token, which works just fine
  2. The token is being used to call the /reports endpoint in order to return all report IDs
  3. I am using the list of IDs to perform a call for every single report ID using a relative path to the /reports/*ID*/users endpoint to get me the users of each report

Everything worked fine until some day - due to more reports and more users - the flow runtime exceeded 1 hour.

After 1 hour the entire flow will fail because the access token I am retrieving through my query function expires.

 

This made me realize that the logic of the flow I built is not working in the way I thought it was.

I thought - by using a function for retrieving an access token - every single call to an API endpoint will use its own new access token.

This is apparently not the case. The same old token is beeing used for every element in my report ID list.

My question: How can I fix this. Is there a way, to rewrite the function, so that it generates a new key for every request?

 

The logic of my function currently looks like this:

let 
    body = [
        grant_type = "client_credentials",
        resource = "https://analysis.windows.net/powerbi/api",
        client_id = "*********************",
        client_secret = "***********************************",
        scope = "https://analysis.windows.net/powerbi/api/.default"
    ],
    GetJson =
        Json.Document(
            Web.Contents(
                "https://login.microsoftonline.com/***********************/oauth2/token", [
                    Headers = [#"Content-Type" = "application/x-www-form-urlencoded"], 
                    Content = Text.ToBinary(Uri.BuildQueryString(body))
                ]
            )
        ),
    access_token = GetJson[access_token]
in
    access_token

 

Thanks in advance

Tim

8 REPLIES 8
cpwebb
Employee
Employee

There is a limitation at the moment that any single request to a source that uses OAuth2 authentication will fail after an hour because the access token expires. We're working to address that and refresh the token midway through a request but I can't give you a date for when the fix will be ready. The workaround is to make multiple requests that each take less than an hour; if you were using this code to load data into a table in a semantic model this would involve partitioning the table but I'm not sure there's an easy way of doing the same thing for dataflows.

 

That said, this is also a great example of why you need to create a custom connector when connecting to web services that use OAuth2 authentication (see https://blog.crossjoin.co.uk/2021/08/29/connecting-to-rest-apis-with-oauth2-authentication-in-power-...). Apart from the one hour limitation you mention there are lots of other cases where token refresh is needed and support for doing that is only available in custom connectors - see https://learn.microsoft.com/en-us/power-query/handling-authentication#implementing-an-oauth-flow 

Does the latest gateway version On-premises data gateway April 2024 release | Microsoft Power BI Blog | Microsoft Power BI address this specifically? The release notes are cryptic as always.

I've been told that it does, which is good news!

v-jingzhan-msft
Community Support
Community Support

Hi @timalbers 

 

You may try the custom connector suggested by @lbendlin .

 

For the access token question, an access token is generated according to the client and resource of the request. Since the current access token is for the same scope "https://analysis.windows.net/powerbi/api/.default", it won't be regenerated unless it gets expired. It's possible to extend the lifetime of an access token, but this requires some specific Microsoft Entra ID licenses. To learn more about the lifetime, please refer to Access tokens in the Microsoft identity platform - Microsoft identity platform | Microsoft Learn

 

Another alternative is to separate the current dataflow into different dataflows. For example, the first dataflow is to call the /reports endpoint to return all report IDs. The second dataflow references the first dataflow to get all report IDs, then call every single report ID to get report users. You can set different schedule refresh times for these dataflows, e.g. the first dataflow refreshes first, the second dataflow refreshes one or two hours later. This ensures that the second dataflow starts to refresh after the first dataflow's refresh completes and it will get a new access token then. Currently the dataflow also has refresh timeout threshold, so splitting a large dataflow can avoid refresh timeout in the future. 

Understand and optimize dataflows refresh - Power BI | Microsoft Learn

 

Hope this would be helpful.

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

lbendlin
Super User
Super User

I remember why I did not pay much attention to custom connectors...
I need a dataflow. Custom connectors can only be used in Desktop, or am I wrong?

remember that a gateway is a headless desktop.  Custom connectors work on gateways too, if you install the .mez correctly.  Not sure about dataflows in UI mode but it should work with a blank query and code copy/paste.

Yes, the reason is that I simply wasn't aware of this connector, thank you for suggesting it! I will have a look at it.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors