Keep Added Data Matched to Excel Power Query Rows
Question details
The user needs to keep manually added values associated with the correct imported records when the source owner inserts new rows.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Additional values are entered beside an imported Excel table whose source can change between refreshes.
- Observed behavior
- Inserted source rows change row positions, so values added beside the table may become associated with the wrong records.
Identify a source column that uniquely identifies each record and remains unchanged, such as a persistent Row ID. Confirm that each added value can be assigned to that ID and that the key columns in both tables use the same data type.
Merge a separate additional-data table using a stable unique key
Use this approach to preserve record-level relationships when source rows are inserted, reordered, or refreshed.
Power Query should match the source and additional data by record identity, not by worksheet row position. Keep manually maintained values in a separate input table rather than typing them into or beside the query output.
A Power Query index generated from the current row order is not a stable identifier if that order can change. Use an ID maintained in the source, or a combination of columns that uniquely and consistently identifies each record.
On a separate worksheet, enter a key column such as Row ID and the columns for your added values. Assign each value to the correct source ID, select the range, and choose Insert > Table. On Table Design, give the table a recognizable name such as AdditionalData.
Click a cell in AdditionalData and choose Data > From Table/Range to open it in Power Query Editor. Select the key column and set its data type to match the source query. Choose Home > Close & Load > Close & Load To, then select Only Create Connection if that option is available.
Open Data > Queries & Connections and double-click the source query. In Power Query Editor, choose Home > Merge Queries. Select AdditionalData as the second table, click the matching key column in each preview, and choose Left Outer to retain all source records. Click OK.
Click the expand icon in the new merged column header. Select only the additional-data columns you need, clear Use original column name as prefix if desired, and click OK. Check that known IDs display the correct added values.
Choose Home > Close & Load to load the result into Excel. For future edits, update AdditionalData rather than the query output, then choose Data > Refresh All. Newly inserted source records will match by ID; records without an additional-data entry will show null values in the merged columns.
Use WPS Spreadsheet for Everyday Excel-Compatible Table Editing
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for easy migration of everyday editing tasks. Use it to maintain a separate ID-based additional-data table; the Power Query merge and refresh procedure described here remains an Excel workflow.
Frequently Asked Questions
Why do values typed beside a Power Query table move to the wrong records?
Those values are associated with worksheet positions rather than source record IDs. When a refresh inserts or reorders source records, the adjacent values may no longer align. Store them in a separate table and use Home > Merge Queries in Power Query Editor to match by a stable key.
Can I use a Power Query index column as the matching key?
Not if the index is regenerated from a row order that can change. Add Column > Index Column numbers the current rows, so an inserted or reordered record can receive a different index on refresh. Use a persistent source ID or stable, unique combination of source columns instead.
What happens when a new source record has no added values yet?
With a Left Outer merge from the source query, the new record stays in the output and its additional-data columns contain null values. Add its ID and values to the separate input table, then choose Data > Refresh All in Excel.
Why did the merge create duplicate rows or fail to find a match?
Multiple matching IDs in the additional-data table can duplicate output rows. Missing matches can result from different key data types, extra spaces, or different ID values. Open both queries through Data > Queries & Connections, check key uniqueness and data types, and use Transform > Format > Trim for text keys where needed.
Where should I edit the additional values after creating the merge?
Edit the separate AdditionalData input table, not the loaded query result. Then select Data > Refresh All so Power Query rebuilds the output using the updated values and their matching IDs.
