Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

Hi Everyone, I am facing the the following issue and hoping someone can point me in the right direction. I get the following error message each time when initially hitting refresh in a couple report...
Need Help
Bilbo_777's avatar
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's avatar
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's avatar
    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
    in
      Source