Formula firewall error - Query referenced to replace parameter

[Anonymous]

Hi all,

It seems that Formula.Firewall is quite a common issue, I understand that this is linked to Query folding, but I can't find how to solve it properly (I managed to do it by modifying the Privacy settings of my file, but I'm not happy with it).

Here is my story:

1. I created an Excel file, displaying the result of several queries.

2. Two of these queries (say, Query1 and Query2) are based on other Excel files (say, Test 1 and Test2) in a unique folder. As the user is not familiar with Power Query, I want him to be able to modify easily these parameters if needed, and so created a tab in the Excel file for him to precise the storage path and files names:

Storage_path	S:\MAIN FOLDER\Extract files\
File1	Test1.xlsx
File2	Test2.xlsx

3. Using following method: I uploaded these cells as queries in my Power Query: Power Query Parameters - using Excel named cells (accessanalytic.com.au)in order to try ti use them as parameters
4. I now want to use this when connecting Query1 and Query2 to the corresponding Excel files:

Source = Excel.Workbook(File.Contents(Storage_path & File1), null, true)

And that's here that Firewall says that's he is not happy.
Any hint ? 🙂

Thanks!

[Anonymous]

@Jimmy801 thank you for your answer, I get the idea but can't find the good formula.

The closest that I find is something like : 
= Excel.Workbook(File.Contents(Expression.Constant(Storage_path & File1)), null, true), but I get an Expression.Error: "Caracters non conform in access path"

[Jimmy801 · Accepted solution]

Hello @Anonymous 

probably this is coming from your parameters Storage_path and File1. Does the first parameter end with an "\"? otherwise the complete path is not valid.

BR

Jimmy

[Anonymous]

I finally found the issue, it works with a \\myfiles\folder1\ access path, but not with a X:\folder1\ one.

Thanks for the help!

[Jimmy801]

Hello @Anonymous 

yes, include the query where you are importing the selection of the user (maybe you to adapt it - like filtering or connecting) into the query where you are quering the files. If you have one unique query where you are doing both (use selection of user and querying the file itself), firewall should be no problem.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy