Power Query Problems

How to Save a Filtered Power Query Result to a New Excel Workbook

John Wilson Sep 28, 2026 868 views

Question details

The user needs to save a large dataset, which has been filtered using Power Query, into a new Excel workbook. The query is currently loaded only as a connection to the Data Model, and copying the table only retrieves the first 1,000 rows.

How to Save a Filtered Power Query Result to a New Excel Workbook

Product: Microsoft Excel
Device & OS: not provided
Scenario: Exporting or saving a large, filtered CSV dataset from Power Query into a completely separate Excel workbook for further use.
Observed behavior: The worksheet displays only one row, or the 'Copy Entire Table' function only copies a 1,000-row preview instead of the hundreds of thousands of filtered records in the Data Model.

Before you start

Ensure your filtered dataset does not exceed Excel's maximum worksheet limit of 1,048,576 rows if you plan to load the results directly into an Excel table.

Solution 1Recommended

Copy the Query via Power Query Editor

The most reliable method to transfer the full dataset to a new workbook is to copy the underlying query itself from within the Power Query Editor, rather than copying the preview data.

Copying from the Queries & Connections pane or using 'Copy Entire Table' will only retrieve the 1,000-row preview. By copying the actual query script, you instruct the new workbook to process and load the entire dataset directly.

Launch Power Query Editor

In your original Excel workbook, go to the Data tab on the ribbon, click Get Data, and select Launch Power Query Editor.

Copy the Query

In the Queries pane on the left side of the Power Query Editor, right-click your final filtered query and select Copy.

Paste into a New Workbook

Open a new blank Excel workbook. Go to Data > Get Data > Launch Power Query Editor. In the new editor's Queries pane, right-click the empty space and select Paste.

Close & Load

Click the Close & Load button on the Home tab. The new workbook will process the query and load the full filtered dataset into a new worksheet table.

Data Transfer Complete: You can now save this new workbook. The query will remain functional and can be refreshed in the new file as long as the source CSV file remains in its original location.

Solution 2

Change the Load Destination to a Table

If you want to view the data in a worksheet instead of just keeping it in the Data Model, you must change how the query is loaded in your current workbook.

Free Microsoft Office alternative

Handle Large Datasets Smoothly with WPS Office

If you are struggling with Excel's Power Query connection limits or complex data models, try WPS Office. It provides a lightweight, fast, and completely free alternative for managing large CSV files and standard spreadsheets with full Microsoft Office format compatibility.

1. Open WPS Spreadsheets: Launch WPS Office and create a new blank spreadsheet.
2. Import your Data: Navigate to the Data tab and choose Import Data to seamlessly load your large CSV file.
3. Filter and Save: Use the built-in AutoFilter tool to instantly narrow down your records, then click File > Save As to create a new standard workbook.

100% compatible with Microsoft Excel formats (.xlsx, .csv, .xls)Lightweight architecture optimized for fast processing of large datasetsFree to use with a familiar, user-friendly spreadsheet interfaceSeamless transition with no steep learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does 'Copy Entire Table' in Power Query only copy 1,000 rows?

The Power Query Editor is designed to only load a preview of the first 1,000 rows to optimize performance and prevent your computer from freezing while editing. To get all records, you must load the query to an Excel table using the Close & Load function.

Why is my Power Query result only showing as a connection?

During the initial setup, the query's load settings were likely configured to 'Only Create Connection' and 'Add this data to the Data Model.' You need to right-click the connection, choose 'Load To', and select 'Table' to view the data physically in a worksheet.

Can I load more than 1 million rows into an Excel worksheet?

No, Excel worksheets have a strict physical limit of 1,048,576 rows. If your filtered result exceeds this limit, you cannot load it into a Table. You must keep it as a Data Model connection or export the data directly to a CSV file using external tools.

Excel & Data Problems