Power Query Problems

Can Excel Power Query Create a New Worksheet on Every Refresh?

Aamir Naveed AkramAamir Naveed Akram Nov 22, 2026 868 views

Question details

The user wants to preserve each Power Query refresh as a separate worksheet, including rows that may later be deleted from the source.

Can Excel Power Query Create a New Worksheet on Every Refresh?
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A temporary table changes repeatedly, and the user wants a separate snapshot of its contents for each refresh without writing code.
Observed behavior
Power Query normally refreshes its existing output rather than creating a new worksheet for each run.
Before you start

Make sure the query is loaded to a worksheet table so you can copy its results. Refresh and save a snapshot while the rows you want to preserve are still present in the source.

Solution 1Recommended

Save each refresh manually as a values-only worksheet

Use this no-code method to preserve the current query output as a separate snapshot.

1
Refresh the query

In Excel, select Data > Refresh All and wait for the query output table to finish updating.

2
Copy the results

Select the query output table, including its headers, and press Ctrl+C or use the Copy command.

3
Create a snapshot sheet

Select the plus button beside the worksheet tabs to add a new sheet. Select cell A1, then use Home > Paste > Values to paste a static copy.

4
Name and save the snapshot

Rename the new worksheet with the refresh date or another identifier, then save the workbook. Repeat these steps after each refresh you want to keep.

Save each refresh manually as a values-only worksheet
Capture rows before they disappear: A snapshot only preserves rows present in the query output at the time you copy it. Power Query cannot recover rows that were deleted before the refresh.
Free Microsoft Office alternative

Review Excel snapshot workbooks in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface for working with spreadsheet files. Open compatible Excel workbooks and review saved snapshots in WPS Spreadsheets; it does not automate Microsoft Excel Power Query refreshes.

Review saved refresh snapshots on separate worksheetsOpen and edit compatible Microsoft Excel XLSX filesUse a familiar spreadsheet interface for easier migrationWork with a free, lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does Power Query keep a history of deleted source rows?

Not by default. A refresh reflects the data returned by the query at that time. Copy the output to a separate sheet before the rows disappear if you need a snapshot.

Can I create a new sheet for each refresh without code?

You can do it manually: select Data > Refresh All, copy the output table, add a worksheet, and use Home > Paste > Values. Repeat for each snapshot.

Will copying the query output as values update the snapshot later?

No. Paste > Values creates a static copy, so later query refreshes do not change that snapshot.

Can VBA create a worksheet after a Power Query refresh?

Yes. A VBA macro can be programmed to refresh the query, wait until it finishes, add a worksheet, and copy the results. Open Developer > Visual Basic to create or edit the macro.