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

[GrantBrunton]

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?

[Anonymous]

I have a similar issue, in that I am using a bunch of APIs to create tables in M (origianlly broke my queries into main API URL, and relative paths and parametes, but was forced to recomibne them back into one string) (also, had to rebuild all queries into "base" and the "reference" becuase of the issue you are having: doing merges off of one big query to the API caused table loading issues), and my API M queries work in PBIX, but when I upload to PBIS to schedule automatic refresh, half of the APIs dont work. MSFT Power BI has issues with API protocols in PBIS and in Data Flows. Queries that work fine on desktop PBIX do not work in PBIS. We are using anonymous connection, and passing a token). But not all of our queries are working in PBIS if they use an API. It would be nice if MSFT would publish some best practices and address the issue of using APIs with Power BI.

[v-juanli-msft]

Hi @GrantBrunton

Please refer to this blog

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

Best Regards

maggie

[GrantBrunton]

You mean the article I referenced in the first post @v-juanli-msft? Did you actually read my post or just the title?

[GrantBrunton]

In addition, I have tested embedding the token query into the table queries and that does indeed make the problem disappear at the cost of doubling my API calls. Just need a way to make it work without doing that...