Forum Discussion
Formula.Firewall: Query references other queries, so it may not directly access a data source.
I am getting the following error:
Formula.Firewall: Query 'Query1' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
In my first query I am accessing an API to get a list of URLs. In the second query it uses this list of URLs to then go back to the API and request all of the data.
I understand what the complaint is, but how do I get around it? I am actually always hitting the same domain for both queries, but maybe it doesn't know that. I suppose it wouldn't know that until runtime.
I have read that in Excel PowerQuery you can go to:
File=>Options and Settings=>Options and set the Ignore Privacy Levels option.
But no such option seems to exist in Powe BI Desktop. Does anyone know what I'm doing wrong or how I can get around this restriction, it seems like a common usage scenario?
Ken Puls blogged about this here
http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
58 Replies
Ken Puls blogged about this here
http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
The error can occur from privacy permissions as well. It may be necessary to take the steps outlined in the solution above and also to change the privacy options. File>Options and settings>Query Options>Privacy
Then choose "Always ignore privacy level settings".
Hi MattAllington, thanks for the info..
Yes I was able to get around it just now by effectively moving all of the steps from the other query into the same query. This gets around the problem but it's a bit annoying.
All steps in the same Query! Without "Reference"!!!
This did the trick!
You are a lifesaver!!!
Thank you so much!
Michael
First of all, this link mentioned no longer exist, so this is definitely not a solution.
Then. In my scenario, I've defined a transformation for a JSON file from a known format as a formula. This transformation DOES use data from other sources, because it's exactly what makes it useful as a formula, and the very reason for it to be converted into a formula.
Problem is, this formula becomes useless due to this error - unless I go and apply the security settings fix.
I understand this won't work in the cloud. But then, how to fix it AND keep the formula useful?Hey mate, have you found any 2023 solution?
Please try the following in Power BI Desktop:
File -> Options -> Privacy -> Select “Ignore Privacy Levels…”
It helped me.
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.
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.
from 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
Fantastic! This saved my day (and most of yesterday 🙂 )
For anyone still experiencing this issue, under File > Options, select Privacy, and if it's appropriate, select Ignore privay levels. Even after following all the other suggestions referenced in this thread, I still got an error when refreshing. It worked when I was in the Query Editor just fine (no errors), but refreshing from the regular report view would fail.
thank you
For Excel folks out there (and for future myself) finding this thread on google:
Data tab -> expand Get Data -> Query Options -> Privacy -> Always ignore Privacy Level settings -> OK
For people using multiple APIs and publishing to the Power BI Service in 2023, here is the solution that worked for me:
Power Query does not want you to reference another Query AND gather external data at the same time. So, if you need to use the result from one API to make more requests to another API you will have to do it all in the same Query.
Many suggest to just use the "always ignore privacy level settings" in the file settings, but it seems to me that it only works on your personal computer. When that same dashboard is published in the Power BI Service, that option will be ignored, and the dataset will not be able to refresh.
Here is the link from Power Query's documentation exlpaining how it works:
learn.microsoft.com/en-us/power-query/data-privacy-firewall#example-passing-data-from-one-data-source-to-another
Hope it helps 🙂
Any ideas on what to do if you need to use a function to iterate through paging?
Hello, from my experience, you can indeed use functions to make iterating requests (even in the power BI service). I would advise to avoid making your request functions too complicated though, like accessing multiple sources at the same time or passing data between queries.
That is the best I can say without knowing the details of what youre trying to do right now.
Is it going to cause any problem if the privacy level setting is turned off??
The privacy settings are designed to prevent accidentally sending sensitive data somewhere you shouldn't. Eg. Imagine you have a table in SQL with staff details including salaries. You write a query that joins this data to Facebook data (for some reason). The privacy settings will stop the private data in SQL being sent to the open data source, Facebook. As long as you don't have any such queries, turning off the privacy settings will have no impact. Eg, as long as all your data sources are internal to your organisation.
You can also try the following
Formula.Firewall Error Referencing Queries - R & Python Scripts - sqlitybi
It helped me.
Thank you...
This sorted it for me.
Method 1: Ignore Privacy Levels
The advice provided in this blog post now appears to be running contrary to experience in Power BI.
A query that contains all the Web.Contents calls a query needs leads it to function correctly. The splitting of the different Web.Contents calls into seperate queries before being merged into one final query leads an error to be thrown.
This ^^
you need to bundle all the bits together inside one query, rather than referencing values/tables generated by other queries. BI can't handle you bringing in other queries
For me it was do the following via 2 functions inside the same query (each time you use the API):
- generate token function
- invoke the paged call, using said token
Recycles some code but it works
As far as I'm concerned this is their catch-all, can't cope error message!
I have a spreadsheet with a lot of queries (which create data for sheets in the spreadsheet) and function queries which reference that data and return to other queries, I did it in this way as I had repeatedly run into this error and it was suggested to me that placing the data retrieval in a function would resolve the issue, but it doesn't.
I have several very compliccated scripts which work fine. I just copied a few lines from one of those scripts into a far simpler script
- call a common function to retreive data from a sheet into a table- a function to reference the above table to get a piece of information
- a call to the above function to get the required piece of information
and, BANG, formula firewall error.
Looking at the message it tells me that the error is in the last step in the script BUT the retrieval of the data is in the previous step!
I stepped through the script and it then failed on the previous step.
The reason it was failing on the final step when I ran the whole script is because M uses a lazy interpretation model which only tries to determine the value of something when it's needed, so in the final step it referened data from the previous step and that's where the problem occurred.
And this is why I think these errors occur all too frequently ... the lazy interpretastion ... I'm sure that if every step were separately processed (and the result stored until the end of the script) that a lot of these errors would not occur ... if memory is an issue then add a command to discard something when it's no longer required.
I've looked at my script and tried to work out what the hell that error message means and how to fix it and I simply don;'t understand ... why does the same kind of processing work fine in another script?
I've had similar problems in other scripts ... I added a parameter sheet to the spreadsheet so that I could change the query results without having to edit the script every time ... retrieving the parameters works fine in most scripts BUT in a couple scripts when I add the step to retrieve the parameters I get the Firewall error ... surely this is simply a matter of M not being able to cope and throwing out this generic and unhelpful message?
I wish they would fix this.
After a couple hours I found a workaround for the problem I was having but it really just confirms what I already think about M.
I found that the problem I was having was NOT down to accesing an external data source (all of my data loads are done in functions) but because I load a table, create two filtered tables from that table and then join them together. Why it would object to that I don't know, but it does ... I suspect (as per my previous post) that it's something to do with the lazy interpretation and it getting confused!
I then moved the splitting and re-joining of the table into a function script and it worked!
I think M / Power Query is a fantastic tool but it needs so many improvements ...
1. Ability to save files from within the Power Query environment ... qiute a few times Excel has hung/crashed when exiting the environment and I have lost hours of work!
2. Ability to export all scripts to text files in a specifed folder.
Would be nice ...
1. The Advanced Editor name is a bit of a joke as it anything but!. Colour-coding and all of that would be nice BUT for starters how about some basic Notepad-style editing features such as Search and Replace?
2. I really wish I could change the editor font size as it is too small, I don't want to have to change all of Window settings just because of this.
3. Environment state. It would be nice if the opened/closed groups stayed opened/closed in Power Query and the Excel sidebar, I constantly have to close the groups I'm not interested in.
So, regarding editing of Power Query, I just found out that there's an addon for Power Query in Visual Studio (via Adam Saxton), and just started using it. He also mentioned that there's one for Visual Studio Code as well.
