Merging Web Data with Local Tables: Privacy Level collisions.

Merging web data with local tables in Power Query triggers a privacy level collision when the engine cannot verify whether local data passed into the merge will be transmitted over the internet. To safeguard local files, Power Query blocks queries that combine high-privacy local inputs with unauthenticated web endpoints.

Quick Answer:

Match the privacy settings for both sources: open Data Source Settings, find your Web URL and local file path, and set both to Organizational. If the web API uses local parameters directly inside a Web.Contents call, restructure the call using the RelativePath and Query options to maintain firewall compliance.

---

The Mechanism Behind Web and Local Collisions

Power Query categorizes connections into three privacy levels:

• Private: Confidential data that should never be sent outside the local environment.
• Organizational: Internal data that can be shared across company servers and domain boundaries, but not publicly.
• Public: Open endpoints where submitted parameters may be logged or observed.

By default, an external API or URL pulled via Web.Contents or Web.Page is treated as Public or untrusted. A local Excel table is frequently treated as Private or left unassigned.

When you execute a step that combines them, such as filtering a web table against a list of local client IDs, the firewall blocks the operation. It does this because an unvetted web request could append private local values to the web URL string as query parameters, leaking proprietary information to external server logs.

[Local Data: "Confidential_Client_45"]
                 ↓ (Attempted merge)
[Web Endpoint: <https://api.dataprovider.com/lookup>]
                 ↓
[Power Query Mashup Engine Interception: FIREWALL COLLISION]

---

Diagnostic Step: Check Assigned Privacy Boundaries

1. Open Excel and click Data > Get Data > Data Source Settings.
2. Select Data sources in current workbook.
3. Locate your Web address (e.g., https://api.example.com) and your local workbook or file system entry.
4. If either entry reads Privacy Level: None, Power Query prompts you repeatedly or throws a firewall collision.
5. Highlight the Web URL, click Edit Permissions, and adjust the level. Repeat for the local data source.

Rules for Collision-Free Execution:

• If Source 1 is Public, Source 2 can only interact with it if Source 2 does not send data outbound.
• If Source 1 is Organizational, Source 2 must also be Organizational.
• A Private source cannot merge directly with a Public source under active firewall conditions.

Review the structural hierarchy in How to fix Privacy Level Mismatches: Public vs. Organizational vs. Private for detailed source-tier combinations.

---

Fixing Parameterized Web Calls in M-Code

If your local table supplies search terms, dates, or product codes to an API, building the URL via simple text concatenation triggers a collision:

// Bad: Triggers Firewall Collision and Web.Contents Errors
let
    LocalParam = Excel.CurrentWorkbook(){[Name="SearchTerm"]}[Content]{0}[Term],
    Response = Json.Document(Web.Contents("<https://api.example.com/v1/search?q=>" & LocalParam))
in
    Response

The Mashup Engine flags "<https://api.example.com/v1/search?q=>" & LocalParam as an unvalidated dynamic URL constructed from local memory, breaking firewall and query folding boundaries.

The Compliant M Pattern

Rewrite the call using RelativePath and Query arguments inside Web.Contents:

// Compliant: Isolates base URL and cleanly passes parameters
let
    LocalParam = Excel.CurrentWorkbook(){[Name="SearchTerm"]}[Content]{0}[Term],
    Response = Json.Document(
        Web.Contents(
            "<https://api.example.com>",
            [
                RelativePath = "v1/search",
                Query = [q = Text.From(LocalParam)]
            ]
        )
    )
in
    Response

Using this format, the base URL remains static (https://api.example.com), allowing Power Query to establish and validate authentication and privacy levels against that root address before evaluating query parameters.

If this step produces URL formation errors, consult Troubleshooting Web.Contents errors: “The provided URI scheme is invalid.”.

---

Advanced Troubleshooting: Combining Web Calls with Local Merges

If you must join a loaded web dataset with a loaded local table:

1. Buffer Local Data: Use Table.Buffer on your local staging table prior to the join step. This forces the local dataset into memory before the web connection begins downstream operations, preventing the engine from attempting simultaneous execution: BufferedLocal = Table.Buffer(Staging_LocalTable)
2. Review Global Privacy Settings: In dedicated reporting environments where data egress is monitored at the network proxy level, you can disable workbook-level privacy checks. See Understanding the “Ignore Privacy Levels” setting: Risks and Rewards for procedural instructions and operational trade-offs.
3. Handle Mashup Failures: If you have restructured queries and aligned permissions but the refresh fails immediately upon execution, refer to Power Query Privacy & Firewall Guide: Fixing Data Mashup Errors to diagnose remaining query barriers.