Picture this: your ETL pipeline pulls customer transaction data from an on-premises SQL Server, enriches it with demographic data from a third-party REST API, and then joins it against a public reference dataset hosted on a government website. The pipeline runs fine in development. But when you deploy it to the enterprise Power BI service, queries start failing with cryptic firewall errors, or worse — data that should never leave your internal network silently gets sent to an external endpoint because Power Query's formula firewall made an unexpected optimization decision. This isn't a hypothetical. It happens constantly in enterprise environments, and the root cause is almost always a misunderstanding of how Power Query's privacy levels and credential management actually work under the hood.
Privacy levels and credential management are the two pillars of secure ETL in Power Query, and they're deeply intertwined in ways that most tutorials never fully explain. Get them wrong and you're either locking yourself out of legitimate cross-source queries, or — more dangerously — you're inadvertently allowing sensitive data to be transmitted to untrusted endpoints. Get them right and you unlock the ability to build genuinely secure, auditable, multi-source pipelines that your security team won't have nightmares about.
By the end of this lesson, you'll have a thorough, practical understanding of how to architect and implement privacy-aware Power Query pipelines. You won't just know what to configure — you'll understand why the formula firewall makes the decisions it does, which means you'll be able to diagnose and fix issues that no error message will ever fully explain.
What you'll learn:
Before diving in, you should be comfortable with:
let expressions, and record/list manipulationBefore you can manage privacy levels intelligently, you need a mental model of why they exist. Power Query's formula firewall is not a traditional security control like a network firewall or an access control list. It is a query folding constraint — an engine-level mechanism that prevents M query plans from inadvertently transmitting data from one source to another during query optimization.
Here's the core problem it solves. When Power Query optimizes a query, it tries to push as much computation as possible down to the data source — this is query folding. In a cross-source join, a naive optimizer might decide to pull records from your internal SQL Server and send them as filter parameters to an external API call, or vice versa. If those two sources have different trust levels, that's a data leak, and it can happen silently with no error and no audit trail.
The formula firewall's job is to catch exactly these situations. When it detects that a query plan would combine data from sources with different privacy levels, it intervenes. Depending on the configuration, it will either refuse to run the query entirely, or it will execute the query in a way that prevents folding across source boundaries (which preserves security but can destroy performance).
Understanding this distinction is critical: the formula firewall doesn't prevent you from combining data sources. It prevents Power Query from letting one source see another source's data during query optimization. The actual data combination happens in the M engine's memory, after both sources have returned their data independently. The firewall is about what gets sent to each source, not what you ultimately combine in your output.
Power Query defines three built-in privacy levels, but the documentation's explanations are notoriously superficial. Let's go deeper.
A Private data source is treated as a black box that should never be referenced by any other source's query. Think of it as: "the content of this source must never leave this source's boundaries during query construction." If you mark a data source as Private, Power Query will refuse to fold any query that would send data from this source to another source, even another Private source.
The practical implication is that Private-to-Private combinations require the M engine to retrieve both datasets fully and perform the join in memory. This is correct behavior — two isolated private systems shouldn't be able to see each other's data even during optimization — but it comes with significant performance costs at scale.
Use Private for:
An Organizational data source is trusted within your organization but not with external parties. The firewall will permit Organizational sources to see each other's data during query optimization (and thus allow folding), but it will block any optimization path that would send Organizational data to a Public or Private source.
Wait — why would it block Organizational-to-Private combinations? Because Private means isolated. A Private source has opted out of all cross-source visibility, so even though Organizational data is "safe," the Private source's policy says it doesn't want to participate in folding with anything.
Use Organizational for:
A Public data source contains data that is by definition not sensitive — it's already public information. The firewall will allow Public data to be sent to any other source. It will also allow Public data to be seen by any source during query optimization.
Use Public for:
This table describes what folding is permitted between source combinations:
| Query Source → | Private | Organizational | Public |
|---|---|---|---|
| Private | ✗ No folding | ✗ No folding | ✗ No folding |
| Organizational | ✗ No folding | ✓ Folding permitted | ✓ Folding permitted |
| Public | ✓ Folding permitted | ✓ Folding permitted | ✓ Folding permitted |
Reading this table: the column is the source receiving data from another source during folding. Private sources never receive data from any other source. Organizational sources can receive data from Organizational and Public sources. Public sources can receive data from anyone.
This asymmetry trips people up. You might think "both sources are Organizational, so everything is fine," and you'd be right for Organizational-to-Organizational. But if you add a third Private source to the mix, the firewall re-evaluates the entire query plan. Even if your join with the Private source seems isolated, the engine may determine that the optimal query plan would share data across that boundary, and it will intervene.
When you connect to a new data source in Power BI Desktop, the system will prompt you to set a privacy level the first time you access it. This prompt appears as part of the "Edit Credentials" dialog. The levels appear as a dropdown: Private, Organizational, Public, or None.
"None" is the wildcard that causes the most trouble. When a source is set to None, Power Query doesn't know its privacy level. When the formula firewall encounters a None source in a cross-source combination, it must assume worst case — it will either block the combination entirely or prompt you (interactively) to make a decision. In a scheduled refresh context where there's no user interaction possible, a None source in a cross-source query will cause the refresh to fail with an error that looks like a firewall error but is actually a missing-privacy-level error.
To set or change privacy levels after initial setup, navigate to File → Options and Settings → Data Source Settings. Select the data source and choose "Edit Permissions." The privacy level dropdown is on the permissions dialog.
Warning: Privacy level settings in Power BI Desktop are stored per user, per machine. They are not embedded in the PBIX file itself. This means when you publish a report, privacy levels need to be reconfigured in the Power BI Service. This is one of the most common sources of "works on my machine" failures in enterprise deployments.
In the Power BI Service, privacy levels are configured on a per-dataset basis through the dataset settings. Navigate to your dataset, open Settings, and expand the "Data source credentials" section. Each data source listed there will have a privacy level setting accessible through "Edit credentials."
For enterprise deployments using a data gateway, privacy levels must also be configured at the gateway data source level. The gateway admin portal allows you to set privacy levels for each registered data source. These settings interact with the dataset-level settings — the more restrictive of the two always wins.
While you can't directly set privacy levels from M code (they're metadata managed by the host environment), you can architect your queries to make privacy-level-aware design decisions explicit. This is where parameterized data source configurations become valuable.
Consider a pattern where you define your data source connection strings and types as parameters, making the privacy intent clear in the architecture:
// DataSourceConfig.pq - A shared query defining your source topology
let
SourceConfig = [
InternalDW = [
Server = "dw-prod.corp.internal",
Database = "SalesWarehouse",
PrivacyIntent = "Organizational",
ContainsPII = false
],
HRSystem = [
Server = "hr-prod.corp.internal",
Database = "HRCore",
PrivacyIntent = "Private",
ContainsPII = true
],
PublicReferenceAPI = [
BaseUrl = "https://api.worldbank.org/v2/",
PrivacyIntent = "Public",
ContainsPII = false
]
]
in
SourceConfig
This doesn't configure privacy levels automatically, but it creates a single source of truth for your team's intent, which you can reference in documentation, query audits, and onboarding. Pair this with a deployment runbook that maps each entry to its required privacy level setting in the target environment.
Credentials in Power Query are separate from privacy levels, though they're managed in the same location. Credentials answer "who are you?" while privacy levels answer "what trust level does this source have?" Both need to be right for secure ETL.
Power Query credentials are stored in the host application's credential store:
This architecture means credentials are never in your M code or your PBIX files. That's by design, and you should never work around it. If you find yourself tempted to hardcode credentials in M code, stop — there's almost certainly a better pattern.
One of the most powerful and misunderstood patterns in enterprise Power Query is using parameters for connection metadata (server names, database names, API endpoints) while keeping credentials in the credential store. This gives you environment portability without credential exposure.
Here's a complete example for a SQL Server source that needs to work across development, staging, and production:
// Parameter: Environment
// Type: Text
// Current Value: "production"
// Possible Values: ["development", "staging", "production"]
// ServerConfig query - resolves environment to actual server details
let
Environment = Environment, // References the Environment parameter
ServerMap = [
development = [
Server = "dw-dev.corp.internal",
Database = "SalesWarehouse_Dev",
Schema = "dbo"
],
staging = [
Server = "dw-staging.corp.internal",
Database = "SalesWarehouse_Staging",
Schema = "dbo"
],
production = [
Server = "dw-prod.corp.internal",
Database = "SalesWarehouse",
Schema = "dbo"
]
],
Config = Record.Field(ServerMap, Environment)
in
Config
// SalesTransactions query - uses ServerConfig for connection
let
Config = ServerConfig,
Source = Sql.Database(
Config[Server],
Config[Database],
[
Query = "SELECT t.TransactionID, t.CustomerID, t.Amount, t.TransactionDate
FROM " & Config[Schema] & ".Transactions t
WHERE t.TransactionDate >= DATEADD(day, -90, GETDATE())",
CommandTimeout = #duration(0, 0, 30, 0)
]
)
in
Source
Critical Note: Parameterized connection strings have a significant caveat. Power Query needs to be able to identify the data source definitively to look up its credentials. When you use a parameter to construct a server name, Power Query may not be able to match the constructed server name to a stored credential. The connection will be made, but if the resolved server name doesn't exactly match a stored credential entry, you'll get an authentication prompt or failure. Always test your parameterized connections by verifying that the credential lookup succeeds for each environment value.
REST API authentication using API keys is particularly tricky because the natural place to put an API key is in a query header or URL parameter, and both of those locations are visible in M code and potentially logged by the source system.
The correct enterprise pattern uses Power Query's built-in Web.Contents credential mechanism for header-based authentication. Here's the pattern:
// ExternalMarketDataQuery - demonstrates secure API key handling
let
// BaseUrl must be a literal or stored parameter - NOT constructed dynamically
// This is required for Power Query to match credentials correctly
BaseUrl = "https://api.marketdatavendor.com/v3/",
// RelativePath and Query parameters are safe to construct dynamically
// They're sent after credential lookup has occurred
EndpointPath = "securities/prices",
QueryParams = [
symbols = "AAPL,MSFT,GOOGL",
interval = "1d",
range = "3mo",
format = "json"
],
// Power Query will look up credentials for BaseUrl in the credential store
// If configured as "Web API" credentials with an API key header,
// the key is injected by the engine without appearing in M code
Response = Web.Contents(
BaseUrl,
[
RelativePath = EndpointPath,
Query = QueryParams,
Headers = [
Accept = "application/json",
#"Content-Type" = "application/json"
// API key header is NOT specified here - it comes from credential store
],
Timeout = #duration(0, 0, 0, 30)
]
),
ParsedResponse = Json.Document(Response),
// Navigate the response structure
PricesTable = Table.FromRecords(ParsedResponse[data])
in
PricesTable
To configure the API key credential in Power BI Desktop, go to the data source settings for https://api.marketdatavendor.com/v3/, edit credentials, and choose "Web API" authentication. Enter the header name (typically X-API-Key or Authorization) and the key value. Power Query will inject this header into every request to that base URL without it ever appearing in your M code.
Warning: The URL matching for credential injection is prefix-based. If your BaseUrl is
https://api.marketdatavendor.com/v3/, the credential will be used for any request whose URL starts with that prefix. This is usually what you want, but be careful if the same domain hosts both sensitive and non-sensitive endpoints — you may want to use a more specific prefix to avoid injecting credentials into public endpoint calls.
For data sources requiring OAuth2, Power Query has built-in support through the "Organizational account" credential type. However, for service-to-service scenarios (where a human user isn't in the loop), you'll often need to implement your own token acquisition.
The challenge is that M is a functional language without state. You can't store a token in a variable that persists between query evaluations. Every time your query runs, you need to acquire a fresh token (or implement a caching strategy at the infrastructure level).
Here's a pattern for acquiring an OAuth2 client credentials token within a query:
// OAuthTokenQuery - acquires a service-to-service token
// Note: ClientId is non-sensitive; ClientSecret must come from credential store
let
// These are non-sensitive configuration values
TenantId = "your-tenant-id-here",
ClientId = "your-client-id-here",
TokenEndpoint = "https://login.microsoftonline.com/" & TenantId & "/oauth2/v2.0/token",
Scope = "https://yourdataplatform.azure.com/.default",
// The client secret is stored as a Web API credential for the token endpoint URL
// Power Query injects it via the credential store - do NOT hardcode here
// In practice, you configure "Basic" auth with ClientId as username
// and ClientSecret as password for the token endpoint
TokenRequest = Web.Contents(
TokenEndpoint,
[
Content = Text.ToBinary(
"grant_type=client_credentials"
& "&client_id=" & ClientId
& "&scope=" & Uri.EscapeDataString(Scope)
),
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded"
]
]
),
TokenResponse = Json.Document(TokenRequest),
AccessToken = TokenResponse[access_token],
// Now use the token for the actual API call
DataEndpoint = "https://yourdataplatform.azure.com/api/v1/",
DataResponse = Web.Contents(
DataEndpoint,
[
RelativePath = "transactions",
Headers = [
Authorization = "Bearer " & AccessToken
]
]
),
Data = Json.Document(DataResponse)
in
Data
Critical Security Note: This pattern puts a valid access token in a query expression that the M engine evaluates. The token value will appear in query diagnostic logs, mashup engine traces, and potentially error messages. For high-security environments, the better approach is to use Power Query's native OAuth2 connector framework or implement a credential proxy service that sits between Power Query and your target API. The pattern above is pragmatic but should be used with awareness of its logging implications.
Now let's put everything together in a realistic enterprise scenario. You're building a Customer 360 pipeline that combines:
The naive approach would be to build one big query that joins all four sources. Let's examine why that's problematic and how to restructure it correctly.
// BAD PATTERN - Don't do this
let
CRMData = Salesforce.Tables(...),
TransactionData = AzureSql.Database(...),
EnrichmentData = Web.Contents("https://private-enrichment-api.vendor.com/..."),
CountryRef = Web.Contents("https://data.un.org/countries.json"),
// This join chain forces the formula firewall to evaluate
// a query plan that combines Private, Organizational, and Public sources
// in a single expression graph
Combined = Table.Join(
Table.Join(
Table.Join(CRMData, TransactionData, "CustomerID", "CustomerID"),
EnrichmentData, "CustomerID", "EntityID"
),
CountryRef, "CountryCode", "ISO2"
)
in
Combined
The formula firewall will either block this entirely or produce a query plan with terrible performance, because it cannot allow any folding across the Private enrichment source boundary, which cascades to block folding on the sources joined to it.
The solution is to separate your query graph into stages that respect privacy boundaries:
// Stage 1: Organizational data combination (folding permitted between these)
// Query name: CustomerTransactionBase
let
CRMSource = Salesforce.Tables(
"https://yourorg.salesforce.com",
[ApiVersion = "54.0"]
),
CustomerTable = CRMSource{[Name="Contact"]}[Data],
CustomerFiltered = Table.SelectColumns(
CustomerTable,
{"Id", "FirstName", "LastName", "Email", "AccountId", "Country__c", "CreatedDate"}
),
// Azure SQL - same organizational trust level, folding permitted
TransactionSource = AzureSql.Database(
"yourdw.database.windows.net",
"SalesDatabase",
[Query = "
SELECT
c.CustomerID,
c.SalesforceContactId,
SUM(t.Amount) as TotalRevenue,
COUNT(t.TransactionID) as TransactionCount,
MAX(t.TransactionDate) as LastTransactionDate
FROM dbo.Customers c
JOIN dbo.Transactions t ON c.CustomerID = t.CustomerID
WHERE t.TransactionDate >= DATEADD(month, -12, GETDATE())
GROUP BY c.CustomerID, c.SalesforceContactId
"]
),
// This join is between two Organizational sources - formula firewall permits folding
JoinedData = Table.Join(
CustomerFiltered,
TransactionSource,
"Id",
"SalesforceContactId",
JoinKind.LeftOuter
),
// Enrich with Public reference data - permitted with Organizational sources
CountryRef = Web.Contents(
"https://raw.githubusercontent.com/lukes/ISO-3166-Countries/master/all/all.json"
),
CountryTable = Table.FromRecords(Json.Document(CountryRef)),
WithCountryNames = Table.Join(
JoinedData,
CountryTable,
"Country__c",
"alpha-2",
JoinKind.LeftOuter
),
FinalBase = Table.SelectColumns(
WithCountryNames,
{
"Id", "FirstName", "LastName", "Email",
"TotalRevenue", "TransactionCount", "LastTransactionDate",
"Country__c", "name"
}
)
in
FinalBase
// Stage 2: Private enrichment data - isolated query
// Query name: EnrichmentScores
// Privacy level for the enrichment API MUST be set to Private
let
// Only fetch the minimum necessary from the private source
// Never pull full records if you only need scores
EnrichmentEndpoint = "https://api.enrichmentvendor.com/v2/",
// Customer IDs to score - we get these from the base query
// but pass them as a parameter, not exposing the full dataset to the API
CustomerIDs = List.Transform(
Table.ToList(
Table.SelectColumns(CustomerTransactionBase, {"Id"})
),
each _{0}
),
// Batch request to minimize API calls
BatchSize = 100,
CustomerBatches = List.Split(CustomerIDs, BatchSize),
FetchBatch = (batch as list) =>
let
RequestBody = Json.FromValue([
entity_ids = batch,
score_types = {"demographic_propensity", "churn_risk"},
version = "v3"
]),
Response = Web.Contents(
EnrichmentEndpoint,
[
RelativePath = "batch/scores",
Content = RequestBody,
Headers = [#"Content-Type" = "application/json"]
]
),
ParsedResponse = Json.Document(Response)
in
ParsedResponse[scores],
AllScoreRecords = List.Combine(
List.Transform(CustomerBatches, FetchBatch)
),
ScoresTable = Table.FromRecords(AllScoreRecords),
// Keep only the fields we'll join on and the scores themselves
ScoresFiltered = Table.SelectColumns(
ScoresTable,
{"entity_id", "demographic_propensity", "churn_risk", "score_date"}
)
in
ScoresFiltered
// Stage 3: Final combination - joins Private and Organizational results
// This join happens in the M engine's memory, after both sources have
// returned data independently. No folding occurs across this boundary,
// which is correct behavior.
// Query name: Customer360Final
let
BaseData = CustomerTransactionBase,
Enrichment = EnrichmentScores,
// This join is safe: both datasets are already in memory as M values
// The formula firewall has already evaluated each source independently
// No data from BaseData was sent to EnrichmentScores, and vice versa
Combined = Table.Join(
BaseData,
Enrichment,
"Id",
"entity_id",
JoinKind.LeftOuter
),
// Final column selection and typing
Typed = Table.TransformColumnTypes(
Combined,
{
{"TotalRevenue", Currency.Type},
{"TransactionCount", Int64.Type},
{"demographic_propensity", type number},
{"churn_risk", type number},
{"score_date", type date}
}
)
in
Typed
Architecture Insight: Notice that the final join between
CustomerTransactionBase(Organizational) andEnrichmentScores(Private) is safe precisely because each query ran independently. TheCustomerTransactionBasequery finished and returned a table of results. TheEnrichmentScoresquery finished and returned a table of results. The final join inCustomer360Finalis just combining two already-materialized M tables. The formula firewall's job was done at the individual query level. This is the key insight that makes staged pipelines work.
One of the most overlooked aspects of enterprise Power Query management is ongoing auditing. Privacy levels can get misconfigured silently — a new data source gets added without a privacy level, a gateway data source gets reconfigured, or someone updates a connection string that invalidates the credential mapping.
You can use M's diagnostic capabilities to inspect aspects of your query configuration. Here's a query that helps you document and verify your source topology:
// PrivacyAuditReport
let
// Define your expected source configuration
ExpectedSources = Table.FromRecords({
[
SourceName = "Internal DW",
ConnectionType = "SQL Server",
ServerOrBaseUrl = "dw-prod.corp.internal",
ExpectedPrivacyLevel = "Organizational",
ContainsPII = false,
DataOwner = "data-platform-team@corp.com",
LastReviewed = #date(2024, 11, 1)
],
[
SourceName = "HR Core System",
ConnectionType = "SQL Server",
ServerOrBaseUrl = "hr-prod.corp.internal",
ExpectedPrivacyLevel = "Private",
ContainsPII = true,
DataOwner = "hr-systems@corp.com",
LastReviewed = #date(2024, 11, 1)
],
[
SourceName = "Enrichment Vendor API",
ConnectionType = "Web",
ServerOrBaseUrl = "https://api.enrichmentvendor.com/v2/",
ExpectedPrivacyLevel = "Private",
ContainsPII = false,
DataOwner = "vendor-relations@corp.com",
LastReviewed = #date(2024, 11, 1)
],
[
SourceName = "World Bank Open Data",
ConnectionType = "Web",
ServerOrBaseUrl = "https://api.worldbank.org/v2/",
ExpectedPrivacyLevel = "Public",
ContainsPII = false,
DataOwner = "data-platform-team@corp.com",
LastReviewed = #date(2024, 11, 1)
]
}),
// Calculate days since last review
Today = Date.From(DateTime.LocalNow()),
WithReviewAge = Table.AddColumn(
ExpectedSources,
"DaysSinceReview",
each Duration.Days(Today - [LastReviewed]),
Int64.Type
),
// Flag sources overdue for review (>90 days)
WithReviewStatus = Table.AddColumn(
WithReviewAge,
"ReviewStatus",
each if [DaysSinceReview] > 90 then "OVERDUE"
else if [DaysSinceReview] > 60 then "Due Soon"
else "Current",
type text
),
// Flag high-risk combinations
WithRiskFlag = Table.AddColumn(
WithReviewStatus,
"RiskLevel",
each if [ContainsPII] and [ExpectedPrivacyLevel] <> "Private"
then "HIGH - PII in non-Private source!"
else if [ExpectedPrivacyLevel] = "Organizational"
then "Medium - Verify Org boundary controls"
else "Standard",
type text
)
in
WithRiskFlag
This won't automatically detect misconfigured privacy levels in the Power BI service (that requires the Admin API), but it creates a living document of your intended configuration that can be compared against actual settings during audits.
For truly enterprise-scale auditing, you should complement your Power Query configuration with programmatic auditing using the Power BI Admin REST API. The dataset datasources endpoint returns data source information that you can compare against your expected configuration:
GET https://api.powerbi.com/v1.0/myorg/admin/datasets/{datasetId}/datasources
This returns the actual connected data sources for a dataset. You can build a Power Query dataset from this API to create a real-time dashboard of your organization's data source topology and compare it against your expected configuration baseline.
You're going to build a simplified version of the Customer 360 pipeline we designed above, using publicly available data sources to simulate the multi-trust-level scenario. This exercise will give you direct experience with privacy level conflicts and resolution.
Open Power BI Desktop and create a new report file.
Create the following parameters:
Environment (Text, default: "development")ApiRateLimit (Whole Number, default: 100)Create a new blank query named CountryReference with this M code:
let
Source = Web.Contents(
"https://raw.githubusercontent.com/stefangabos/world_countries/master/data/en/countries.json"
),
Parsed = Json.Document(Source),
AsTable = Table.FromList(
Parsed,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
ExpandedColumns = Table.ExpandRecordColumn(
AsTable,
"Column1",
{"id", "alpha2", "alpha3", "name"},
{"CountryID", "Alpha2Code", "Alpha3Code", "CountryName"}
)
in
ExpandedColumns
Set the privacy level for raw.githubusercontent.com to Public.
For this exercise, use a SharePoint list or an Excel file on OneDrive for Business to simulate an internal organizational source. Create a simple customer table with columns: CustomerID, Name, Email, CountryCode.
Connect to it and name the query InternalCustomers. Set its privacy level to Organizational.
Create a second Excel file (locally) with columns: CustomerID, SegmentScore, RiskCategory. Connect to it as a local file. Set its privacy level to Private.
Name the query PrivateSegmentScores.
Create a query NaiveJoin that joins all three sources directly:
let
Customers = InternalCustomers,
Scores = PrivateSegmentScores,
Countries = CountryReference,
Step1 = Table.Join(Customers, Scores, "CustomerID", "CustomerID"),
Step2 = Table.Join(Step1, Countries, "CountryCode", "Alpha2Code")
in
Step2
Observe what happens. Depending on your privacy level settings, you'll either see an error about combining data sources with different privacy levels, or the query will run but you'll get a warning in the query editor.
Now refactor. Create a CustomerWithCountry query that joins only the Organizational and Public sources:
let
Customers = InternalCustomers,
Countries = CountryReference,
Joined = Table.Join(
Customers,
Table.SelectColumns(Countries, {"Alpha2Code", "CountryName"}),
"CountryCode",
"Alpha2Code",
JoinKind.LeftOuter
)
in
Joined
Then create Customer360 that does the final join with the Private source:
let
BaseCustomers = CustomerWithCountry,
Scores = PrivateSegmentScores,
Final = Table.Join(
BaseCustomers,
Scores,
"CustomerID",
"CustomerID",
JoinKind.LeftOuter
)
in
Final
Compare the behavior. The staged approach should work correctly because the Private-to-Organizational combination happens at the M engine level, not at the query optimization level.
Customer360 still combines Private and Organizational data?The error message "This operation is not supported for this data source combination" looks like a permissions or network error. It's not. It's the formula firewall refusing to combine sources with incompatible privacy levels.
Diagnosis: Go to File → Options and Settings → Options → Privacy in Power BI Desktop. If "Combine data according to your Privacy Level settings for each source" is selected (the default), privacy levels are being enforced. If "Ignore the Privacy Levels and potentially improve performance" is selected, the firewall is disabled. The latter should never be used in production — it's only for development exploration.
Resolution: Audit your data source privacy levels. Find the source that's missing or incorrectly configured and set it appropriately.
If you construct your API base URL dynamically (using parameters, environment lookups, etc.), Power Query may not be able to match the constructed URL against a stored credential.
// This BREAKS credential matching:
BaseUrl = "https://api." & Environment & ".yourservice.com/",
// This WORKS - use a static base URL, make path dynamic:
BaseUrl = "https://api.yourservice.com/",
RelativePath = Environment & "/data/endpoint"
Resolution: Always use static literal strings for the first argument to Web.Contents. Make dynamic parts of the URL use RelativePath and Query parameters instead.
As mentioned earlier, privacy levels set in Power BI Desktop are machine-local. After publishing to the service, all privacy levels reset to unset.
Resolution: Build a deployment checklist that explicitly lists every data source and its required privacy level. After every publish, walk through the data source credentials settings in the Power BI service and verify each one. Consider automating this check using the Admin API.
If your dataset connects to on-premises data through a gateway, both the gateway data source and the dataset data source need matching or compatible privacy levels. A common failure mode: the gateway data source is set to Private, but the dataset has it as Organizational. The gateway's setting wins, causing unexpected folding restrictions.
Resolution: Establish a policy that gateway privacy level settings are managed by the gateway admin, and dataset authors are informed of those settings during the onboarding process for each data source. Document this in your data governance register.
API calls made via Web.Contents include URL query parameters in request logs, access logs on the API server, and potentially in intermediary proxy logs. Never pass sensitive values (customer IDs in bulk, PII, tokens) as URL query parameters.
// BAD - CustomerIDs appear in server access logs:
Response = Web.Contents(
BaseUrl,
[
RelativePath = "lookup",
Query = [customer_ids = Text.Combine(CustomerIDList, ",")]
]
)
// BETTER - CustomerIDs go in the POST body (still logged in some environments,
// but not in standard access logs):
Response = Web.Contents(
BaseUrl,
[
RelativePath = "lookup",
Content = Json.FromValue([customer_ids = CustomerIDList]),
Headers = [#"Content-Type" = "application/json"]
]
)
Leaving privacy levels unset (None) is not neutral — it's a ticking time bomb in scheduled refresh scenarios. Power Query can't make firewall decisions without a privacy level, so it will block any cross-source combination involving a None source.
Resolution: Make it a team standard that every data source connection must have an explicit privacy level before a report is published. Include a privacy level audit as part of your report review checklist.
Privacy levels have direct performance consequences, and understanding the performance model helps you make better architectural decisions.
When the formula firewall prevents folding across a source boundary, both sources must return full result sets to the M engine, which then performs the join in memory. For large datasets, this is catastrophic for performance. A join that would have been handled efficiently by the database engine (via folded query) instead requires transferring millions of rows across a network connection into M's memory.
The mitigation strategies are:
Pre-aggregation: Before the cross-privacy-level join, aggregate your data to the minimum grain needed. If you're joining a Private enrichment source against an Organizational transaction history, don't join the full transaction table — pre-aggregate to a customer-level summary first. This reduces the data volume that must be materialized in M memory.
Explicit filtering at the source: Use native database queries (via the Query option in Sql.Database) to pre-filter data before it reaches M. Since the filtering happens inside the trusted source, no cross-boundary data transfer is needed.
Incremental refresh coordination: For large datasets with Private sources, consider whether the Private data truly needs to be refreshed in every pipeline run. If demographic enrichment scores change infrequently, you might refresh the Private-source-dependent portion weekly while refreshing the Organizational-source portions daily.
Dataflow staging: Power BI Dataflows allow you to materialize intermediate results in Azure Data Lake Storage. If you have a complex multi-source pipeline with privacy-level constraints, consider breaking it into two dataflows: one that handles Organizational-level data and materializes to a Dataflow entity, and a second that combines the materialized entity (now a single, classified source) with Private data. This pattern can dramatically improve performance and simplifies privacy level management.
You've covered substantial ground in this lesson. Let's consolidate the key insights:
The formula firewall is a query optimization constraint, not an access control. It prevents one source's data from being sent to another source during query planning. Understanding this distinction explains both why it exists and how to work with it correctly.
Privacy levels are a classification system, not a permissions system. They tell Power Query how to treat data during query optimization, not whether a user can access a source. Your actual access controls live in your database, API gateway, and network infrastructure. Privacy levels determine how Power Query behaves when combining data from those systems.
Staged architecture is the correct pattern for multi-trust-level pipelines. Separate your query graph into stages that respect privacy boundaries, perform Organizational-level combinations first, then combine with Private data at the M engine level (where no folding occurs). This is architecturally sound, security-correct, and helps you reason about data flow clearly.
Credentials belong in the credential store, never in M code. Use parameterized connection strings for environment portability, but always ensure the base URL or server name resolves to a stored credential. Never embed secrets in M expressions.
Audit continuously. Privacy levels are machine-local in Desktop and can drift after publishing or gateway reconfiguration. Build auditing into your deployment and maintenance processes.
To build on this foundation, explore:
Power BI Dataflows and Power Query Online: The credential and privacy management model in Power Query Online (used by Dataflows, Power Apps Dataverse, and Fabric) differs from Desktop in important ways. Understanding these differences is essential for enterprise-scale deployments.
Custom Connectors and Privacy Level Annotations: If you're building custom Power Query connectors, you can annotate them with privacy level metadata that Power Query uses to make better firewall decisions automatically. The Power Query SDK documentation covers the PrivacyLevel attribute.
Azure Purview Integration: Microsoft Purview can scan Power BI datasets and classify data sources based on their content. Learning to integrate Purview's classification output with your Power Query privacy level configuration creates a genuinely automated data governance pipeline.
Power BI REST API for Governance: The Admin and Dataset APIs let you build automated compliance checks that verify privacy level configuration at scale across your entire Power BI tenant. Combining this with your CI/CD pipeline creates robust deployment gates.
Row-Level Security and Privacy Levels: Understanding how RLS interacts with privacy levels in shared datasets is a separate but related security concern. A dataset with correctly configured privacy levels but misconfigured RLS can still expose data — the two systems are complementary, not redundant.
Learning Path: Power Query Essentials