Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hello @Stéphane
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
@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"
Hello @Stéphane
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
I finally found the issue, it works with a \\myfiles\folder1\ access path, but not with a X:\folder1\ one.
Thanks for the help!
Hello @Stéphane
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.