Understanding the Formula Firewall

When you encounter a Power Query error related to Formula.Firewall or privacy levels, it's not a bug. This is Power Query's built-in security mechanism at work. The Formula Firewall is designed to prevent sensitive data from leaking between different data sources. It achieves this by refusing to combine data from multiple sources until it can ascertain that doing so is safe. For instance, attempting to merge data from an internal company database with a public web API will trigger this firewall by default, as there's a risk of inadvertently exposing organizational data to an untrusted source.

The core of this protection lies in the privacy level assigned to each data source. Power Query categorizes sources into three levels: Public, Organizational, and Private. These levels are set the first time you connect to a source. The firewall then monitors query steps. If a query attempts to pass data from a source with a higher privacy level (e.g., Organizational or Private) into a process that involves a source with a lower privacy level (e.g., Public), the firewall intervenes. It blocks the operation to prevent potential data leakage.

How Privacy Levels Work

The privacy level of a data source is a critical setting. When you first connect to a data source – be it a local Excel file, a SharePoint list, a SQL Server database, or a web service – Power Query prompts you to assign it a privacy level. The options are:

  • Public: Data from this source is accessible to anyone on the internet. Examples include websites like Wikipedia or public APIs.
  • Organizational: Data from this source is accessible to anyone within your organization. This could be internal SharePoint sites, company databases, or shared network drives.
  • Private: Data from this source is only accessible to you. This typically applies to local files or specific personal accounts.

Power Query uses these classifications to build an internal map of trust. When a query involves multiple sources, it checks if data from a more restricted source is being sent to a less restricted one. If this condition is met, the firewall activates.

The Firewall's Logic Explained

Imagine you have two queries:

  • Query A: Connects to an internal SQL Server database (Organizational privacy level).
  • Query B: Connects to a public weather API (Public privacy level).

Now, suppose you create a third query that tries to use data from Query A (e.g., a list of customer addresses) to filter or enhance data retrieved from Query B (e.g., fetching weather forecasts for those addresses). Power Query sees that you are attempting to send Organizational data (customer addresses) into a process that involves a Public source (weather API). This is where the Formula Firewall steps in. It blocks this operation because it cannot guarantee that the customer addresses won't be exposed or logged by the public API, or through the connection process itself.

The firewall's purpose is not to be an obstacle, but a safeguard. It's like a security guard at a high-security facility who won't let you bring classified documents into a public park. The guard isn't being difficult; they are enforcing protocol to protect sensitive information.

Resolving Formula Firewall Errors

When you encounter this error, you have a few options to resolve it, depending on your confidence in the data sources and the security implications.

Option 1: Ignore Privacy Levels (Use with Extreme Caution)

For specific scenarios where you are certain that combining data is safe and won't cause data leakage, you can instruct Power Query to ignore privacy levels for that particular query. This is done within the Power Query Editor settings:

  1. Go to File > Options and settings > Privacy.
  2. Under Privacy settings, select Always ignore Privacy Level settings.

This setting effectively disables the Formula Firewall for all queries in your Power BI Desktop or Excel instance. This is the least secure option and should only be used if you fully understand the risks and have no sensitive data involved.

A more granular approach is to adjust the privacy levels of individual data sources. If you have a query that combines an Organizational source with a Public source, and you are confident it's safe, you can change the privacy level of the Organizational source to Public. However, this also carries risks.

Option 2: Configure Privacy Levels Correctly

The recommended approach is to correctly configure the privacy levels of your data sources. If you have an internal data source that you are willing to treat as public for the purpose of a specific report, you can change its privacy level.

  1. In the Power Query Editor, select the query for the data source you want to modify.
  2. Go to the Query tab and click Edit Permissions.
  3. In the Edit Permissions dialog box, select the data source from the list.
  4. Under Privacy Level, choose the desired level (e.g., change 'Organizational' to 'Public' if you are comfortable with that).
  5. Click OK.

After adjusting the privacy levels, you may need to refresh your queries for the changes to take effect. This method maintains a degree of security by ensuring you explicitly decide which data sources can be combined.

Option 3: Separate Queries and Combine Later

Sometimes, the most robust solution is to avoid direct combination within Power Query. You can load data from separate sources into the Power BI data model and then use DAX to combine or relate them. DAX operates outside the Formula Firewall's scope for source-level privacy checks. This approach is particularly useful when dealing with complex data transformations or when you need to maintain strict data segregation.

This involves:

  1. Creating separate queries for each data source.
  2. Loading each query into the Power BI data model without any cross-source transformations in Power Query itself.
  3. Defining relationships between the tables in the data model view.
  4. Using DAX measures or calculated columns to perform any necessary aggregations or combinations.

This strategy ensures that sensitive organizational data never leaves the secure environment of its original source query before entering the Power BI data model, where it can then be handled with appropriate model-level security measures.

Broader Implications

The Formula Firewall is a crucial, often misunderstood, feature for anyone working with sensitive data in Power BI or Excel. It acts as an essential data governance tool, forcing users to be explicit about data source interactions and preventing accidental data exposure. Developers and analysts must understand these privacy settings to build reliable and secure data solutions. Ignoring the firewall can lead to data breaches, while understanding and configuring it correctly ensures data integrity and compliance.