Log In
Register
Home
Power Query
Power Pivot
July 11, 2016
Offline
Hi,
I have created a query that picks up the source file from a cell in the Excel worksheet. This is to allow the user to easily change the file that they want the query to address.
I posted the file with the query to a network location so others could use it. The queries run fine for me but whenever anyone else tries to refresh them they get an error that states "Formula.Firewall: Query 'Query_Forecast' (step 'Source') references other queries or steps , so it may not directly access a data source. Please rebuild this data combination.
The privacy levels are set to none and ignore privacy levels. The users who are trying to run the queries all have the same permissions on the network folder as I do.
I have attached the file containing the queries (Power_Query_Formula_Firewall_Error.xlsx) and the source file it is trying to look at (PQ_Use_Sheet_Name_Source_File.xlsx). The cell R2 in the first file is where the filepath and name is entered to allow the user to change the file they want to look at.
Any help as always greatly appreciated.
Thanks
Bax
See AnswerActive Member
September 20, 2020
Offline
I recently encountered this problem for almost the same reason.
I ended up using creating a new parameter, and instructing the other users on how to change the parameter itself.
I edited your workbook and created a parameter to access the file and had no formula firewall issues.
Power Query
Power Pivot
July 11, 2016
Offline
Hi Mike,
Thanks for the reply and the file. I was trying to avoid the need for the users to launch the query editor. I wanted to pick the value up from the worksheet. I can't see how this is done with the parameter you added. If it is possible can you let me know how?
Also the thing that is confusing me is that how I set it up works for me but not for others.
Thanks
Bax
Active Member
September 20, 2020
Offline
Ah, I see. In my case I was wanting to make it harder for someone to unintentionally change the parameter.
I have attached a workbook that should work for what you are wanting, and won't generate the formula firewall error.
Answers Post
Power Query
Power Pivot
July 11, 2016
Offline
Hi Mike,
Thanks for taking the time to look at this again. I can see what you have done differently to my original query. As mentioned mine worked for me but not for other users. I will adopt your method and get one of the others to test. I will report back when done.
Thanks
Bax
Power Query
Power Pivot
July 11, 2016
Offline
Hi Mike,
I got the other users to test the refresh with the query updated to use your method. I can confirm that it all worked fine so problem solved.
Thanks for your help.
Regards
Bax
1 Guest(s)
