Forum Discussion
Skilled SharerPower BI REST API - Auto refresh access token
Microsoft EmployeeThere 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-query-power-bi/). 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
Memorable Membercpwebb
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!