API with dynamic access token can't refresh in Power BI service

[emilyh]

Hello,

I have a report needs to get data from a REST API. To access the API, I need to first generate a access token and the token will be expired every 1 hour. I've searched many materials and was able to write a power query in Power BI desktop to generate the dynamic token and then refresh the dataset. However, after publishing the report to Power BI service, it won't allow me to refresh the dataset by schedule. Some of the posts say that we need to build a custom connector to make it work in Power BI service. 

Does anyone has idea is there some other way to make it work?

Thanks.

[peggysue · Accepted solution]

I realize this is an old thread and I'm not addressing the original question. But I finally found a solution to the expiring access token problem so I'm hoping I can help someone else.

In a Power BI dataflow, I'm calling a REST API that uses a Bearer token for authentication. The token expires in 2 hours. My dataflow refresh would run fine for 2 hours, and then fail with bad credentials. I was trying to call for a fresh access token every time, but it didn't work -- Power BI always seemed to use the original token, because it failed after exactly 2 hours.

Now it all finally makes sense to me after reading this excellent post: https://blog.crossjoin.co.uk/2017/01/06/web-contents-caching-and-the-excludedfromcachekey-option-in-....  It explains that "When you’re using the Web.Contents() M function to call a web service from Power Query or Power BI, you don’t necessarily get one HTTP request each time you call the function: some caching takes place, so that if you make the same request multiple times your query won’t waste time asking for the same data over and over."

So Power BI was trying to be helpful and caching the result of my first token call and reusing it the whole time. I thought that sending the same request over and over would give me new tokens, but Power BI said "oh, I see that you already made that request, and I already have the result, so here you go" ... OLD TOKEN!

Luckily, the fix is SO easy. Just send another made-up header into your token request so Power BI thinks you're asking for something different. For me, I sent in the hour of the day, so I knew that Power BI would go get me a new token every hour. It works!

Here's a snippet showing what I changed. I just made up a header called "VariableHeader" (you could name it whatever you want, the API ignores it), and gave it the hour of the day.

[apdams]

I can auto-schedule a report in PowerBI Service that uses PowerBI Gateway to get data from a custom connector. The auto-scheduling of this report wroks as long as the initial Auth0 SPA token hasn't expiered (24 hours in my case). If it has expired, the service/gateway/??? doesn't refresh the token, even though the custom connector has a refreshtoken method.
I have tried your suggestion and put that "variableheader" into the Headers element, but it doesn't change the behaviour. the token is not being refreshed and powerBI service will ony be able to regen the report after someone manually authenticates again using PowerBI

[apdams]

The solution for my problem ended up being very simple. All you need to do is add "offline_access" as a scope in your refresh token Auth call to Auth0 

[apdams]

I have a further problem. The fix I refer to above sorted out my token refresh issue (sort of). It does refresh the token, and the token refreshes successfully for 6 days, then it fails. Digging into our Auth0 server, I see that the associated RefreshToken expires in 7 days. So it seems to me that the refresh token is not being rotated but instead is reused for 6 days and then expires, so the refresh on the 7th day then fails

This is all a bit confusing, seeing as all the checkboxes are checked in Auth0 to rotate the refresh token, so I don't really know why it isn't being rotated but instead expires after 6 days.

Does anyone know how to properly enable RefreshToken rotation?

[Quebas_2]

Did you find a solution?

[Anonymous]

If you have Power BI Premium license, we will be able to do the schedule refresh without issues.

[Anonymous]

Hi All.. I also have the same problem. Does any one know how to fix this?

[Anonymous]

Hi @emilyh ,

Please refer to the following documents to see if it helps you.

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

https://community.powerbi.com/t5/Service/Airtable-Power-Bi-Service-Dynamic-Source-Refresh-Error/m-p/...

If it does not solve your problem, please provide a screenshot on it.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[emilyh]

Hi @Anonymous ,

I tried the solution you provided from the links, but did not work for my scenario. I need to access to a API with a dynamic access token which need to be refreshed every one hour. The access token is part of the header.

if I put the authorization part in a query, the authentication did not work.

it works using header, but it won't allow me to refresh in Power Bi service using this way.

[Anonymous]

@emilyh , Where you able to find a solution for this, even I do have the same problem.

[Quebas9]

Did any of you solve the problem? Facing the same.

[scuche]

Hi @Anonymous 

I have the same problem. Is there any solution for that?

Thanks