Solve the Formula.Firewall Error in Power Query

The Formula Firewall error is a data privacy error you might get when sharing Power Query Excel files and it's basically Excel's way of saying you have an external data source when it seems like you don't. One way to mitigate this is to change the privacy setting to ignore this message, but when you're collaborating with other, they might not feel comfortable doing this. The other option is to set up a bridge or staging querying. This involves getting into the M code, but it's not so bad. Click the link above to see the video.
Here's the link on how to create a parameter (as mentioned in the video). • Sharing Files with Pow... .
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

[@shirleymoreman6725]

Thank you! This is by far the clearest explanation I've seen for how to get round this very annoying problem!

[@DougHExcel]

You're welcome! Great to hear!

[@daviddecastro9653]

Excellent tutorial - thanks

[@DougHExcel]

You are welcome!

[@SergioLotito]

Thank you!!!! Incredible and clearest explanation, you are amazing

[@DougHExcel]

Hi Sergio Lotito, thanks for the kind words!

[@ramonalmonte3035]

Excelente video, Excelent idea, Thanks

[@DougHExcel]

You are welcome!

[@wayneedmondson1065]

Hi Doug. Nice one. Normally, I skip the bridge query and just define the path parameter before the Source step of the main query, as in:
let
Path = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content][Pathname]{0},
Source = Excel.Workbook(File.Contents(Path & "data.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
etc..
That said, I can see the value of have a bridge query, just to isolate it for troubleshooting in the future and or maybe easier edits. As always, a great tutorial from you :)) Thanks for sharing and thumbs up!!

[@DougHExcel]

Wow, super suggestion Wayne!

[@oku9000]

Thank you very much! This was such a great workaround!

[@aaronyue322]

wondering what if I have multiple parameters. eg, for SQL server source I want to put servername, DW name as two parameters

[@planfinanceirapessoal]

How to do this by replacing the worksheet with folder? Because I bring all the worksheets in a folder, not just a single worksheet.

[@fredrikarntsen5880]

Hello Doug,
Nice video! I have been trying to grasp my head around this firewall, and this explains it really well. However, it seems like I am working on something that is a bit more tedious than this example.
I have a query that pulls several Excel files into one table (Folder.Files) and a function that grabs the path inside of a table or cell, so when a new user opens up it grabs the username of that person to dynamically change this path. This is OK, the query grabs all the files for both all users. A function is then applied to each row using [Folder path & Name], each path is dynamically changed to their username, name of the file is obviously constant. The function dynamically detects the position of different information that I need. It will then consolidate this information into a table so that I have everything in a single table in the correct format.
This is where I get others get the firewall error. Do you have an explanation for why this is happening? Is it because it is trying to apply a function and append external files? I do not understand why it works for me, but not for other people... The privacy setting is "Always ignored" for everyone.

[@batava]

I got the same issue! Using the file to pass it to other functions that use an API. Any words of wizdom, Doug?

[@sunhyejoo5879]

Hi Doug. I'm experiencing another firewall error(Formula.Firewall: Information about a data source is required.) which seems the solution of your firewall error in this video clip is not working. The power query is accessing to SharePoint lists, and this error is occurring whenever a bot account is trying to refresh the power query. It is solved when a human interrupt to reassign the credentials. I don't experience this error when myself or my colleagues refresh the query. Do you have any insight for this firewall error? The privacy levels in this power query for either current workbook or the SharePoint is all set to None.

[@DougHExcel]

Hi sunhye joo, thanks for the comment! Maybe this other one will give an idea ua-cam.com/video/ignTb_UozTw/v-deo.html