Power Query Problems

How to Preserve Static Excel Data When Refreshing Power Query Results

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs a method to keep manually entered tracking data aligned with the correct records after refreshing a dynamic Power Query table.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Adding manual progress tracking columns next to an Excel table that is populated and updated by Power Query.
Observed behavior: Refreshing the Power Query changes the row order of the results, causing the manually entered static data in adjacent columns to become misaligned and associated with the wrong rows.

Before you start

Ensure your source data contains a unique, stable identifier for each row (such as an Employee ID, Task ID, or Product Code), as this key is strictly required to map your static data back to the dynamic query results accurately.

Solution 1Recommended

Use a Self-Referencing Table Pattern in Power Query

Create a self-referencing query to merge your manually entered static data back into the refreshed Power Query results based on a unique identifier.

When manual columns are added directly to an Excel table loaded from Power Query, Excel anchors this static data to the row positions. If a query refresh adds, removes, or sorts rows, the dynamic data shifts while the static data stays in place, causing a mismatch.

The self-referencing table pattern resolves this by loading the final output table (which includes your manually added columns) back into Power Query as a new source. By merging this new query with your original query using a unique key, you lock the manual data to the correct records regardless of row order changes.

Load your base query to an Excel table

Create your initial data query in Power Query and load the results as a Table into a new Excel worksheet.

Add manual tracking columns

Type your new column headers (for example, 'Status' or 'Notes') directly next to the loaded Excel table. Excel will automatically expand the table's boundaries to include these new columns.

Load the expanded table back into Power Query

Select any cell within this updated table, navigate to the Data tab on the Excel ribbon, and click 'From Table/Range'. This creates a self-referencing query containing both your dynamic data and your manual entries.

Merge the original and self-referencing queries

Open your original base query in the Power Query Editor. Click 'Merge Queries' on the Home tab, select the self-referencing query from the dropdown, and click the unique identifier column (e.g., Task ID) in both table previews to join them.

Expand and load the final data

Click the expand icon at the top of the newly merged column. Uncheck all columns except your manually added ones (e.g., 'Status', 'Notes'). Click Close & Load to update your Excel table. Your manual data will now remain attached to the correct rows upon refresh.

Handling Circular References: Because the query reads from and writes to the same Excel table, you might occasionally see a data source error if you modify the structure aggressively. Always ensure your unique key has no blank or duplicate values to prevent data multiplication during the merge.

Free Microsoft Office alternative

Need a Lightweight, Fast Alternative for Data Management?

If complex Power Query setups and self-referencing loops are slowing down your workflow, consider switching to WPS Office. It provides a highly compatible spreadsheet environment for handling large datasets using familiar formulas and pivot tables, without the heavy overhead of advanced query merges.

1. Visit the Official Website: Go to the official wps.com website and click the Free Download button.
2. Install WPS Office: Run the downloaded installer and follow the simple on-screen instructions to set up the software.
3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel workbooks to manage your data with full compatibility.

Fully compatible with Microsoft Excel (.xlsx, .csv) formatsLightweight architecture ensures fast performance even with large datasetsFamiliar user interface makes migration seamless with zero learning curvePowerful built-in formulas and lookup tools for straightforward data analysisFree to download and use for everyday office productivity tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does static data shift when I refresh a Power Query table?

When Power Query refreshes, it drops the existing dataset and loads the newest version from your source. Standard Excel anchors your manually added columns to physical row numbers rather than the data itself. If the refreshed data contains new rows, deleted rows, or a different sort order, your static data stays in its original row number, causing it to misalign with the new query results.

Can I use VLOOKUP instead of a self-referencing query to keep data aligned?

Yes. A simpler alternative to the self-referencing query pattern is to keep your dynamic Power Query table separate from your manual inputs. You can maintain a separate 'Tracker' worksheet where you manually enter unique IDs alongside your static data. Then, use VLOOKUP or XLOOKUP inside your dynamic query table to pull in the static data from your Tracker sheet.

What is a unique stable key, and why is it necessary?

A unique stable key is a column (or combination of columns) that uniquely identifies every single row in your dataset and never changes over time. Common examples include an Employee ID, Invoice Number, or SKU. This key is absolutely necessary because it allows Excel and Power Query to accurately map your manually entered data back to the correct dynamic record during a merge, regardless of where that record moves.

Excel & Data Problems