Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

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

Formula Firewall in Power Query

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.

Download the example file: Click the link below to download the example file used for this post:

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
  • 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 error from combining data sources

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 from privacy levels

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 Initially, the process has been created with a fixed date of 15 January 2023. Now we want to make the date dynamic.

Formula.Firewall - Example File

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


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

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

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

The M code for this query is:

    Source = Web.Page(Web.Contents(
    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}
    #"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(

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

Query dependencies view - with formula.firewall error

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


    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}})
    #"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, 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.

Formula.Firewall from 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.

Ignore Privacy settings

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

Query dependencies view - no formula firewall error

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.

Edit Permissions dialog box

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:

  • Organizational
  • Public

Power Query applies Public to both data sources, as is the parent of

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


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:

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

3 thoughts on “How to fix the Formula.Firewall error in Power Query (2 ways)

Leave a Reply

Your email address will not be published. Required fields are marked *