Forum Discussion
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
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
27 Replies
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
Hi AlexisOlson,
Like you mentioned here, I have ignored the privacy settings and tried to refresh. But when I do this, the columns in few tables changes its naming convention and also the data types. For Eg: If a table A has a column name Address and once I change the privacy settings to none and refresh, the column name changes to Addressname ...
I'm having the same issue. While I'm able to get beyond query refresh challenges after ignoring Privacy settings, columns were renamed when I made this settings change. Now, I'm trying to create a new Measure and can't use a column because it doesn't seem to think it exists. It is a column in a Table where another column was renamed by Power BI and now what I see in the Transformation Table does not match the Semantic Model Table. Thoughts?
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 very much for sharing!! I got this same strange error and adding the reference from advance editor to both queries resolved it.
Syndicate_Admin
It fixed my problem, thanks a lot !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
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 ?
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
Same. What do you mean "rebuilt"?
How would this be avoided once published for a scheduled refresh?
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
ignoring privacy levels has worked fine for me, thanks!
Please read my response for additional insight into error reasons and potential solutions:
https://community.fabric.microsoft.com/t5/Service/Power-Query-s-Data-Privacy-Firewall/m-p/4412647#M265836
