Add a Refresh-Safe Dropdown Column Beside a Power Query Table in WPS Spreadsheets
Question details
The user needs to add an Excel data validation dropdown beside a Power Query table so they can assign responsibility to rows while ensuring manual assignments are not deleted or overwritten when the query refreshes.
- Product
- WPS Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- Assigning responsible owners to items returned by a Power Query table in a refresh-safe way.
- Observed behavior
- Manual columns added directly beside a Power Query output can be overwritten or removed during a query refresh.
Confirm that the Power Query result and the separate responsibility table share a unique item identifier, such as an item number or ID, so they can be merged reliably.
Create a Separate Responsibility Table with Data Validation and Merge It in Power Query
Use this method whenever you need to manually assign responsibility while keeping entries safe when the Power Query result refreshes.
Storing manual entries directly beside a query output is not refresh-safe. A separate input table keeps data outside the query overwrite area and lets Power Query combine it back by item number.
In WPS Spreadsheets, insert a new worksheet and create a table containing the same item number column and a Responsible Person column.
Select the Responsible Person column, go to the Data tab, click Data Validation, choose List, and enter the allowed responsibility names as the source.
In Power Query, load both the original query result and the assignment table, then use Merge Queries and join them on the item number column to return the responsible person field.
Load the merged table to a worksheet so the responsibility dropdown values appear beside the query data without being overwritten on refresh.
Keep Power Query Assignments Refresh-Safe in WPS Spreadsheets
Create the dropdown in a separate assignment table, merge it with the Power Query output by item number, and keep manually assigned responsibilities intact across refreshes in WPS Office.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains your Power Query output table.
- 2. Create the assignment table: Add a new worksheet and enter item numbers plus a Responsible Person column with Data Validation list values.
- 3. Merge with Power Query: Use Power Query to load both tables and merge them on the item number column, then expand the responsible person field.
- 4. Refresh safely: Load the merged result to a worksheet and refresh the query; assignments remain because they are stored in the separate input table.
Frequently Asked Questions
Why does my dropdown column disappear after a Power Query refresh?
Manual columns added directly beside a Power Query output are not part of the query table. When the query refreshes, WPS Spreadsheets can overwrite or remove that adjacent column. Store manual entries in a separate assignment table and merge it back into the query instead.
Can I add Data Validation to a Power Query table?
Yes, but it is safer to apply Data Validation in a separate responsibility table. In WPS Spreadsheets, select the responsibility column, go to Data > Data Validation, choose List, and enter the allowed values.
How do I merge a responsibility table with a Power Query result?
Open Power Query Editor, load both the original query result and the responsibility table, select Merge Queries, join them on the shared item number column, expand the responsible person field, and load the merged table to a worksheet.
Will this method work with Microsoft Excel files?
Yes. WPS Spreadsheets supports Data Validation and Power Query and can open and save XLSX files, so the refresh-safe dropdown workflow remains compatible with Microsoft Excel.
