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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
timalbers
Responsive Resident
Responsive Resident

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

11 REPLIES 11
cpwebb
Microsoft Employee
Microsoft 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 

dbeavon3
Impactful Individual
Impactful Individual

@cpwebb 

Hi,

Did you see that dataflows are now supposed to be able to allow "mid-stream" refreshes?

I can't get this working for web API's (via the web connector for PQ).

 

It would help if the PBI team would publish more examples to guide customers thru the struggle of making this work.  It is not self-evident.  There are a variety of subtle handshakes which I believe are expected (eg. replying with WWW-Authenticate header, during a 401).


 

... furthermore, the Power BI gateway has some proprietary stuff going on which makes it harder to investigate why dataflows are failing after an hour.  For example, I don't believe that the local mashup containers ("microsoft.mashup.container.netfx") are able to refresh their own tokens when they expire.  This is how a simple web client would be developed.  Instead, I think there is some sort of delegate - back to the PBI service in Azure, then back down to a different process running in my enterprise gateway ("microsoft.powerbi.enterprisegateway").  That other process is probably the only one that is allowed to generate a new oauth token after expiration.  This whole system seems opaque and fragile, and there seem to be no docs explaining it.

 

Given the new announcement from Nikki about "mid-stream" refreshes for dataflows, I'm hoping I can get them working but it is taking a ton of flailing around.  In my experience it is hard to get REST api's working without trial-and-error....  But the hidden & proprietary interactions between the mashup engine, service, and gateway process are making things all that much more difficult!

 

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.

cpwebb
Microsoft Employee
Microsoft Employee

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.

dbeavon3
Impactful Individual
Impactful Individual

@lbendlin 
Pretty sure that dataflows have never supported custom connectors.  That is sort of a dead end.  

They added it to several release waves in the past, but never delivered.

 

Here is the idea
https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=70ae2adf-2f7f-42e3-9995-2b0f91c975d1

 

The latest tentative ETA is Q1 2025.  But I have zero confidence they will hit that goal, given the many times they promised this on the release waves in the past.

 

dbeavon3_0-1726196795900.png

 

You are right, I had forgotten about that.  Fingers crossed that they will support AAD auth.

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors