Pull data fromAPI with token from multiple query, each one invalidate each other's token

[Anonymous]

I have a dataflow with multiple queries ( i have 8 queries) that are getting data from a third party API.

Each query call the get_token() function at the beggining and stores it in a variable to use in the mulitple calls. But each API call to get the token, invalidates the previous token. Since the api call returns multiple pages, each query will call the api multiple time and thus the stored token within the query will be invalid because the other query will have generated a new token. (It's my understanding that those query are run simultaneously when ran manually or on schedule)

When the scheduled refresh of the dataflow happens (only showing 2 query instead of 8 to simplify)

1. query1 call get_token() and stores it 
2. query1make api request, API return results page one
3. query2 call get_token() and stores it 
4. query2make api request, API return results page one
5. query1 make api request for results page2, doesn't work because authentication failed

I have a function that gets the token

() =>
let
    url = "xxxxxxxxx",  //url to get the token
    headers = [#"accept" = "application/json",#"Content-Type" = "application/json"],
    postdata = Json.FromValue(
      [
          ident = "xxxx",
          username = "xxxx@xxxx",
          password = "xxxxxxx"
      ]
    ),
    response = Web.Contents(url, [Headers = headers, Content = postdata]), //adding a Web.Contents call switches the https request from GET to POST
    Data = Json.Document(response),
    token = Data[accessToken]
  in
    token

If I run query1 on schedule alone without query2, it works perfectly. 

If I run query2 on schedule alone without query1, it works perfectly. 

 Yes, it would be possible to call the api to get the token before calling next page, but that seems like a bad way and I would think that a race condition could occur and one query would invalidate another one.

I couldn't find anything regarding creating a global variable that every query can refer to instead of calling the function and generating a new token. Is there a way to have a function call the api only once, then on every other time that function is called, the token is return from a variable?

Thank you. 

[Anonymous · Accepted solution]

For clarity, I will write the full solution here

 @aj1973 provided a clue to get to my solution. in this link https://www.youtube.com/watch?v=2RZkc_qrV1g&t=610s

What I need was :

• get a access_token to be generated once and stored in order to be used by the other queries.
• do not expose that token outside of those queries

What I could not do :

• have a function that request the token and call that function in each subsequent queries because each call would generate a new accesstoken and invalidate the previous one.

Solution : 

• create a queries that results in a 1 row, 1 column table with the accesstoken.
• Uncheck the "enable load" on that query 
  • 

  •  this prevent this token to be accessible outside the queries and will not be accessible by anyone consulting the resulting data

  • Doesn't required premium, if you leave it check, it will be consider a premium feature
• Then in each of the query that needs that token, you can get that token like this: Table.FirstValue(get_tokenQuery)
  Each time a query ask for that value from that table, if that table is empty, the get_tokenQuery will run and then return the result, if the get_tokenQuery has already ran, it will simply return the token but will not generate a new token. Thus all the query can run simultaneously with the same token

Success.

[aj1973]

Hi @Anonymous 

Please watch this video, it will help you out

https://www.youtube.com/watch?v=2RZkc_qrV1g&t=610s

[Anonymous]

Hi,

Thank you for your example.

from that video I get that he is getting all his data in one simple query and gets multiples table. That is not the case for me. All of those query can not be returned via a simple api call. They each needs multiples call with different parameters. I could include all of those api calls of my 8 queries sequentially within a MASTER  query and create one table for each of those results and add them into their own column, then refer to them in the next queries, one column per query and expand them. That would make the first query very hard to troubleshoot. 

I could also call the api and create a table with the access token. But I don't like having the accesstoken stored inside a table. It feels like an unecessary risk. By adding

get_token2 = Table.FirstValue(get_tokenQuery),

it states "Computed tables require Premium to refresh. To enable refresh, upgrade this workspace to Premium capacity"

EDIT : Nevermind about the 'computed tables requites premium..." msg. I removed the "load" from the query and it's gone now.

Did I miss something?

[aj1973]

Hi @Anonymous 

We are talking about Power BI REST APIs that you are trying to use, aren't they?

If so then it would be better to an App Registration, that you won't need tokens to excute your APIs 

[Anonymous]

No, it is not regarding the powerbi API.  this is an third party API . I will add this information to my question.

(did I post this in the wrong part of the forum?)

[aj1973]

Ok I see. sorry i can't be in any help for you.

I let the community take care of you.