Power Query Problems

Keep Excel Power Query Job Statuses Linked After Refresh

Anum MushtaqAnum Mushtaq Nov 18, 2026 869 views

Question details

Keep manually maintained job statuses associated with the correct jobs when a Power Query table refreshes.

Keep Excel Power Query Job Statuses Linked After Refresh
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the status table

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.

2
Add the status table to Power Query

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.

3
Merge the tables

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.

4
Expand and load the status

Select the expand button in the merged column, choose Status, and confirm. Choose Home > Close & Load to update the job query output.

5
Update and refresh

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.

Merge a separate job status table by unique ID
Using checkboxes: If you use checkboxes, store their linked values in the separate status table and merge those values by job ID. Do not rely on a checkbox or linked cell staying beside the same row in the query output.
Free Microsoft Office alternative

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.

Maintain job IDs and manual status lists in a spreadsheet.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar interface when moving from Microsoft Office.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.