How to fix the Formula Firewall in Power Query

How to fix the Formula Firewall in Power Query | Excel Off The Grid

Рет қаралды 5,978

Күн бұрын

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post★
exceloffthegrid.com/formula-f...
★ About this video ★
One of the most frustrating error messages in Excel is the Formula.Firewall error. It only ever comes when we try to do something advanced, and the message doesn't make any sense!
In this video, we show you why the Formula Firewall is there (it's actually there to help us!) and how to work with it within Power Query
0:00 Introduction
0:27 What is the Formula Firewall?
3:15 Scenario
4:18 Flattening queries
7:15 Apply privacy levels
9:36 What are privacy levels?
10:48 Conclusion
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel

Пікірлер: 38

@jimfitch 6 ай бұрын

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!

@kebincui Жыл бұрын

Best video in explaining this annoying issue so clearly. Thanks Mark👍👍

@ExcelOffTheGrid Жыл бұрын

Thanks Kebin. I appreciate that. 😁

@excel-in-g Жыл бұрын

Finally an explanation that makes sense. Got to revisit some privacy levels settings now... Thank you Mark for both 😛

@ExcelOffTheGrid Жыл бұрын

I was hoping to create a video that actually made sense of this error, so I’m glad that came across. 😀

7 ай бұрын

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

@ExcelOffTheGrid 7 ай бұрын

Glad it helped 😁

@GrainneDuggan_Excel Жыл бұрын

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.

@ExcelOffTheGrid Жыл бұрын

Thanks 😁 Hopefully it will help lots of people.

@ja3mi2 6 ай бұрын

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...

@serdip Жыл бұрын

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.

@ExcelOffTheGrid Жыл бұрын

You're very welcome! Glad it was helpful.

@ExcelU Жыл бұрын

Thanks Mark :)

@ExcelOffTheGrid Жыл бұрын

My pleasure Jeff 😀

@odallamico Ай бұрын

Usefull trick. Thank you for share

@houstonsam6163 Жыл бұрын

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.

@ExcelOffTheGrid Жыл бұрын

I'm glad it was helpful. Hopefully it will help you through.

@CeliaAlvesSolveExcel Жыл бұрын

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?

@ExcelOffTheGrid Жыл бұрын

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).

@CeliaAlvesSolveExcel Жыл бұрын

@@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.

@christophertauss3640 4 ай бұрын

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.

@ExcelOffTheGrid 4 ай бұрын

Great news - I’m glad I could help. 😁

@ziggle314 Жыл бұрын

As always, nice job.

@ExcelOffTheGrid Жыл бұрын

Thanks 😁

@IvanCortinas_ES 11 ай бұрын

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!

@ExcelOffTheGrid 11 ай бұрын

Thanks Ivan. I worked hard to try to get the best explanation. So I appreciate that feedback. 😀

@excelisfun Жыл бұрын

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?

@excelisfun Жыл бұрын

P.S. Subbed and Thumbs Up : ) : )

@ExcelOffTheGrid Жыл бұрын

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.

@excelisfun Жыл бұрын

@@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...

@dannypuype6104 Жыл бұрын

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).

@ExcelOffTheGrid Жыл бұрын

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.

@moociferzhang 18 күн бұрын

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 ??

@Andreandre110 Жыл бұрын

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!

@Andreandre110 Жыл бұрын

Worked like a charm. Thank you so much for this video!

@ExcelOffTheGrid Жыл бұрын

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.