How to fix the Formula.Firewall error in Power Query (2 ways)

One of the most frustrating errors in Power Query is the Formula.Firewall error. It only ever appears when we try to do something advanced. Plus, the error messages don’t seem to make much sense. However, once you understand what it does, you’ll realize the formula firewall is there to protect us. Therefore, we just need the right approach to know how to fix the Formula.Firewall error in Power Query.

Table of Contents

• What is the formula firewall and how does it work?
• The two types of Formula.Firewall errors
• Reference other queries error message
• Privacy levels error message
• Flattening queries
• Privacy levels
• Always ignore privacy level settings
• Apply the correct privacy level for each data source
• Inherited privacy levels
• Fixing the example file
• Conclusion

If prompted when opening the example file, set the Privacy Levels as follows (this will help to trigger the errors shown in this post).

• Current Workbook: Organizational
• https://xe.com/currencytables: Public

Watch the video

Watch the video on YouTube

What is the formula firewall and how does it work?

The purpose of the formula firewall (or “data privacy firewall” as it’s also known) is to prevent users from unintentionally passing information between two data sources.

To understand this, let’s look at an example.

Let’s suggest we had a query that used a passport number as a parameter to get data from an external data source. If that passport number were stored in a cell in an Excel workbook, it would only be accessible to those in our organization. The external data source, however, is outside our organization. Passport numbers are sensitive/confidential information, which should not be shared externally, therefore we really don’t want Power Query to allow this data leak.

In this scenario, the formula firewall error appears; it stops us from taking this action.

Power Query does not know whether the value we are passing is confidential or not. Therefore, rather than allow the risk, Power Query stops data from passing between incompatible privacy levels. This forces users to be intentional about what data is passed to and from queries.

The two types of Formula.Firewall errors

The Formula.Firewall error can take two forms:

• Reference other queries error message
• Privacy levels error message

We will look at both of these.

Reference other queries error message

The first error message is:

Formula.Firewall: Query ‘____’ (step ‘____’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

This error arises when the formula firewall is not able to operate correctly due to how queries have been structured.

In the background, Power Query creates partitions to control how data sources interact with each other. Each partition does not have direct access to other partitions, but instead makes a call through a gateway.

If our queries are structured so that one source provides input into another source, the gateway cannot operate correctly. Therefore, this triggers the Formula.Firewall error.

See the flattening queries section below for details on how to fix this error.

Privacy levels error message

The second error message is:

Formula.Firewall: Query ‘____’ (step ‘____’) is accessing the data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

This error arises when combining two data sources with incompatible privacy levels. See the privacy levels section below for details to fix this error.

Flattening queries

The process of flattening queries ensures information is passed directly from the source to the query and not via another source. This allows the gateway to operate correctly.

To understand the process, let’s use an example. We have created a tool to retrieve exchange rates from xe.com. Initially, the process has been created with a fixed date of 15 January 2023. Now we want to make the date dynamic.

The value in Cell C3 is a date that the user can input.

The formula in cell C5 uses the date in cell C3 to generate a valid URL for working with xe.com.

="https://www.xe.com/currencytables/?from=USD&date="&TEXT(C3,"YYYY-MM-DD")

C5 is also a named range called URLPath; this has been loaded in Power Query as a separate query. Then, within Power Query, we have drilled down into the value to create a dynamic parameter.

The M code for URLPath query is (click View > Advanced Editor to see the code):

let
    Source = Excel.CurrentWorkbook(){[Name="URLPath"]}[Content],
    Column1 = Source{0}[Column1]
in
    Column1

The original query to retrive exchange rates for 15 January 2023 is the FX Rates query.

The M code for this query is:

let
    Source = Web.Page(Web.Contents(
        "https://www.xe.com/currencytables/?from=USD&date=2023-01-15"
    )),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{
        {"Currency", type text}, 
        {"Name", type text}, 
        {"Units per USD", type number}, 
        {"USD per unit", type number}
    })
in
    #"Changed Type"

Since the URLPath has been reduced to a dynamic parameter, we should be able to use the URLPath in place of the web address. But, what happens if we change the Source step of the FX Rates query?

Change this:

    Source = Web.Page(Web.Contents(
        "https://www.xe.com/currencytables/?from=USD&date=2023-01-15"
    )),

To this:

Source = Web.Page(Web.Contents(URLPath))

Unfortunately, making this change triggers the Formula.Firewall error.

Looking at the query dependencies view we can see the issue (click View > Query Dependencies).

The data from the Current Workbook is passed into the URLPath before it is passed into the FX Rates query. Therefore, the data is passed between partitions, and the gateway cannot operate correctly.

To correct this, we can flatten the query:

• Select the URLPath query, click View > Advanced Editor
• In the Advanced Editor, copy the text between let and in (but don’t include the words let and in), then click Done.
• Select the FX Rates query, click View > Advanced Editor
• Paste the copied text between the words let and Source
• In the first code block, make the following changes (this ensures unique and meaningful step names)
  • Change Source to SourceX (both instances)
  • Change Column1 to WebURL
• Each Power Query step must end with a comma, so add a comma at the end of the WebURL step
• In the second code block, change URLPath to WebURL
• Click Done

The final code in the FX Rates query now looks as follows (all changes highlighted):

let

    SourceX = Excel.CurrentWorkbook(){[Name="URLPath"]}[Content],
    WebURL = SourceX{0}[Column1],

    Source = Web.Page(Web.Contents(WebURL)),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{
        {"Currency", type text}, 
        {"Name", type text}, 
        {"Units per USD", type number}, 
        {"USD per unit", type number}})
in
    #"Changed Type"

The “reference other queries” Formula.Firewall error disappears. So, we have removed the first Formula.Firewall error 😀

The URLPath query can be deleted as it no longer serves any purpose.

However, depending on the privacy levels set for the Excel Workbook and https://xe.com, we may still receive the “privacy levels” Formula.Firewall error, which we move onto next.

Privacy levels

If we have the following error message, we need to fix the privacy levels.

We can either address this error by:

• Ignoring privacy entirely
• Using the correct setting for each data source.

We look at both below.

Always ignore privacy level settings

This first option ignores the data privacy settings entirely. If you know confidential information will not be passed, it’s an acceptable option.

Click File > Option Settings > Query Options.

The Query Options dialog box opens. In the Current Workbook section, select privacy > Always ignore Privacy Level settings, then click OK.

Refresh the preview window by clicking Home > Refresh Preview. The “privacy levels” error message should disappear and the queries operate correctly.

The Query Dependency view now shows both sources separately feeding into the FX Rates query. The gateway can operate correctly. 👍

Apply the correct privacy level for each data source

Rather than ignoring privacy settings, we may want to set them correctly.

To set the privacy level for each source, click File > Options > Data source settings.

In the data source settings dialog box, select the data source, then click edit permissions….

Note: If the data source is not within the data sources in the current workbook section, it should be in the global permissions section.

In the edit permissions dialog box, we can set the privacy level for each source.

There are four privacy settings:

• None: No privacy levels are applied. This is not the same as ignoring privacy level, but an indication that Private, Organizational, or Public levels have not been selected.
• Private: The data is confidential or sensitive and must not be passed outside of this source. Any data in sources marked as Private cannot be shared with another source.
• Organizational: The data can be shared within the organization. Therefore, this data can only be shared with other Organizational data sources.
• Public: The data can be shared with any other data source, including Public or Organizational sources.

Inherited privacy levels

One area that can catch us out is how Power Query deals with privacy levels from the same source.

For example, let’s suggest we have two privacy levels set as follows:

• https://xe.com/currencytables: Organizational
• https://xe.com: Public

Power Query applies Public to both data sources, as https://xe.com is the parent of https://xe.com/currencytables.

Therefore, you may need to change a parent’s privacy level for the Formula.Firewall to operate correctly.

Fixing the example file

To ensure the example file operates correctly:

• Set the privacy level for both the Excel Workbook and https://xe.com/currencytables as Organizational.
• Click Home > Close & Load to implement the changes.

Now the tool should operate correctly. Change the date in Cell C3, then click Data > Refresh All. The historical exchange rates for the selected date will load into the Table.

Conclusion

In this post, we have seen how to fix the Formula.Firewall error in Power Query. It can be tricky to understand, especially with two error messages that look very similar. By following the steps in this post, you should be able to fix the error for your scenario and enjoy more dynamic queries.

Related Posts:

• Common Power Query errors & how to fix them
• Change the Power Query source based on a cell value
• Power Query – Import Data from the Web

---

What next?

Discover how you can automate your work with our Excel courses and tools.