Power Query Problems

Prevent Excel Power Query Refreshes from Misaligning Manual Data

Bushra ParveenBushra Parveen Nov 18, 2026 869 views

Question details

Keep manually entered values associated with the correct records when a Power Query result changes on refresh.

Prevent Excel Power Query Refreshes from Misaligning Manual Data
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Manual values are entered beside a worksheet table loaded from a Power Query.
Observed behavior
After a refresh changes the query result's size or order, manual values can appear beside the wrong source records.
Before you start

Identify a stable, unique ID for each source record and verify that existing manual values are matched to the correct IDs before moving them. The IDs must remain unchanged across refreshes.

Solution 1Recommended

Store manual data in a keyed table and merge it in Power Query

Use a separate table keyed by record ID so manual values are matched by identity rather than worksheet row position.

1
Create the manual data table

On a separate worksheet, enter a column for the record ID and columns for the manual values. Select the range, choose Insert > Table, and confirm that each ID appears only once.

2
Add the table to Power Query

Click inside the manual data table and select Data > From Table/Range. In Power Query, check that the ID column has the same data type as the ID in the source query, then use Home > Close & Load To to create a connection.

3
Merge by record ID

Open the source query from Data > Queries & Connections. In the Power Query Editor, select Home > Merge Queries, choose the manual data query, and select the ID column in both tables. Use a Left Outer join to keep all source records.

4
Expand and load the result

Select the expand button on the merged column, choose the manual fields you need, and select Home > Close & Load. Keep the separate manual input table outside the query output.

5
Test the refresh

Select Data > Refresh All and check that manual values remain with their matching IDs. New source IDs without a manual entry will show null values until you add them to the manual table and refresh again.

Store manual data in a keyed table and merge it in Power Query
Avoid duplicate IDs: Duplicate IDs in the manual table can create multiple output rows for a source record. A self-referencing query table is another option, but a separate keyed table is generally easier to maintain.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for editing spreadsheets and moving between Office formats. It does not fix Microsoft Excel Power Query refresh behavior.

Edit spreadsheet tables and review record IDs in WPS Spreadsheet.Open and save Microsoft Excel formats, including XLSX.Use a familiar interface for everyday spreadsheet work.Move existing Office documents into a lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why do my manual values move to the wrong records after a refresh?

Values entered beside a query result are tied to worksheet positions, not source record IDs. If the result changes order or size, the values can sit beside different records.

What makes a good key for the merge?

Use an ID that is unique to each record and stays the same across refreshes. In Power Query, make sure the ID columns in both tables use the same data type.

What happens when a new source record has no manual data?

With a Left Outer merge, the source record remains in the result and its manual fields show null. Add its ID and values to the manual input table, then select Data > Refresh All.

Can I keep editing manual values in the merged query output?

Enter or update those values in the separate manual input table instead. Then select Data > Refresh All so Power Query can merge the updated values into the output.