Query references other queries or steps, so it may not directly access a data source

Advocate IV

3 years ago

For anyone that might still be listening in on this, or stumbles on it anew, it may not be the privacy setting.

I had the same issue a few weeks ago. Each time like the original poster, I accessed Power Query, did nothing, closed and applied, and it resolved itself.

Yesterday, I looked up this forum post, changed the privacy setting and it resolved the issue.

A bug got in my brain though, and reflecting on the error message, it just made no sense. Why not through a privacy error instead?

Taking this apart piece by piece, I discovered my using a separate query to retrieve only the latest report from a folder created the issue.

My original query gets all reports from a folder.

My second query gets only the latest report from that same folder.

When I created a second query, I copy/pasted the original query in there and modified the second query to only get the latest report from that folder. It worked and I thought nothing of it until this error began popping.

Today, rebuilding that "second" query from ground zero appears to have resolved the issue.

Again, oddly enough, "Ignoring" in the privacy setting also resolved the issue.

What exactly is going on here?

Bilbo_777

Advocate I

2 years ago

I agree, it's bizarre behaviour - I see it too. The refresh gives the error "Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination." You then go into Transform, a click through the queries sometimes have a ? or a warning ! against them (see pic) and allow the preview to refresh. Then close and apply and try the refresh again and it works. No code changes, just touching the tables or poking a refresh on them individually. Could it be something to do with parallelization or order the refreshes happen in, can you enforce a run-order ?

Anonymous

Not applicable

2 years ago

I had two steps, generate API token, and query using said token

Turns out the error was BI being unable to use a reference to the token query result inside the subsequent API queries.

Once I put the API token generation inside each API calling query, it worked. Took me a few days to figure out

Could optimise this code so it's not making a token for each page, but as follows is working code for MS graph list of users (stuff with hashes are params which can be referenced😞

let
    makeToken = (#"Azure Graph API Url" as any, #"Azure Tenant ID" as any, #"Azure Application ID" as any, #"Azure Application Client Secret" as any) => let
        loginURL = "https://login.microsoftonline.com/",
        TokenUri = #"Azure Tenant ID" & "/oauth2/token", // which domain is this a token for
        ResourceId = #"Azure Graph API Url", // where is this token for
        TokenResponse = Json.Document(
            Web.Contents(
                loginURL, [
                    RelativePath = TokenUri, 
                    Content = Text.ToBinary(
                        Uri.BuildQueryString([
                            client_id = #"Azure Application ID", 
                            resource = ResourceId, 
                            grant_type = "client_credentials", 
                            client_secret = #"Azure Application Client Secret"
                            ])
                    )
                    , Headers = [Accept = "application/json"], ManualStatusHandling = {400}
                ]
            ) // end web contents
        ), // end json
        AzureAccessTokenB = TokenResponse[access_token] // assign token value
    in
    AzureAccessTokenB,
    GetPages = (Path)=>
        let
            Host = #"Azure Graph API Url",
            Source = Json.Document(
                Web.Contents(
                    #"Azure Graph API Url"
                    , [RelativePath = Path, Headers = [Authorization = "Bearer " & makeToken(
                        #"Azure Graph API Url"
                        , #"Azure Tenant ID"
                        , #"Azure Application ID"
                        , #"Azure Application Client Secret"
                    )]]
                )
            ),
            LL= @Source[value],
            Next = Text.Replace(Source[#"@odata.nextLink"], Host, ""),
            result = try @LL & @GetPages(Next) otherwise @LL
        in
            result,
        Fullset = GetPages("beta/users?$")
    in
        Fullset

ImTrainChooChoo
Regular Visitor
1 year ago
Same problem here:
Loaded a string from a text file, that contains a SQL-query. Then called a Database using that sql-string.
"I had two steps, generate [SQL query string], and query using said [SQL query string]"

And solved by
"Once I put the [SQL query string] generation inside each [Database] calling query, it worked"

This worked (one power query):
let
Source = Sql.Database(
SQL_Endpoint,
LakeDB,
[Query = Text.FromBinary(Web.Contents("https://yourcompany.sharepoint.com/path_to_sql/sqlquery_in_textfile.sql"))])
in Source

and ridiculously, this did not work (two separate power queries):
let
Source = Sql.Database(
SQL_Endpoint,
LakeDB,
[Query = sql_query])
in
Source
with sql_query another power query:
let
Source = Text.FromBinary(Web.Contents("https://yourcompany.sharepoint.com/path_to_sql/sqlquery_in_textfile.sql"))
in
Source

Anonymous
Not applicable
3 years ago
How did you rebuild that second query? I have the exact same query, getting the latest file from a folder. And I'm getting this annoying error.
- sp8
  Helper II
  3 years ago
  Same. What do you mean "rebuilt"?
- jusTodd
  Advocate IV
  3 years ago
  I only had about 7 steps, so I just manually rebuilt the second query. I am sure there is probably a better way.
- Giulia9444
  New Member
  2 years ago
  Have you solve problem?

Forum Discussion

Query references other queries or steps, so it may not directly access a data source