Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 reports:

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

I then open the query in query editor, close it without making any modifications, and it is automatically resolved when refreshing again.

Where should I look to avoid getting the error message in the first place?

Thanks

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

This is related to privacy settings. For lots of details see here:

https://blog.crossjoin.co.uk/tag/formula-firewall/

 

If there are no privacy concerns with the particular data you're working with then the easiest solution is to ignore privacy levels entirely. This can be found under the options and settings as described here:

https://learn.microsoft.com/en-us/power-bi/enterprise/desktop-privacy-levels

View solution in original post

21 REPLIES 21
PedroNalin
Frequent Visitor

ignoring privacy levels has worked fine for me, thanks!

 

C4YNelis
Advocate II
Advocate II

Hi All,

 

this error often occurs simply due to a very annoying bug that has been around for years now. Please check this issue and provide your input there if this is what you experience too.

 

Desktop Refresh Error: Query "references other que... - Microsoft Fabric Community

 

Cheers,

Niels

Syndicate_Admin
Administrator
Administrator

I THINK I GOT IT...

 

I was struggling with the same thing and I saw some other post from Matt mentioning something about problems when merging tables with different sources... Is like power bi questioning if you really have access to both, so it needs to make sure of that so the query works...

 

So, I merged a query of a table called area with a query of a table called quotation. I merged both queries in the quotation query.

 

What I did to fix the problem was, i copied the reference from the advance mode in the area and pasted inside the source code of the quotation.

 

Copied this:

and pasted here:

 

 

it works like a charm!

 

 

Thank you for sharing! This worked for me too. 

To be more elaborate these are the 2 queries which I am merging

Ruchita_0-1704705851592.pngRuchita_1-1704705892297.png

Which step should I add in which query? As in the SS you have provided I can't see the merge step.

 

JMacC
Frequent Visitor

because PowerBI firewall doesn't let you combine two queries, you need to also include the code for creating the MasterBranch  table inside this query. 

 

Once that's done, you should be able to join them. Currently firewall will be blocking this query, as it's merging two sources

Thanks for your quick response. The problem is fixed now. I used a different approach.

 

The 2 tables had a relationship between them with 2 columns. So I was merging the 2 tables. Now instead I created a custom col by concatenating the 2 cols in both the tables. And created a relationship between the 2 tables using this new col. This approach solved this error as well as refresh from service showing incorrect values issue.

Hi,

I am also facing the same issue. I am merging 2 queries and sometimes get the above error during refresh. Can you elaborate what step you added?

 

Thank you

Just delete all the connection that created by power bi automatically. Keep only connections created by you an drefresh. It will work

@Syndicate_Admin 
It fixed my problem, thanks a lot ! 

Thank you very much for sharing!! I got this same strange error and adding the reference from advance editor to both queries resolved it.

Syndicate_Admin
Administrator
Administrator

How would this be avoided once published for a scheduled refresh?

jusTodd
Advocate III
Advocate III

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? 

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 ?

Bilbo_777_0-1696940099321.png

 

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
Anonymous
Not applicable

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.

Have you solve problem? 

Same. What do you mean "rebuilt"?

I only had about 7 steps, so I just manually rebuilt the second query.  I am sure there is probably a better way.

AlexisOlson
Super User
Super User

This is related to privacy settings. For lots of details see here:

https://blog.crossjoin.co.uk/tag/formula-firewall/

 

If there are no privacy concerns with the particular data you're working with then the easiest solution is to ignore privacy levels entirely. This can be found under the options and settings as described here:

https://learn.microsoft.com/en-us/power-bi/enterprise/desktop-privacy-levels

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors