Brilliant! I have encountered this error many times, never realized there were 2 Formula.Firewall errors, & solved them by ignoring privacy levels (which made me squeamish). Now, I know why they occur, to pay attention to the error type, & how to fix them (without feeling squeamish). Thank you!
This is a great video!!! Thank you very much : ) : ) Although I have created videos on the same topic, I am coming to your video because of what appears to be a new issue. I have been using Excel tables from within the current workbook as queries and then stacking them like: Source => Query1 => Query 2 for almost 10 years when I create queries for lookup tables. I have NEVER seen this error in this situation before, but today, the hated "references other queries or steps, so it may not directly access a data source. Please re-build the data combination" error reared its ugliness. Did Microsoft change this recently? I just see no way whatsoever that with the 20 or 30 times I have used lookup tables in Power Query over the last decade that I could have not seen this. Any ideas?
Hi Mike - Firstly, thanks for watching my video. I'm not aware of any change in the behavior that you've described. Though I have had an issue recently with a new error: Formula.Firewall MultipleUnclassified/Trusted error It doesn't stop the refresh, but it does show an error message. From what I can tell, it was an error which was fixed in 2015 and now it's returned. I've reported it to Microsoft. So whether that is related in some way. Have you tried opening one of the files where it has worked in the past? In theory, you should get the same error as the new file. If it doesn't error, it might point you to another solution. Feel free to find my email address in one of my messages to the Excel MVP DL and send me the file the look at.
@@ExcelOffTheGrid Thanks for post back, Excel Teammate!!! I did many tests and iterations to try and figure out what was causing this seemingly impossible error (cuz they are from same source at same privacy level), but never tracked what the cause was. I have not seen this situation before or since, so I am chalking it up to Excel gremlin...
Thanks Mark for such a thorough and clear explanation! Firewall errors can be very frustrating when you don't know what these messages are really saying.
Many thanks for the entire explanation, but particularly the summary on privacy levels at the end. Many is the time I've stumbled over those and struggled to get through it.
Thank you very very much for explaining in such clear language what causes these very mysterious errors as well as what the privacy levels are all about. I am now a subscriber and will be checking out more of your content.
Very helpful, Mark! Specially that last bit about the global sources. If I understood it correctly, in terms of security, there does not seem to be a significant difference between the two methods. If instead a date we were passing on passport number to query the website, matching the privacy levels of the two sources would still expose the private information the same way as solving the issue with ignoring the privacy levels at the workbook level. Is this correct?
Yes, it would pass the information in the same way. What changes is whether Power Query conducts any checks at all. Setting the Privacy Levels to be compatible is stating that we know what information is being passed between sources, and that we're happy with it. If new sources are added, we need to make an active decision about those sources too. Ignoring the Privacy Levels is stating that we don't want Power Query to perform any compatibility checks. If new sources are added, Power Query will let us pass any data between them. So, it's all down to managing risks for each workbook. I think the most likely Formula.Firewall error is when combining dynamic sources (e.g. cell values to determine which file path to use as the source), in most cases, these would both be organizational, so the Privacy Levels error often will not appear. Interrestingly, Ignoring Privacy Levels removes the step of requiring checks, therefore that gives us faster processing times, which I think will make most people decide to ignore the privacy levels (even if that is not the right choice).
@@ExcelOffTheGrid yes, that's why it says something like "Ignore privacy levels and potentially improve performance." I had a case where I flattened the queries to not have the workbook set to ignore privacy levels. The firewall error would not show but the queries started to take a very long time to refresh instead of a few seconds. I had to switch back to ignore privacy levels. The data source were local folders containing Excel files.
Excellent video tutorial, Mark. I think it is a point that is widely used by companies when they connect to websites to recover data for some reason. Without a doubt, you explain perfectly how to solve the matter. Thank you!
An excellent, clear and efficient proposed solution. The simulation of the failure was very well set up. With this solution I was able to solve a problem with a report. Thank you very much for all the effort in sharing knowledge. Greetings from Bogotá - Colombia
Thanks very much for explaining this issue that seems to occur rather often. This video makes a great resource and reference for use when building and debugging Power Query queries. Thank you kindly.
Thanks for sharing ! Interesting indeed to clarify this mysterious feature of privacy levels in PQ. I had 'global privacy' in Query Options set as 'always ignore' which indeed didn't triggered any problem. But having privacy settings in Query Options on a global level, then also on a current workbook level + then also have the options to set /edit permissions levels for the data sources makes this again into a brain drainer (although I can see the logic behind).
I’m not confident enough to set the global setting to Ignore. I don’t know what data I’ll be working with, so I think it’s safer to set at a workbook level.
I thought I understood this, but I still can't rectify the following situation .. Using text.combine with a parameter list, from a table in Excel, in a SQL query WHERE clause. The only way I have found to remove the formula firewall error is by ignoring privacy levels. This can't be the best solution, surely ??
Hello mark. I have built the query structure in a similar way that you introduced as the wrong way of doing it. Up until now i've had zero problems with it. But when my co-worker is trying to 'refresh all' he gets the 'formula.firewall' error message. Will try your fix tomorrow at work. But in the instance of it not working, what might be the problem that i am facing? Thanks for the video anyway!
Each time a different user opens a file with a query it resets the privacy settings. You can use the VBA Queries.FastCombine setting in the Workbook Open event to force the setting when a user opens the file.
![Yeat & Quavo - 5BRAZY [Official Music Video]](http://i.ytimg.com/vi/4XxnTAwyxus/mqdefault.jpg)
Brilliant! I have encountered this error many times, never realized there were 2 Formula.Firewall errors, & solved them by ignoring privacy levels (which made me squeamish). Now, I know why they occur, to pay attention to the error type, & how to fix them (without feeling squeamish). Thank you!
Best video in explaining this annoying issue so clearly. Thanks Mark👍👍
Thanks Kebin. I appreciate that. 😁
Finally an explanation that makes sense. Got to revisit some privacy levels settings now... Thank you Mark for both 😛
I was hoping to create a video that actually made sense of this error, so I’m glad that came across. 😀
This is a great video!!! Thank you very much : ) : ) Although I have created videos on the same topic, I am coming to your video because of what appears to be a new issue. I have been using Excel tables from within the current workbook as queries and then stacking them like: Source => Query1 => Query 2 for almost 10 years when I create queries for lookup tables. I have NEVER seen this error in this situation before, but today, the hated "references other queries or steps, so it may not directly access a data source. Please re-build the data combination" error reared its ugliness. Did Microsoft change this recently? I just see no way whatsoever that with the 20 or 30 times I have used lookup tables in Power Query over the last decade that I could have not seen this. Any ideas?
P.S. Subbed and Thumbs Up : ) : )
Hi Mike - Firstly, thanks for watching my video.
I'm not aware of any change in the behavior that you've described.
Though I have had an issue recently with a new error: Formula.Firewall MultipleUnclassified/Trusted error
It doesn't stop the refresh, but it does show an error message. From what I can tell, it was an error which was fixed in 2015 and now it's returned. I've reported it to Microsoft.
So whether that is related in some way.
Have you tried opening one of the files where it has worked in the past? In theory, you should get the same error as the new file. If it doesn't error, it might point you to another solution.
Feel free to find my email address in one of my messages to the Excel MVP DL and send me the file the look at.
@@ExcelOffTheGrid Thanks for post back, Excel Teammate!!! I did many tests and iterations to try and figure out what was causing this seemingly impossible error (cuz they are from same source at same privacy level), but never tracked what the cause was. I have not seen this situation before or since, so I am chalking it up to Excel gremlin...
Thanks Mark for such a thorough and clear explanation! Firewall errors can be very frustrating when you don't know what these messages are really saying.
Thanks 😁
Hopefully it will help lots of people.
Many thanks for the entire explanation, but particularly the summary on privacy levels at the end. Many is the time I've stumbled over those and struggled to get through it.
I'm glad it was helpful. Hopefully it will help you through.
Thank you very very much for explaining in such clear language what causes these very mysterious errors as well as what the privacy levels are all about. I am now a subscriber and will be checking out more of your content.
Great news - I’m glad I could help. 😁
Very helpful, Mark! Specially that last bit about the global sources.
If I understood it correctly, in terms of security, there does not seem to be a significant difference between the two methods. If instead a date we were passing on passport number to query the website, matching the privacy levels of the two sources would still expose the private information the same way as solving the issue with ignoring the privacy levels at the workbook level. Is this correct?
Yes, it would pass the information in the same way. What changes is whether Power Query conducts any checks at all.
Setting the Privacy Levels to be compatible is stating that we know what information is being passed between sources, and that we're happy with it. If new sources are added, we need to make an active decision about those sources too.
Ignoring the Privacy Levels is stating that we don't want Power Query to perform any compatibility checks. If new sources are added, Power Query will let us pass any data between them.
So, it's all down to managing risks for each workbook.
I think the most likely Formula.Firewall error is when combining dynamic sources (e.g. cell values to determine which file path to use as the source), in most cases, these would both be organizational, so the Privacy Levels error often will not appear.
Interrestingly, Ignoring Privacy Levels removes the step of requiring checks, therefore that gives us faster processing times, which I think will make most people decide to ignore the privacy levels (even if that is not the right choice).
@@ExcelOffTheGrid yes, that's why it says something like "Ignore privacy levels and potentially improve performance."
I had a case where I flattened the queries to not have the workbook set to ignore privacy levels. The firewall error would not show but the queries started to take a very long time to refresh instead of a few seconds. I had to switch back to ignore privacy levels.
The data source were local folders containing Excel files.
Best explanation of Formula.Firewall error I have seen. THANK YOU! It all makes much more sense now, and more importantly, I fixed my problem...
Excellent video tutorial, Mark.
I think it is a point that is widely used by companies when they connect to websites to recover data for some reason.
Without a doubt, you explain perfectly how to solve the matter.
Thank you!
Thanks Ivan.
I worked hard to try to get the best explanation. So I appreciate that feedback. 😀
Excellent explanation. Thanks ❤
Thank you.
An excellent, clear and efficient proposed solution.
The simulation of the failure was very well set up.
With this solution I was able to solve a problem with a report.
Thank you very much for all the effort in sharing knowledge.
Greetings from Bogotá - Colombia
Glad it helped 😁
Thanks very much for explaining this issue that seems to occur rather often. This video makes a great resource and reference for use when building and debugging Power Query queries. Thank you kindly.
You're very welcome! Glad it was helpful.
As always, nice job.
Thanks 😁
Usefull trick. Thank you for share
Thanks for sharing ! Interesting indeed to clarify this mysterious feature of privacy levels in PQ.
I had 'global privacy' in Query Options set as 'always ignore' which indeed didn't triggered any problem.
But having privacy settings in Query Options on a global level, then also on a current workbook level + then also have the options to set /edit permissions levels for the data sources makes this again into a brain drainer (although I can see the logic behind).
I’m not confident enough to set the global setting to Ignore. I don’t know what data I’ll be working with, so I think it’s safer to set at a workbook level.
Thanks Mark :)
My pleasure Jeff 😀
I thought I understood this, but I still can't rectify the following situation ..
Using text.combine with a parameter list, from a table in Excel, in a SQL query WHERE clause.
The only way I have found to remove the formula firewall error is by ignoring privacy levels. This can't be the best solution, surely ??
Hello mark. I have built the query structure in a similar way that you introduced as the wrong way of doing it. Up until now i've had zero problems with it. But when my co-worker is trying to 'refresh all' he gets the 'formula.firewall' error message. Will try your fix tomorrow at work. But in the instance of it not working, what might be the problem that i am facing? Thanks for the video anyway!
Worked like a charm. Thank you so much for this video!
Each time a different user opens a file with a query it resets the privacy settings.
You can use the VBA Queries.FastCombine setting in the Workbook Open event to force the setting when a user opens the file.