Keep Manually Edited Excel Values Synchronized with Power Query Data in WPS Office
Question details
The user needs to understand how to keep manually edited values synchronized after a Power Query refresh and prevent row misalignment when sorting adjacent columns in WPS Office Spreadsheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a Power Query output table and wants to add manual edits or notes next to refreshed data without losing them during refresh or when sorting adjacent columns.
- Observed behavior
- Manual edits in the Power Query output are overwritten during refresh, and sorting an adjacent column can misalign rows because the edited cells are not tied to the source rows.
Before changing the workbook, confirm that the source query has a stable unique identifier such as an order ID, product code, or employee ID. If no unique ID exists, add one in the source data because it is required to match manual overrides to refreshed rows.
Store manual edits in a separate override table and merge by unique ID
Use this method when manual entries must survive Power Query refreshes and stay aligned with the correct records.
Power Query output tables are replaced during refresh, so values typed directly into the output range are discarded. Keeping manual edits in a separate table and merging them back with the query result by a unique ID creates a refresh-safe workflow.
Open the source table in WPS Office Spreadsheets, insert a new column, and fill every row with a unique value such as OrderID, SKU, or Employee ID. Save the file and make sure the Power Query output includes this ID column.
On a new worksheet, create a table with the same unique ID column plus the columns you need to edit manually, for example ReviewStatus, Notes, or AdjustedAmount.
In the result worksheet, use WPS Office functions such as VLOOKUP, INDEX and MATCH, or Data > Merge to bring values from the override table into the refreshed query output by matching the unique ID.
Click Data > Refresh to refresh the query, then check that each manually edited value still appears beside the correct unique ID and that no rows have shifted.
Enter manual edits only after the final data refresh
Use this approach for one-off reports or when the query will not need to be refreshed again after manual editing.
Synchronize manual edits with Power Query data in WPS Office Spreadsheets
Use WPS Office Spreadsheets to create a refresh-safe manual edit workflow: add a unique ID, store overrides in a separate table, and merge them back after each Power Query refresh without losing entered values.
- 1. Open the Excel workbook in WPS Office: Launch WPS Office, click Menu > Open, and select the Excel file containing your Power Query data and manual edit columns.
- 2. Create or confirm the unique ID column: In the source table, add a column such as Record ID and ensure every row has a unique value that also appears in the query output.
- 3. Create a separate manual override sheet: Insert a new worksheet, add the same ID column and the fields you need to edit manually, then enter or paste the current override values.
- 4. Merge overrides into the query result: In the report worksheet, use VLOOKUP or INDEX and MATCH referencing the override sheet to pull manual values by the unique ID, so refreshed rows stay aligned.
Frequently Asked Questions
Why does Power Query overwrite manually edited cells in the query output?
Power Query does not update cells one by one. A refresh replaces the entire output table with the query result, so any values typed directly into that output range are discarded.
How do I stop rows from misaligning when I sort a column next to refreshed data?
Add a unique ID column that exists in both the source output and the manual edit table, then merge the manual values back by that ID instead of sorting only the adjacent cells.
Can I edit a Power Query output cell and keep the change after refresh?
There is no reliable way to keep a direct edit inside the Power Query output because refresh replaces the output table. Store the manual value in a separate table and merge it back by unique ID.
Does this method work in WPS Office if the workbook was created in Microsoft Excel?
Yes. WPS Office Spreadsheets opens Microsoft Excel .xlsx and .xls files, and you can use VLOOKUP, INDEX and MATCH, or similar lookup functions to merge your override table with the refreshed query output.
What formula should I use to merge manual edits back into the query output?
Use VLOOKUP or INDEX and MATCH against the override table. For example, in the query result sheet, enter a VLOOKUP formula that looks up the unique ID in the override table and returns the manual entry column.
