Keep Manual Data Aligned with Excel Power Query Rows
Question details
Keep manually entered values associated with the correct records when a Power Query table refreshes.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A user adds manual information to records returned by a Power Query and needs those values to remain aligned after refreshes.
- Observed behavior
- Manually entered values can appear beside the wrong records after the query output changes order or size.
Identify a stable, nonblank ID present in the source data, such as a record number. Make sure each ID appears only once in the table you will use for manual entries.
Merge a Separate Manual Table into the Power Query
Use a stable ID to match manual values to source records instead of relying on worksheet row positions.
Keep manual entries in a separate Excel table, not in columns beside the query output. Power Query can then match each entry to its record by ID when the data refreshes.
On a separate worksheet, enter an ID column and the manual fields you need. Select the range and choose Insert > Table, then enter one row per ID.
Select a cell in the manual table and choose Data > From Table/Range. In Power Query, choose Home > Close & Load To and select Only Create Connection.
Choose Data > Queries & Connections, then open the query that supplies the original records. Ensure its stable ID column is included.
In Power Query Editor, choose Home > Merge Queries. Select the manual-table query, click the matching ID column in each table, and choose Left Outer to retain all source records.
Click the expand icon in the merged column, select the manual fields, and choose OK. Select Home > Close & Load, then use Data > Refresh All after changing the source data or manual table.

Manage Spreadsheet Tables with WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and compatibility with Excel files for an easy move between office suites. Use Microsoft Excel to maintain the Power Query merge described above; WPS does not fix that Excel refresh behavior.

Frequently Asked Questions
Why do my manual values move to the wrong records?
A refresh can change the order or number of Power Query output rows, while values entered beside the output remain tied to worksheet positions. Store them in a separate table and merge by ID instead.
What should I use as the matching ID?
Use a stable, nonblank identifier shared by the source and manual tables, such as a record number. An address is suitable only if it reliably identifies one record and does not change.
How do I add a new manual value later?
Add or edit the record in the separate manual Excel table, then select Data > Refresh All to update the merged query output.
What happens if an ID has no matching manual entry?
With a Left Outer merge, the source record remains in the output, but its expanded manual fields are blank. Add the matching ID to the manual table and select Data > Refresh All.




