Troubleshooting Power Query Issues with Large Files

Quick Answer: The "download did not complete" error in Power Query appears when a data load or refresh cannot finish, most commonly because the source file has been moved or renamed, the query cache is corrupted, or a column name in the source data has changed since the query was last configured. It is not a file size error; it is a connection or configuration error. Clear the cache, verify the source path, and duplicate the query to resolve it in most cases.

Key Takeaways:

• The "download did not complete" error is most commonly caused by a changed source file path, a corrupted query cache, or a column name mismatch in the source data. Each has a specific fix.
• Power Query struggles with large files due to hardware limitations and inefficient data processing. Upgrading to 64-bit Excel significantly improves performance.
• Splitting large files and pre-processing data before importing reduces memory usage and speeds up transformation.
• Best practices such as filtering data early and disabling background query loading help optimize Power Query performance.
• M language error handling (using try...otherwise syntax) lets you catch and manage errors at the query level without crashing the entire pipeline.
• When refresh failures become recurring or file sizes exceed what local tools can handle reliably, a cloud-based data pipeline platform solves what Power Query cannot.

Understanding Power Query and Its Common Uses

Power Query is a data connection technology developed by Microsoft, integrated into Excel and Power BI, that allows users to discover, connect, combine, and refine data across various sources. Its intuitive interface and robust capabilities make it a standard tool for data transformation and preparation.

If you want a full overview of its features and capabilities, read Mastering Power Query In Excel: A Comprehensive Guide. This article focuses specifically on troubleshooting: readers here already know what Power Query does and need to fix what's broken.

"Download Did Not Complete" Error in Power Query: Causes and Fixes

The "download did not complete" error appears in Power Query during a data load or refresh when the connection between Power Query and the source cannot be completed. It surfaces as a red error banner in the query editor or as a failed refresh notification in Excel or Power BI. It is not triggered by file size alone; it signals a broken connection or a configuration mismatch.

What Causes This Error

Step-by-Step Fix Sequence

Follow these steps in order. Most cases are resolved by steps 1 through 3.

1. Verify the data source path. In the Power Query Editor, go to Home > Data Source Settings and confirm the file path matches the current location of the source file. Update it if the file has been moved or renamed.

2. Clear the Power Query cache. In Excel, go to File > Options and settings > Options > Data and select Clear Cache. This removes stale cached data that may be causing the refresh to fail.

3. Duplicate the query and reload. Right-click the failing query in the Queries pane and select Duplicate. Load the duplicate to a new worksheet. If it loads successfully, the original query's output configuration was the issue, not the source data.

4. Check for column name changes in the source file. Open the source file directly and compare column headers against the transformation steps in your query. Any renamed column will cause a step to fail. Update the step to reference the new column name.

5. Disable background refresh and retry. Right-click the query connection in Excel, select Connection Properties, and uncheck Enable background refresh. This forces Excel to complete the refresh before returning control to the user, which resolves timing-related failures.

6. Upgrade to 64-bit Excel. If the error occurs consistently on large files and the above steps do not resolve it, the 32-bit memory ceiling may be the root cause. Switching to the 64-bit version of Excel removes the 2 GB limit and allows Power Query to handle larger datasets without failing mid-download.

When these steps don't resolve the issue: If the error persists after all six steps, the problem is likely structural. Recurring refresh failures on large or complex datasets are a signal that local tools have hit their limits. See the section "When Power Query Isn't Enough" below.

Identifying the Problem with Large Files

One of the primary issues with large files in Power Query is performance degradation. As file sizes increase, the time required for loading, transforming, and refreshing data grows significantly. This often leads to slow query performance and sometimes causes Power Query to crash unexpectedly.

Memory limitations are another frequent problem. When working with very large datasets, the system's memory can be overwhelmed, leading to out-of-memory errors or extremely sluggish performance. This is especially problematic on the 32-bit version of Excel, which has more restrictive memory limits compared to the 64-bit version.

Symptoms and Error Messages

When Power Query struggles with large files, several symptoms commonly appear:

• Slow load times: The query takes an unusually long time to load data or perform transformations.
• Out-of-memory errors: Messages like "Memory error: Memory Allocation failure" indicate that Power Query has exhausted available memory.
• Crash or freeze: Power Query or Excel becomes unresponsive during data processing.
• "Query execution failed due to memory pressure": A direct indicator of insufficient RAM for the dataset being processed.
• "Not enough storage is available to complete this operation": Signals that disk space or virtual memory is also constrained.

Impact on Workflow and Data Processing

Slow performance and frequent crashes disrupt the entire data processing pipeline, causing delays and increasing the risk of data loss. When Power Query crashes mid-transformation, unsaved work is lost and steps must be rebuilt from scratch.

These problems also affect data accuracy. When Power Query struggles with large files, there is a higher risk of incomplete data processing or errors going unnoticed, which can lead to flawed analysis downstream.

Causes of Power Query Not Working for Large Files

Large file failures in Power Query fall into three categories: hardware limitations, software constraints, and file-related problems.

Hardware Limitations

Hardware limitations are the most common root cause of Power Query failures on large files. Insufficient RAM is the primary bottleneck. Upgrading RAM makes a substantial difference in handling larger files. A more powerful CPU (Central Processing Unit) also helps, particularly for complex transformation chains that require intensive computation.

Software Constraints

Always use the latest version of Excel and Power Query. Outdated versions miss performance improvements that directly affect large file handling.

File-Related Problems

Solutions and Workarounds for Handling Large Files

Optimizing Hardware and Software Setup

Switching to 64-bit Excel is the single highest-impact software change for large file performance. The 32-bit version's memory ceiling is a hard constraint that no query optimization can work around.

Data Pre-Processing Techniques

Splitting large files into smaller chunks is one of the most effective strategies for preventing crashes. By dividing a massive dataset into several smaller files, each chunk can be processed independently and the results merged afterward. This reduces the memory load and prevents the system from being overwhelmed.

Using Efficient File Formats

CSV files are plain text with no embedded formatting, formulas, or metadata. Converting large Excel files to CSV before processing them in Power Query consistently reduces load times and memory usage.

Related Reading: CSV Formatting: Tips and Tricks for Data Accuracy

Best Practices for Using Power Query with Large Files

Tips for Efficient Data Loading and Processing

Filter data early. Applying filters at the beginning of a query drastically reduces the volume of data Power Query needs to process. If you only need data from the last 12 months, apply that date filter as the first step. This speeds up query execution and minimizes memory strain.

Disable background query loading. When Power Query loads multiple queries simultaneously in the background, it consumes significant system resources. Disabling background query loading ensures more resources are allocated to the active query. Adjust this in the query connection properties.

Techniques for Improving Performance

Incremental Data Loading

Incremental data loading is particularly beneficial for very large datasets. Instead of loading all data at once, load it in smaller, manageable chunks. Process each chunk separately, then load the next. This approach manages memory usage more effectively and prevents the system from being overwhelmed by data volume.

Using Staging Queries

Staging queries break down complex transformations into simpler, intermediate steps. For example, create one staging query to clean the data, another to filter it, and a third to merge it with other datasets. This modular approach makes it easier to troubleshoot and optimize each step independently, leading to better overall performance.

Power Query Error Handling with M Language

M language error handling lets you catch and manage errors at the query step level, preventing a single bad value from crashing an entire transformation. This is especially useful when processing large files where a small number of malformed rows would otherwise block the entire load.

The try...otherwise Pattern

The most common M language error handling pattern is try...otherwise. It attempts an expression and returns a fallback value if the expression fails.

// Return null instead of an error when a value cannot be converted
try Number.FromText([Column]) otherwise null

This replaces any conversion error in [Column] with a null value, allowing the rest of the rows to process normally.

Custom Error Messages with try...otherwise

You can return a custom string instead of null to make errors easier to identify during review:

// Return a descriptive string when conversion fails
try Number.FromText([Column]) otherwise "conversion_error"

This is useful when you want to flag problem rows for manual review rather than silently replacing them with nulls.

Value.ReplaceErrorWith vs. try...otherwise

Value.ReplaceErrorWith is applied at the column level in the Power Query Editor UI (under Transform > Replace Errors) and is equivalent to wrapping each cell in a try...otherwise null pattern, but without writing M code manually.

For teams moving beyond M language workarounds and managing data quality at scale, data transformation pipelines offer a more scalable approach with built-in error handling, alerting, and logging.

Troubleshooting and Debugging Common Issues

Step-by-Step Guide to Troubleshooting

1. Check system resources.

Ensure your system has sufficient memory and CPU power to handle large datasets. Task Manager (Windows) or Activity Monitor (Mac) can identify whether system resources are maxed out during the query process. If resources are consistently constrained, consider upgrading hardware or optimizing your current setup.

2. Review query steps.

Go through each step in the Power Query Editor methodically to identify bottlenecks. Look for transformations that could be simplified or applied earlier in the process to reduce the volume of data being processed at each stage.

3. Resolve common errors.

• Out-of-memory errors: Break data into smaller chunks or upgrade to 64-bit Excel.
• Slow performance: Remove unnecessary steps, verify data types are set correctly, and use CSV file formats where possible.
• Query execution failed: Check data types and source compatibility. Ensure all transformation steps are compatible with the current data format, particularly if the source schema has changed.

When Power Query Isn't Enough: Scaling Beyond Local File Limits

Power Query is a capable local transformation tool, but it has structural limits. When file sizes consistently exceed a few hundred megabytes, when scheduled refreshes fail repeatedly, or when you need to join data from multiple sources at scale, local tools hit a ceiling that no amount of optimization can raise.

Three specific scenarios where a cloud-based solution solves what Power Query cannot:

• Scheduled cloud-based refresh: Power Query relies on the local machine being on and available. A data pipeline platform runs refreshes on cloud infrastructure, independently of local hardware.
• Multi-source joins at scale: Combining large datasets from multiple sources (databases, APIs, file storage) in Power Query requires loading everything into local memory. Cloud pipelines process joins server-side, without local memory constraints.
• Automated error recovery: Power Query has no native alerting or retry logic. Automated pipeline monitoring detects failures, sends alerts, and can trigger retries automatically, without manual intervention.

Power Query vs. Integrate.io for Large File Scenarios

For teams exploring the broader landscape of cloud data movement, understanding what is a data pipeline and how automated data ingestion works at scale is a useful starting point. If you're evaluating whether ETL or ELT is the right approach for your architecture, the ELT vs. ETL guide covers the tradeoffs in detail.

For a direct feature comparison, see how Integrate.io compares to Power Query for data transformation.

Also worth reading: Exploring the Limitations of Power Query covers the structural constraints in more depth.

To see how Integrate.io handles large-scale data processing, schedule an intro call or start a free 14-day trial.

FAQs

What does "download did not complete" mean in Power Query?

The "download did not complete" error in Power Query means the tool could not finish loading or refreshing data from the source. This is a connection or configuration error, not a file size error. The most common causes are a changed source file path, a corrupted query cache, or a column name change in the source data since the query was built. To fix it, start by verifying the source path in Data Source Settings, then clear the Power Query cache via File > Options > Data > Clear Cache, and finally duplicate the query and reload it to a new worksheet. Most cases are resolved by one of these three steps.

How do I clear the Power Query cache?

Clearing the Power Query cache removes stale or corrupted cached data that can cause refresh failures. In Excel, go to File > Options and settings > Options, then select the Data tab and click Clear Cache. In Power BI Desktop, go to File > Options and settings > Options > Data Load and select Clear Cache. After clearing, retry the query refresh. If the error was caused by corrupted cache data, the refresh will complete successfully.

Why does Power Query fail on large CSV files but not small ones?

Power Query loads data into memory before processing it. Small CSV files fit comfortably within available RAM, while large ones can exceed the system's memory capacity. On the 32-bit version of Excel, the hard memory limit is 2 GB, which large CSV files can easily exceed. On 64-bit Excel, the limit is much higher but still constrained by physical RAM. Additionally, large files may exceed Power Query's ability to fold queries back to the source, forcing it to load the entire file locally. Upgrading to 64-bit Excel, filtering data early in the query, and splitting large files into smaller chunks all help resolve this.

How can I improve the efficiency of Power Query when importing and transforming a large Excel file composed of multiple worksheets?

Efficiency improves significantly when you buffer the data. Power Query does not cache data well inherently, but using Table.Buffer or Binary.Buffer within a single query loads the data once and reuses it across steps. Another approach is to save your worksheets as CSV files and connect Power Query to those CSV files, which are faster to process than Excel format. For detailed steps and a visual guide, see this YouTube tutorial on custom functions. For frequent updates, consider automating the CSV export process using VBA to reduce manual steps.

How can I manage a large Power BI dataset that has become too large and slow?

Start by performing transformations in your SQL source rather than in Power Query. Create curated views in SQL to simplify and pre-aggregate data before it reaches Power BI. Split the dataset into multiple smaller datasets scoped to individual reports rather than maintaining one large shared model. Use DAX Studio to identify large tables and columns that are contributing disproportionately to model size. For teams where Power BI model size has become a recurring constraint, moving data preparation upstream into a dedicated pipeline layer is the most scalable long-term solution.

Why is Power Query in Excel 2016 failing to load to the spreadsheet, giving a "Download did not complete" error?

This error in Excel 2016 is most commonly caused by changes to the source file structure after the query was originally built. Ensure the source files have not been moved, renamed, or had columns added or removed. Try duplicating the query and loading it to a new worksheet; if the duplicate loads successfully, the issue is with the original query's output configuration. Check for column name changes in the source file and update the affected transformation steps. If the error persists, reimport the data into a new sheet and rebuild the transformation steps from scratch.