Fix Power Query Formula.Firewall Without Disabling Privacy Checks

Keep privacy checks enabled, identify which Firewall error you have, and separate data-source access from query combination where appropriate. Do not mark confidential sources Public or set global privacy options to Ignore just to make Refresh succeed.

The Firewall helps prevent values from one source being passed unintentionally to another during query evaluation. It is not an account-permission manager, and it does not make an otherwise unauthorized combination acceptable. Microsoft: data privacy levels and query evaluation and folding.

Read the error before changing anything

In Excel for Windows, open Data > Queries & Connections, then edit the failing query. Note its name and the failing Applied Step. Do this in a working copy; do not post screenshots containing real paths, server names, tokens, or personal data.

Two common message categories need different investigation:

• References other queries while directly accessing a source: look for a query that reads another query and also calls a connector such as File.Contents, Web.Contents, or Sql.Database. Separating source access into staging queries may resolve this structure.
• Source privacy levels cannot be combined: review the actual data boundaries and assigned classifications. A structural rewrite is not permission to weaken them.

Power Query divides evaluation into partitions, which are not necessarily identical to named queries. That is why creating a reference or renaming a query is not a guaranteed repair. The official Firewall explanation documents both error families. Behind the scenes of the Data Privacy Firewall.

Check the relevant sources, without broadening access

1. In current Windows Excel, open Data > Get Data > Data Source Settings. Select Data sources in current workbook when offered, then inspect each source actually used by the failing query.
2. Select a source and Edit Permissions to review its Privacy Level. Keep confidential information Private. Organizational is for a justified trusted organizational boundary; Public is for data genuinely suitable for public sharing. Do not choose a classification merely because it is more convenient.
3. Keep combination governed by the sources’ privacy levels. Under Data > Get Data > Query Options, review Privacy for both Global and Current Workbook if a previous workaround changed them. Do not select an Ignore privacy option.
4. Retry only the affected query. If the required data transfer is not authorized, stop and ask the data owner for an approved design or export rather than changing the boundary.

These classifications govern evaluation; setting a file Public does not upload it or grant filesystem access. Conversely, a successful refresh does not prove the resulting workbook is safe to share. Microsoft: set privacy levels in Excel.

For credentials or file access failures, use the existing authorized access route. Do not grant broad folder, tenant, or database permissions as a Firewall fix. Inspecting the workbook’s source settings is different from clearing permissions globally. Manage source settings and permissions.

Rebuild a file append as source queries plus a combining query

This small example shows a reviewable structure: each source query reads one local file; the final query references the source queries and does not directly open another source. It is a practice pattern, not a promise that every source or privacy combination will succeed.

Save these synthetic files in a dedicated local practice folder. The example paths use C:\ExcelPractice; replace them with the real path of your synthetic practice files. Do not place confidential files in that folder.

• firewall-north.csv
• firewall-south.csv

Their contents are:

Record,Item,Units
N-01,Tea,3
N-02,Coffee,2

Record,Item,Units
S-01,Tea,4
S-02,Coffee,5

1. Create StageNorth

In Microsoft 365 for Windows, choose Data > Get Data > From Other Sources > Blank Query. In Power Query Editor, open Home > Advanced Editor, replace the sample expression with this code, and name the query StageNorth:

let
    Source = Csv.Document(
        File.Contents("C:\ExcelPractice\firewall-north.csv"),
        [Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.Csv]
    ),
    Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Typed = Table.TransformColumnTypes(
        Headers,
        {{"Record", type text}, {"Item", type text}, {"Units", Int64.Type}}
    )
in
    Typed

This explicitly reads UTF-8 CSV, promotes its header, preserves the two label columns as text, and makes Units a whole number. File.Contents, Csv.Document, Table.PromoteHeaders, and Table.TransformColumnTypes document the M operations used here.

Use Close & Load To > Only Create Connection if offered, so this intermediate query need not occupy a worksheet. That load choice is housekeeping, not a privacy bypass or a durable materialized cache. If Load To is disabled, close and load first, then right-click the query in Queries & Connections and select Load To. Microsoft: creating and loading queries.

2. Create StageSouth

Create another blank query named StageSouth. Use the same code, changing only the path to C:\ExcelPractice\firewall-south.csv. Confirm that its preview has S-01 and S-02. Keep this query connection-only as well.

If a privacy prompt appears, classify only these wholly synthetic files appropriately. They contain invented data and are suitable for Public classification; do not copy that classification to real organizational or confidential sources. If the dialog groups them under a parent folder, ensure that the entire selected scope is the dedicated synthetic folder.

3. Create CombinedPractice

Create a third blank query named CombinedPractice:

let
    Combined = Table.Combine({StageNorth, StageSouth})
in
    Combined

Only this query needs loading to a worksheet. It references the two stages and contains no File.Contents call. The GUI equivalent is Append Queries as New, selecting the two stages. Append stacks rows; it is not a key-based join. Microsoft’s append workflow and Table.Combine reference.

For convenience, download the three-query M recipe as text. Paste each expression into its own named query, not all three into one editor. The recipe contains no credentials or external-service URLs.

Verify the output and the boundary

The combined result should have one header and these four data rows, with a total of 14 Units:

If loaded at A1, =SUM(C2:C5) should return 14. In D2, =COUNTIF($A$2:$A$5,A2) filled down should return 1 for each Record. These use SUM and COUNTIF to catch a missing row, text quantities, or duplicate sample records; they are not security checks.

Then change only the practice South file’s S-02 Units from 5 to 6 using a text editor. Refresh CombinedPractice. The expected total becomes 15, with four records still present. Restore the sample afterward if you want to repeat it.

Also inspect the three query definitions: StageNorth reads only North, StageSouth reads only South, and CombinedPractice reads only the two stages. A combined preview alone is insufficient evidence that real private values did not cross an inappropriate boundary.

When staging does not solve it

• A stage uses another query to build its path or URL: it is no longer the simple single-source pattern shown here. For the practice test, use the literal local path. For production, review the parameter and destination together; do not blindly inline sensitive values into a web request.
• Two Private sources still produce an error: the same label does not mean unrestricted sharing is allowed. Keep the truthful labels and redesign or obtain a specifically approved export. Do not downgrade both to Organizational merely to match them.
• The error is Expression.Error, DataFormat.Error, or access denied: investigate the named path, headers, numeric conversion, or authorized credentials. Those are not automatically Firewall failures.
• The query refreshes on one computer but fails on another: compare the exact Excel build, source identities, local paths, privacy configuration, and existing access. Do not copy another person’s credentials.
• Someone suggests Table.Buffer: it is not a general privacy permission or a guaranteed cure. It can increase memory use and change query folding. Resolve the boundary and structure first. Microsoft’s Table.Buffer cautions.
• A folder connector brings in extra files: constrain the approved files deliberately. Success on this two-file example does not justify reading every file in a production directory.

Excel versions and verification

The menu instructions target Microsoft 365 Excel for Windows. Excel 2016 and later Windows editions integrate Power Query, but connector availability and controls vary. Excel 2010/2013 used a separate Power Query add-in and ribbon; Microsoft documents that add-in as deprecated. This is not an Excel 2013 installation guide, and it does not claim matching Mac or web controls. Power Query history and application support.

Microsoft now documents a relaxed partition rule in Power BI Desktop. That setting is not evidence that the same control or behavior exists in your Excel build. This guide therefore keeps the source-only staging pattern and requires testing in the actual host. Power BI Desktop’s partition setting.

Not tested in Excel or Power Query. Official references were checked October 16, 2026. The synthetic CSV contents, row counts, and totals were independently checked outside Excel. The M expressions were reviewed against their documented signatures, but were not executed; no successful refresh, reproduced Firewall error, or native screenshot is claimed.