Power Query Problems

Keep Manual Data Aligned with Excel Power Query Rows

Steven ParkerSteven Parker Nov 18, 2026 870 views

Question details

Keep manually entered values associated with the correct records when a Power Query table refreshes.

Keep Manual Data Aligned with Excel Power Query Rows
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the manual table

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.

2
Load the manual table

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.

3
Open the source query

Choose Data > Queries & Connections, then open the query that supplies the original records. Ensure its stable ID column is included.

4
Merge by ID

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.

5
Expand and refresh

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.

Merge a Separate Manual Table into the Power Query
Keep IDs consistent: The ID values and data types must match in both tables. Duplicate IDs in the manual table can create duplicate output rows; missing matches produce blank manual fields.
Free Microsoft Office alternative

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.

Organize record IDs and manual values in spreadsheet tablesOpen and edit Microsoft Excel formats, including XLSXWork in a familiar, easy-to-use spreadsheet interfaceUse a free, lightweight office suite
microsoft office alternative - wps office

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.