Keep Manual Status Values from Moving After Power Query Refresh in WPS Office Spreadsheet
Question details
The user needs to keep manually entered status values associated with the correct customer when a Power Query output table changes row order or size after refresh.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user typed manual status entries beside a Power Query output table, but the query results can be sorted or changed after refresh.
- Observed behavior
- The manual status column stays in position while the query output moves, causing statuses to align with the wrong customer records.
Before you rebuild the workbook, confirm that the source data contains a stable unique key, such as Customer ID or Phone Number, and remove any manual status values from columns directly beside the query output.
Use a Separate Status Table and Retrieve Status by Key with XLOOKUP
Use this method when statuses are maintained manually and must survive Power Query refresh, reordering, or row-count changes.
Do not keep manual status values in an unlinked column beside the Power Query output. When the query refreshes and sorts rows differently, the output moves but manual entries do not, which causes mismatched records.
A separate Excel table with a stable key lets WPS Office Spreadsheet re-match each status by Customer ID, Phone Number, or another unique identifier.
Open WPS Office Spreadsheet and create a new sheet named Status. Select a cell, press Ctrl+T to create a table, and add at least one stable key column such as PHONE NUMBER or Customer ID and a STATUS column.
Type each manual status on the row that contains the matching stable key. Keep this table separate from the Power Query output table.
Load the query result into a worksheet as an Excel table. Ensure the query output includes the same stable key, such as PHONE NUMBER, so it can be matched.
In the first empty status cell beside the query output, enter a formula such as =XLOOKUP([@[PHONE NUMBER]],StatusTable[PHONE NUMBER],StatusTable[STATUS],"") and press Enter.
Keep Manual Statuses Linked by Key in WPS Office Spreadsheet
Use WPS Office Spreadsheet to store manual statuses in a separate table and retrieve them by key with XLOOKUP. This keeps status values aligned when a Power Query output is refreshed, reordered, or filtered.
- 1. Download and install WPS Office: Go to the WPS Office official website and click Download WPS Office Free. Install WPS Office Spreadsheet on your Windows PC.
- 2. Open the workbook in WPS Office Spreadsheet: Launch WPS Office Spreadsheet, click Menu, choose Open, and select the workbook containing the Power Query output and status data.
- 3. Build the status table: Click the + button next to the sheet tab to create a new sheet. Select a cell, press Ctrl+T, then add the stable key and STATUS columns.
- 4. Enter the XLOOKUP formula: Click the first status cell beside the query output table, type the XLOOKUP formula referencing the stable key, and press Enter.
- 5. Refresh and verify: Click the Data tab and choose Refresh All if needed, then confirm that each status still appears beside the matching customer.
Frequently Asked Questions
Why do manually entered statuses move after a Power Query refresh?
They usually do not actually move; the query output rows are re-sorted or changed, while the manual status column remains in its original position. Storing statuses in a separate table and matching by a unique key prevents this alignment issue.
Can I use Customer ID instead of Phone Number as the key?
Yes, any unique value that exists in both the Power Query output and the status table works, such as Customer ID, Phone Number, or an employee code. Use the same field name in both tables and reference it in XLOOKUP.
Does XLOOKUP work in WPS Office Spreadsheet?
Yes, WPS Office Spreadsheet supports XLOOKUP. If your version does not show XLOOKUP, use VLOOKUP or INDEX/MATCH as an alternative lookup method.
Will the XLOOKUP formula return an error if no status is entered?
The example formula returns an empty string ("") when no match is found. This avoids #N/A errors and keeps the status column readable.
