Excel Power Query Connection-Only Queries and the Worksheet Row Limit
Question details
The user wants to know whether Excel Power Query can process more than 1,048,576 rows when a query is kept as a connection-only query instead of being loaded directly to a worksheet.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Large data sets are transformed or merged in Power Query, and a later query needs to filter or reduce the results before loading them into Excel.
- Observed behavior
- The worksheet output is limited to 1,048,576 rows, while connection-only queries and data-model processing can handle larger data sets depending on available resources and query design.
Check whether the query is being loaded to a worksheet, the data model, or as connection-only. Also confirm that the source and dependent queries are available in the Queries & Connections pane before changing the load settings.
Keep the large query connection-only and load a filtered dependent query
Use this approach when the source contains more than 1,048,576 rows but the final worksheet only needs a smaller result.
The Excel worksheet row limit applies to the result loaded into a worksheet, not necessarily to data processed inside Power Query. A dependent query can reference the larger query, filter or transform it, and then load only the reduced output.
Connection-only processing is not unlimited. Memory, processor speed, source type, query folding, and the size of merges can significantly affect performance.
In Excel, select Data > Get Data and open Power Query Editor, or select Data > Queries & Connections and double-click the source query.
In the Queries & Connections pane, right-click the large query, select Load To, choose Only Create Connection, and click OK.
In Power Query Editor, right-click the large query in the Queries pane and select Reference to create a second query that uses it as its source.
Use the filter buttons on the relevant columns, or choose Home > Keep Rows or Remove Rows, to reduce the result to the records needed for the worksheet.
Select Home > Close & Load To, choose Table and a worksheet location, then click OK. Keep the output below Excel's 1,048,576-row worksheet limit.
Use the data model or an external destination for very large results
Consider this option when the full result must remain available for analysis and cannot fit into an Excel worksheet.
Move Everyday Spreadsheet Work to WPS Office Without Changing Your File Workflow
WPS Office is a free, lightweight Microsoft Office alternative for opening, editing, and sharing compatible spreadsheets. Its familiar interface supports a smooth migration for everyday workbook tasks, while specialized Microsoft Excel Power Query workflows should remain in an environment that supports them.
- 1. Download WPS Office: Select Download WPS Office Free and install the spreadsheet application on your computer.
- 2. Open the workbook: In WPS Spreadsheet, select Menu > Open and choose the XLSX or XLS workbook you want to review or edit.
- 3. Continue spreadsheet editing: Use the Home, Data, Formulas, and Insert tabs for supported spreadsheet tasks, then save the file in its existing Microsoft Excel format when needed.
Frequently Asked Questions
Does a connection-only Power Query query bypass Excel's 1,048,576-row worksheet limit?
It avoids the worksheet output limit because the query is not writing all rows to a sheet. Power Query can still process larger data sets, but available memory, processor capacity, source behavior, and query complexity determine whether the operation completes successfully.
How can I load only part of a large Power Query result into Excel?
Keep the original query as connection-only, create a reference query from the Queries pane, apply filters or row-reduction steps, and use Home > Close & Load To to load the smaller result as a worksheet table.
Can a second query reference a connection-only query?
Yes. In Power Query Editor, right-click the existing query and select Reference. The new query can filter, merge, or transform the referenced data before it is loaded.
Is the Excel Data Model unlimited for large Power Query results?
No. The Data Model is not restricted by the worksheet's row count in the same way, but it has separate memory, storage, and processing limits. Large relationships and merges may still require significant resources.
Why can a large Power Query merge take more than an hour?
Large merges may need to compare and materialize millions of rows. Performance depends on source type, query folding, join design, available RAM and processing power, and whether unnecessary columns or rows are removed before the merge.
