Speed Up Power Query: Diagnose & Optimize Performance
Step‑by‑step guide to diagnosing Power Query slowness—from filtering and Folder.Files to memory tuning, using Power BI for large file sets, and best practices.
I have an Excel workbook that uses Power Query to merge data from 15 different sources—Excel files stored both on a server and in SharePoint. Two years ago the queries ran in seconds, but over the past year and a half they’ve become painfully slow: steps can take hours, Excel sometimes freezes, and the Microsoft.Mashup.Container.Loader.exe process swallows a ton of resources. IT folks keep saying that Excel/Power Query isn’t fit for analytics. How can I boost Power Query performance in this situation, fix the process‑resource issue, and is Power Query really unsuitable for handling a large number of files?
Accelerating queries and easing the load on Microsoft.Mashup.Container.Loader.exe starts with figuring out why things slowed down, then applying concrete optimization tricks (basically, finding where the engine is stuck). Below is a set of tried‑and‑true recommendations, pulled from user reports and the official docs.
Table of Contents
- Why queries became slow?
- Diagnosing the Mashup.Container.Loader.exe process
- Optimizing Power Query itself
- Tweaking the environment and resources
- Alternatives for huge files
- When Power Query really isn’t the right tool
Why queries became slow?
You’re pulling data from 15 sources (Excel, SharePoint, a server) that now produce over 100 MB of data. If you haven’t filtered or merged early, each step pulls the entire file—like opening every workbook in full. When the files were small, that was fine, but now each step is handling gigabytes of rows, which explains the long load times.
Using SharePoint via web links often causes a double‑download: the file is fetched first, then parsed by Power Query. If the file structure changes, performance drops even more because Power Query loses query folding.
Diagnosing the Mashup.Container.Loader.exe process
| What to check | How to check | What to do |
|---|---|---|
| Background queries | In Excel: File → Options → Data → Background Data | Turn off “Allow data previews to download in the background” – it cuts memory usage and speeds up the editor. |
| Memory & CPU | Task Manager → Processes → Microsoft.Mashup.Container.Loader.exe | If it’s > 70 % RAM, look for Table.Buffer steps that keep data in memory longer than needed. |
| Power Query logs | File → Options → Diagnostics → Enable tracing → Run Tracing | Save the trace and search for “Start Query”/“End Query” for each step; that shows where parsing stalls. |
| Excel / Power Query version | File → Account → About | Make sure you’re on the latest (Excel 2021/365); newer releases improve memory handling. |
Real‑time check: In Power Query Editor hit “Advanced Editor” and prepend = TimeZone.UtcNow() to each step (like putting a timer on every task). It’ll quickly reveal which steps are the slowest.
Optimizing Power Query itself
-
Filter as early as possible
ApplyTable.SelectRowsright afterSourceand before any joins.“Filtering Data Early” is a key point in the Integrate.io blog: the fewer rows that travel downstream, the faster the whole chain.
-
Merge files via a folder
Instead of a separate query per file, useFolder.Files+Table.Combine. Think of it as putting all books in one folder. -
Minimize
Table.Buffer
It’s handy when you’ll reference a table multiple times, but it keeps data in RAM for a long time—like holding books in your hands too long. -
Optimize joins
Create a key column in each source (e.g.,VT_DNM_Quality_Date) and join on it. It’s like having a lock key. -
Reduce the number of steps
Combine several transformations into a single M expression. It’s like assembling all parts into one block. -
Use Power Query in Power BI
Power BI Desktop has a more efficient evaluation container and lets you set RAM limits via Options → Current File → Data Load → Memory usage—think of it as giving the machine more room to work.
Tweaking the environment and resources
| What to tweak | How to tweak | Why it helps |
|---|---|---|
| Container memory | Power BI Desktop: Options → Current File → Data Load → Memory usage – set max to 4 GB (or more if available) | Lets the container process large tables without frequent disk spills. |
| Parallel queries | Excel: File → Options → Advanced → Background Data → Allow parallel queries | Speeds up loading when sources are independent (you can process data in parallel). |
| Cache management | Turn off “Allow data previews to download in the background” | Reduces background load; you’ll see results faster. |
| Driver updates | Ensure the OLE DB/ODBC driver for SharePoint is current | Old drivers can trigger extra server round‑trips. |
Alternatives for huge files
- Power BI Service – export the model to the cloud and use Incremental Refresh; you only refresh new rows.
- Azure Data Factory / Synapse – for 15 files, build a pipeline that consolidates data into a Data Lake, then connect Power BI via DirectQuery.
- Power Query in Excel 365 – if you need instant interactivity, use Power Query in Power Pivot mode and attach it to the model.
- Move to .NET or Python – you can call
Microsoft.Mashupfrom C# for finer memory control, or use Python’spandasfor heavy lifting.
When Power Query really isn’t the right tool
- Volumes > 10 GB: Excel (32‑bit) can freeze because of RAM limits.
- Complex transforms with many
Table.BufferandList.Generatesteps demand a lot of RAM. - Unstable sources (SharePoint, Web) that change structure often break query folding, forcing all work to the client.
- Multi‑user scenarios (10+ users opening the same file) – Excel won’t scale; Power BI Service is a better fit.
Conclusion
- Start with diagnostics: disable background queries, check memory usage, and look for heavy steps.
- Optimize the queries: filter early, merge via folders, limit
Table.Buffer, and combine steps. - Tune the environment: bump container memory, enable parallel queries, and keep drivers up to date.
- Consider the right tool: Power BI Desktop/Service, Azure Data Factory, or even a custom .NET/Python solution for very large data sets.
- Power Query can handle many files—its performance hinges on smart architecture and proper resource allocation.
In short, Power Query isn’t a “bad” tool; it just needs the right setup and scaling strategy to keep up with growing data volumes.