API Token: Query references other queries, so it may not directly access a data source.

There are several cases of this particular issue plaguing Power BI users which appears to be caused by an external data source being merged with an internal query.

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

This blog article is usually referred to as the solution, which involves separating your external data source into its own query and then merging the two internal queries.

But what do you do when you need the result of the internal query to generate the data from your external data source?

So, what am I doing?

1. I have a Token Query that generates an authentication token by passing login credentions to the login api endpoint
2. There are a series of Table Queries that use the result of the Token Query as the authorization header that is sent back to the api generating the tables

Token Query:

let
    loginUrl = "http://myapisite.com",
    loginData = [
      email=Username,
      password=Password
    ],
    loginOptions = [
      Headers = [#"Content-Type"="application/json",#"Accept"="application/json"],
      RelativePath = "api/Users/login",
      Content = Json.FromValue(loginData)
    ],
    Token = Json.Document(Web.Contents(loginUrl, loginOptions),65001)[id]
in
    Token

Example Table Query:

let
    apiUrl = "http://myapisite.com",
    options = [
        Headers = [#"Authorization"=Token],
        RelativePath = "api/Datapoint",
        Query = [filter="{""where"":{""DataType"":""MyData""}}"]
    ],
    apiData = Json.Document(Web.Contents(apiUrl , options),65001),
    #"Converted to Table" = Table.FromList(apiData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
in
    #"Converted to Table"

Of course, I can't separate my external data source into a different query to merge as I need the internal Token query to generate the external data.

I figure I can solve this by embedding the Token Query into each of the Table Queries so it is not generated separately but I have a lot of tables being created and this means that I will be doubling the amount of API calls by requesting a new token for each table which is not very effiecient.

The weird thing is, in my Power BI Desktop app where I created this pbix file everything works perfectly fine and I have no errors refreshing the data. But if anyone else opens the same pbix file in their Power BI Desktop app then they get the below error for every data table that tries to load.

Formula.Firewall: Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Any ideas how I can solve this issue without requesting a new token for each table?