“Unexpected Error” when Switching Power Query Environments

An "Unexpected Error" when switching Power Query environments occurs when moving queries between different runtime contexts, such as transitioning between Excel Desktop and Power Query Online (Excel for the Web, Power BI Dataflows, or Microsoft Fabric), or shifting between Development, Test, and Production parameter sets. This error indicates that the target environment cannot execute the query’s M-code due to mismatched engine feature builds, incompatible connector types, conflicting data privacy rules, or corrupt cached environment tokens.

Fast Fix

Clear the evaluation cache in Excel by navigating to Data > Get Data > Query Options > Data Cache Management and clicking Clear Cache. If migrating a query from Desktop to Power Query Online, verify that your M-code does not reference local disk paths (File.Contents) or desktop-only custom connectors, and align workbook privacy levels to Organizational across all sources.

---

Diagnostic Snapshot

Property	Details
Error Message	An unexpected error occurred. / Container exited unexpectedly.
Common Trigger	Copying M-code between Excel Desktop and Power Query Online, or changing parameter connection strings
Underlying Problem	Feature parity gaps between desktop and cloud M-engines, local path references in cloud, or gateway requirements
First-Line Diagnostic	Clearing Power Query local cache and testing the raw query in a blank workbook

---

Why Environment Switches Fail

The Power Query Mashup Engine exists in two primary execution environments:

1. The Local Desktop Engine: Runs as an out-of-process worker (Microsoft.Mashup.Container.*.exe) installed locally with Microsoft Office. It has direct access to local drives, local Windows credentials, and specific installed COM/ODBC drivers.
2. The Cloud Online Engine: Runs in Microsoft Azure containers (used by Excel for the Web, Dataflows, and Fabric). It operates under strict web sandboxing and cannot directly access local paths like C:\Data\ without an On-premises Data Gateway.

[Local Excel Desktop Query] ──(Pasted to Cloud / Online)──► [Cloud Mashup Sandbox]
        │                                                           │
 Uses: File.Contents("C:\...")                               CANNOT REACH LOCAL DISK:
 Uses: Windows NTLM Auth                                     REJECTS CONNECTOR / CRASHES

When you move a query between these platforms, or switch environment parameters that point from a local test database to a cloud production endpoint, the mashup engine encounters three common points of failure:

• Unsupported Connector Functions: The desktop query might use connectors or functions (such as Folder.Contents, Excel.CurrentWorkbook, or custom .mez/.pqx connectors) that do not exist or are restricted in Power Query Online.
• M Engine Build Disparities: Monthly updates to Microsoft 365 can introduce new M functions or optional arguments into desktop Excel that have not yet reached the cloud service, or vice versa.
• Data Privacy Level Collisions: When switching environments, the new environment may have strict data privacy enforcement enabled, triggering unhandled firewall failures during evaluation.

---

Step-by-Step Resolution

Step 1: Purge the Mashup Data and Preview Cache

When switching environment parameters, Power Query often tries to validate the new connection against cached schema structures from the previous environment, causing internal container crashes.

1. In Excel, go to the Data tab.
2. Click Get Data > Query Options.
3. In the left navigation pane under Global, select Data Cache Management.
4. Click Clear Cache under the Data Cache Options section.
5. Click Clear Cache under the Preview Cache section (if available).
6. Click OK, restart Excel, and re-attempt the switch.

[Query Options]
  └── Global
        └── Data Cache Management
              ├── Data Cache Options   --> [Clear Cache]
              └── Memory Limit (MB): [4096]

If the container process continues to terminate upon opening, consult How to Fix Power Query “Container exited unexpectedly” (Error 0x80040154).

---

Step 2: Replace Desktop-Specific M Functions

If you are moving queries from Excel Desktop into Power Query Online (or vice versa), remove functions that only work on local files:

1. Open the query in the Advanced Editor (Home > Advanced Editor).
2. Scan the Source step:
   • Incompatible Desktop Function: Excel.CurrentWorkbook() cannot evaluate in Power Query Online or dataflow environments.
   • Incompatible Local Drive Call: File.Contents("C:\Reports\Data.xlsx") cannot be reached by cloud containers.
3. Replace local file paths with cloud-native SharePoint or OneDrive web URLs: // Replace local file access: Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx")) // With cloud-compatible web access: Source = Excel.Workbook(Web.Contents("<https://company.sharepoint.com/sites/Data/Sales.xlsx>"))

---

Step 3: Align Environment Privacy Levels

Moving a query from a relaxed environment to a hardened enterprise environment often triggers a firewall crash rather than a graceful warning.

1. Go to Data > Get Data > Query Options.
2. Under Current Workbook, click Privacy.
3. Temporarily select Ignore the Privacy levels and potentially improve performance to test if the unexpected error disappears.
4. If this resolves the error, do not leave it permanently disabled. Instead:
   • Go to Data > Get Data > Data Source Settings.
   • Set the Privacy Level for all sources in the new environment to Organizational.

For full instructions on resolving cross-source privacy conflicts, review How to Fix Power Query “Formula.Firewall: Query references other queries” and Troubleshooting “Privacy Level” Mismatches between Workbook and Cloud.

---

Step 4: Verify Gateway Requirements for Hybrid Sources

If you switched an environment parameter to point to an on-premises SQL Server or local network file from a cloud-connected workbook:

• The cloud mashup engine cannot resolve local network names without an On-premises Data Gateway.
• Ensure the gateway is online, mapped to the correct cluster, and that your user account has User or Admin permissions on the gateway data source.

If the gateway fails to connect after switching parameters, refer to How to Fix “The gateway is unreachable” for Power Query Gateway.