Prevent Excel Power Query Refreshes from Misaligning Manual Data
Question details
Keep manually entered values associated with the correct records when a Power Query result changes on refresh.

- 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.
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.
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.
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.
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.
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.
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.
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.

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.

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.




