Privacy levels and the formula firewall (concept)
Explain privacy levels and recognise formula firewall errors.
~8 min read · advanced · Module 19: Refresh robustness and performance concepts
By Tahir Nas · Examples run in our simulator · Published · Updated
This is a concept lesson. The practice simulator doesn't run this feature; the explanation describes how real Power Query hosts behave, and any code the simulator can't run is labelled. See the compatibility page.
Before this: Merge queries: choosing a join kind, Reference other queries: staging and reuse
Combining a private file with a public web API could leak private values into a web request: if a filter folds into the API call, the URL might contain your data. Power Query's privacy levels (Private, Organizational, Public) and its formula firewall exist to prevent that.
The errors
Two messages are common. Formula.Firewall: Query 'X' (step 'Y') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. appears when one query both reads a source and uses values from another query in the same step. A second form says the query is accessing data sources that have privacy levels which cannot be used together.
The usual fix: separate reading from combining
Read each source in its own staging query, and combine them in a third query that reads no source directly. The simulator has no web or file access, so here is the shape of the combining query using in-memory staging queries:
Query Customers
#table({"Cust", "Country"}, {{"C1", "DE"}, {"C2", "FR"}})Query Rates
#table({"Country", "VatRate"}, {{"DE", 0.19}, {"FR", 0.2}})let
// Combines two staging queries; reads no data source itself
Joined = Table.NestedJoin(Customers, {"Country"}, Rates, {"Country"}, "R", JoinKind.LeftOuter),
Result = Table.ExpandTableColumn(Joined, "R", {"VatRate"})
in
ResultResult
| Cust | Country | VatRate |
|---|---|---|
| C1 | DE | 0.19 |
| C2 | FR | 0.2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The pattern that triggers the firewall is a source function whose arguments come from another source, such as building a web URL from values in a private file:
let
Country = "DE",
Response = Web.Contents("https://rates.example.org/vat", [Query = [country = Country]])
in
ResponseNot run by the simulator
Web.Contents is not supported in the Power Query Exercises simulator. See the compatibility page for the supported subset. This code is shown for real hosts only; see the simulator compatibility page.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Typical fixes
- Set accurate privacy levels for each source in Data source settings.
- Split staging queries (one per source) from the query that combines them.
- Avoid passing values from one source into another source's function call; when you must, make sure both sources' privacy levels allow it.
Where to find this in Excel
- Excel for Windows
- Data > Get Data > Data Source Settings > Edit Permissions; Data > Get Data > Query Options > Privacy
- Excel for Mac
- Microsoft states that Excel for Mac doesn't currently support privacy levels.
- What you see
- Data Source Settings sets each source's level (None, Private, Organizational, Public); Query Options decides whether Excel enforces them.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Set privacy levels (Power Query) (Microsoft Support); Privacy levels in Power Query (Microsoft Learn); Manage data source settings and permissions (Power Query) (Microsoft Support).
Common mistakes
- Turning off privacy levels as the first response to a firewall error.
- Reading a source and merging with another query's result in the same step.
- Marking a source Public when it holds personal or confidential data.
Excel, Power BI and the simulator
- Excel: Data → Get Data → Query Options → Privacy (global and per-workbook); source levels are set in Data Source Settings. Power BI Desktop: File → Options and settings → Options → Privacy, and Data source settings.
- In the Power BI service, privacy levels are set per data source in the gateway or dataset credentials, and they apply during scheduled refresh.
- The simulator doesn't enforce privacy levels; Web.Contents and other connectors raise UnsupportedFeature.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Refresh robustness quiz