Keep Excel Power Query Job Statuses Linked After Refresh
Question details
Keep manually maintained job statuses associated with the correct jobs when a Power Query table refreshes.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Job files are combined into a query output, and checkboxes or status values are added to track each job.
- Observed behavior
- After a refresh changes the order or number of rows, manual statuses or formulas can refer to the wrong jobs.
Identify a stable, unique job ID in the source data; do not use a row number or an index that changes on refresh. If statuses are already beside the query output, copy each job ID and its current status into a separate table before changing the query.
Merge a separate job status table by unique ID
Use this approach when users need to update statuses manually without relying on the position of a query row.
Keep the editable status table outside the Power Query output. The merged status column is a refreshed result, so make future updates in the separate status table rather than in the query output.
Each job ID should appear only once in the status table. Duplicate IDs can produce duplicate rows when the tables are merged.
On a separate worksheet, enter Job ID and Status headers, then add the IDs and their existing statuses. Select the range and choose Insert > Table.
Select a cell in the status table and choose Data > From Table/Range. In Power Query Editor, confirm that the job ID column uses the same data type as the ID in the job query, then choose Close & Load To and select Only Create Connection.
Open the existing job query in Power Query Editor and choose Home > Merge Queries. Select the status query as the second table, select the job ID column in both tables, and use a Left Outer join to keep all jobs from the original query.
Select the expand button in the merged column, choose Status, and confirm. Choose Home > Close & Load to update the job query output.
Enter or change statuses in the separate status table, then choose Data > Refresh All. Add a new job ID to the status table when you need to record its status.

Manage Job Tracking Workbooks with WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. It opens Excel workbooks to help you move existing job-tracking files into WPS; it does not fix Microsoft Excel Power Query refresh behavior.

Frequently Asked Questions
Why do statuses move to the wrong jobs after a refresh?
A refresh can add, remove, or reorder query rows, while manually entered values or references may remain tied to worksheet positions. Store statuses separately and merge them using a stable job ID.
Where should I edit a job's status after setting up the merge?
Edit the Status cell for that job ID in the separate status table, then choose Data > Refresh All. The status shown in the query output is the merged result, not the place to maintain the value.
What happens if a job has no matching status?
A Left Outer merge keeps the job in the output, but its expanded status is blank or null. Add the job ID and a status to the separate table, then refresh.
What if a job disappears from the source and returns later?
Its status remains in the separate status table unless you remove it. If the job returns with the same ID, a refresh can match it to that stored status again.
Can two status rows use the same job ID?
Avoid duplicate job IDs in the status table: a merge can produce multiple output rows for a job. Keep one status row per unique ID and ensure the ID columns in both queries use matching data types.




