Forum Discussion
Formula.Firewall: Query references other queries, so it may not directly access a data source.
Ken Puls blogged about this here
http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
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.
"I think M / Power Query is a fantastic tool but it needs so many improvements ..."
As far as I m concerned, I would say only :
"it needs so many improvements ..."
100% agree with both these statements:
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.
In essence, the option to do source control in some practical way from Excel PowerQuery and PowerBI PowerQuery.
