cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mussol_ Regular Visitor
Regular Visitor

Re: Formula.Firewall: Query references other queries, so it may not directly access a data source.

Solved just using "Merge" as soon as possible on the query.

 

Seems that editor doesn't like to do merges by the end of the query as it was clearly explained on excelguru blog.

 

Josep.

Shiva799 Frequent Visitor
Frequent Visitor

Re: Formula.Firewall: Query references other queries, so it may not directly access a data source.

I'm getting same error but Im not using excel sheet but sql stored procedure query. Is there any solution for this?
MariyaSal New Member
New Member

Re: Formula.Firewall: Query references other queries, so it may not directly access a data source.

Please try the following in Power BI Desktop:

File -> Options -> Privacy -> Select “Ignore Privacy Levels…”

It helped me.

Highlighted
paulob_involves Regular Visitor
Regular Visitor

Re: Formula.Firewall: Query references other queries, so it may not directly access a data source.

Hello,

 

I did this privacy level change, it solved for Power BI Desktop refreshes, but web refresh is still failing due to the same error.

freder1ck Member
Member

Re: Formula.Firewall: Query references other queries, so it may not directly access a data source.

This is the article previously referenced in this thread:

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

I confess I had to read it a few times to get it.

 

SNAGHTML409dfd44_thumb.pngfrom the article by Ken Puls

Basically, each query needs to be loaded into a staging query first. Then, you can combine these staging queries.

 

Fred

 

the2nicks Frequent Visitor
Frequent Visitor

Re: Formula.Firewall: Query references other queries, so it may not directly access a data source.

All of my data retrievals are now done in external functions

  • edicated functions which also do some manipulation of the data before returning it
  • a standard fLoadSheet function which is simply passed the name of a sheet in the spreadsheet and loads and returns it

However, yet again, I had a problem with this frustrating error. I called a standard function to return some parameters from a parameters sheet. Suddenly I got the Firewall error.

 

Given that I have written some VERY complicated scripts which retrieved data from various sources and worked no problem but written other scripts which are not (seemingly) as complicated but have failed with this error, I am still of the opinion that this is Microsoft's catch-all, can't cope error message!

 

Also, I sometimes get this error when stepping through the code (in the "advanced editor") but not when running the script, explain that!

paulob_involves Regular Visitor
Regular Visitor

Re: Formula.Firewall: Query references other queries, so it may not directly access a data source.

This is madness.

 

I have tried using staging queries, and also making data retrieval in custom functions. None worked.

 

I'll probably have to start using a data lake to make simple merges between data sources, only because of this issue.

david7f Frequent Visitor
Frequent Visitor

Re: Formula.Firewall: Query references other queries, so it may not directly access a data source.

Thanks for the solution, I was struggling to solve this and now it works

steve_honey Frequent Visitor
Frequent Visitor

Re: Formula.Firewall: Query references other queries, so it may not directly access a data source.

In case it helps others I ran into this issue after adding a non-supported/unofficial custom connector into PBI desktop and then trying to run queries against SharePoint files. 

Removing the custom connector from the local Documents > Power BI Destop > Custom Connectors folder got rid of the issue for me. (Note I didn't have any quries using the custom connector, just the fact it was there seemed to trigger the firewall thing).

the2nicks Frequent Visitor
Frequent Visitor

stRe: Formula.Firewall: Query references other queries, so it may not directly access a data source.

I've just had it happen again ... twice!

I edited an existing script.

I didn't reference any additional (external) data.

I took a column creation out of a group statement and created it in the next step, the idea being to check a result from the group creation, something you can't do inside the group creation.

I also added a step inside an existing IF statement to check if the new column (which can be null or a record) was null before checking the contents of the column.

That's it, that's all I changed.

It failed with the usual error.

I changed four other scripts (which reference the same data but output different results) in the same way and they worked fine.

Even more bizarrely, one other script which I changed in the same way worked fine in the "Advanced Editor" and then failed when I quit back to Excel and ran the script!

This all adds weight to my belief that this really is a default "I can't cope" error message.

What's even more frustrating is if you read the actual error message it states "references other queries or steps" ... every step references other steps, so this is a meaningless error message!

It also means that I have literally no idea what the problem is or how to resolve it.

So frustrating.