Store and reuse bearer token for multiple subsequent queries as long as it's still valid

[mipopa]

Hi experts,

For my PowerBI report, I have built a query that gets data from a JSON API.

The API requires a bearer token that must be obtained from a specific token service endpoint of the API.

So far everything works fine: I am able to authenticate to the first API to get my Bearer token, then I use the token in my subsequent request to the actual API resources.

However there is a problem: the token service endpoint has a throttling which limits how frequently I can ask for new Bearer tokens, because the organization owning the API considers that the token they issue should be reused (as it's valid for 24 hours).

The way I have implemented all this in PowerQuery is pretty straightforward. The example I followed is this one: https://youtu.be/2RZkc_qrV1g?t=733

In a nutshell, I defined a PowerQuery function (say "GetBearerToken") that calls the token service endpoint, authenticates and then returns the bearer token. I call this PowerQuery function as a first step inside my other queries. I manage to get the actual data from the resource API endpoints by passing the obtained token into the Authorization header of Web.Contents().

Eventually, all my queries that I defined call my function "GetBearerToken" to first obtain the bearer token, which causes the token value to change for each API call to the resource API endpoint. The first few queries manage to retrieve the data successfully, but the others can't retrieve data any more, because of the throttling I mentioned above.

The best practices nowadays are to reuse the obtained token (especially because it's valid for 24 hours).

My quetion to the expert community is how I can store the token in PowerQuery (either as a global variable, is anything like that exists, or inside a parameter) so that multiple queries can reuse it.

I am ready to modify my function GetBearerToken so that it first checks if the token is expired before asking for a new one, but I need to know how to store the token persistently.

Thanks

[parry2k]

@mipopa Great question, one way could be to create a dataflow and call getaccesstoken in that dataflow and refresh this once a day, and then in the Power BI report, get a token using this dataflow and then further use in your queries. This will get you to make a call to get a token once a day.

Let me know how it goes in case you end up using this solution.