An issue that I’ve come across fairly regularly when using Power Query is the Formula.Firewall error:
Formula.Firewall: Query 'query' (step 'step') references other queries or steps and so may not directly access a data source. Please rebuild this data combination.Power BI returns a Formula.Firewall error when one query is making a request to another data source of a different privacy level. This error acts as a security mechanism to prevent potential data leakage from queries accessing external sources.
For instance, if you had a table with a list of stocks, and then created a custom column that called a web API function for each stock, this would return a Formula.Firewall error. Why? Because information is being ‘leaked’ to the external source. If the API owners were nefarious, they could look at the stock ticker info being requested and use it to their advantage.
The Formula.Firewall error is letting us know that this data leakage is occurring, and we should be aware of it.
Of course, often we want to dynamically combining sources. If you trust the sources you are combining, then it doesn’t matter if information leaks from one to the other.
If you don’t trust the sources, or you are concerned about data leakage you should rebuild your query to avoid leaking data in this way.
If you are certain that this form of information leaking is okay in your context, there are a number of ways you can resolve the error:
- Set the Privacy level of all data sources to Organisational. Power Query uses privacy levels to make sure that data is only shared at the same level. It will never let you request information from a public source if that information would leak information from a private source.
- Rebuild your query to avoid the error. Explore alternative solutions such as loading data and then merging it, rather than querying dynamically. This approach might bypass the Formula.Firewall error. This blog from Excel Guru has some great suggestions.
- Ignore Privacy Levels. While not recommended, you can choose to ignore privacy levels if you are confident that the leaked information is not a concern for you. To do this, go to File > Options and Settings > Options > Privacy > Always ignore Privacy Level Settings.
To learn more about privacy levels and the Formula.Firewall error, refer to the official Power BI documentation.
There are also a number of great blogs on this subject: