Repetitive queries to web API from PBI causing code 500 response

[PatSo]

Hi,

I'm quite new in the world of PowerBI and Power Query. I've started my journey four month ago, so many things for me are just new and unknown. There is one thing that I can't find solution for (information on the subject seems to be very scarce).

I'm trying to utilize the PBI connector I've made to one of our services' API. The connector itself is rather simple as there are very few endpoints in the API.

The API has token-based authentication with 10 min lifespan. I have to pass credentials and retrieve the token from webservice.

During creation of this connector I thought it would be a good idea to create a "global scope" variable holding token and assign function call to it, so that I could reference this variable in every endpoint call rather than querying token endpoint each time.

The whole thing works perfectly only when I query one endpoint from PBI. As soon as I add another one I am getting response 500 from the API during data refresh in PBI.

Here is a fragment of my connector code:

It turns out that even though I'm trying to hold token value in a variable (first few lines of code on a screenshot above), the whole function is being called every time I reference this variable (actually, the Feed function references it every time it's being called but the idea is the same).

I've run few tests with Fiddler caputring all the traffic to/from API.

Here is how it looks like when I refresh data with only one entity in the model:

What's interesting in above example, is that there are two calls to "departments" endpoint (and two calls to "login" endpoint - the token granting one). For the "departments" endpoint I've used same approach as for tokens: "global" variable calling GetDepartmentsTable function; everywhere I need (navigation table, GetCountriesTable function) the "departments" data, I reference the variable.

Anyway, it works just fine in both PBI desktop and service.

If only I add another entity to the model, I'm getting an error. In the below example I've added "Countries" entity which is actually made of data existing in "departments" table:

Now, this time the "login" endpoint is being queried three times. I assume that the two of three calls are being made simultaneously and thats why I'm getting response 500 from the web server.

(I'm sorry for repeating myself here and there but I want to be clear and to outline my problem properly)

So here come my questions:

1. Have anyone stumbled upon similar problem?
2. Is there any way to "really" store function output in a variable without calling it everytime the variable is being referenced?
3. Is there any way I could force PBI to refresh data in a series rather than in parallel?

Any suggestions, tips, workarounds are welcome. Maybe I am just missing something in here?

[artemus]

Have you tried setting the Type to Singleton in your DataSource.Kind record?

Type = "Singleton"

Also, the function:

Diagnostics.ActivityId()

Should always give you the same guid every time you call it for a single refresh.

The other option is to use an Oauth workflow