How to Add Dropdowns and Comments to Power Query Output Tables in Excel
Question details
The user needs to add manual columns (like dropdown selections and text comments) to an Excel table populated by a Power Query import (such as SAP data), while ensuring the manual entries are not misaligned or overwritten when the query refreshes.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining automatically refreshed data from an external source (via Power Query) with user-maintained manual input in a single report view.
- Observed behavior
- When adding manual columns directly next to a Power Query output table, changes in row count or sorting during data refresh cause the manual comments and dropdowns to misalign with their original records or be deleted.
Ensure your imported SAP or source data has a Unique Identifier column (such as a Record ID or Transaction Number) for each row, as this is strictly required to reliably map manual entries to the correct records.
Use a Separate Mapping Table and Merge via Power Query
The most robust and reliable way to prevent data loss is to maintain manual user inputs in a completely separate table and merge them back with your source data using Power Query.
This approach guarantees that even if the source data changes its sort order or row count, the user's manual dropdown choices and comments will accurately stick to the correct Unique ID.
In a new worksheet, copy the Unique IDs from your source data. Add your new columns for Dropdowns (using Data Validation) and Comments. Format this range as an official Excel Table by pressing Ctrl+T and name it 'UserInputTable'.
Select any cell inside 'UserInputTable', go to the Data tab on the ribbon, and click 'From Table/Range' to load it into the Power Query Editor.
In the Power Query Editor, navigate to your main SAP data query. On the Home tab, click 'Merge Queries'. Select 'UserInputTable' as the second table, highlight the Unique ID column in both tables to create the join, and click OK.
Click the small expand icon on the newly merged table column header. Select your Dropdown and Comment columns to add them to your main dataset. Finally, click 'Close & Load' to output the combined, refresh-safe table.
Use Lookup Formulas Beside the Query Output
If you prefer not to merge tables inside Power Query, you can keep the manual entry table separate and use dynamic lookup formulas to display the data next to the query output.
Try WPS Office for Seamless Data Management and Spreadsheet Analysis
If you are working with complex data imports and need a reliable, lightweight spreadsheet tool, WPS Office is an excellent choice. It provides high compatibility with Microsoft Excel formats and offers powerful data validation and lookup functions without the heavy resource footprint.
- 1. Open Your Workbook in WPS: Launch WPS Spreadsheet and open your existing Excel workbook containing your imported data.
- 2. Apply Data Validation: Navigate to the Data tab, select 'Validation', and set up your dropdown lists in a dedicated user-input sheet.
- 3. Link Data with Formulas: Use WPS Spreadsheet's built-in XLOOKUP or VLOOKUP functions to seamlessly map your manual tracking data back to your main report.
Frequently Asked Questions
Why do my manual comments disappear when Power Query refreshes?
Power Query completely reconstructs the output table upon refresh. If you manually type into columns adjacent to the Power Query output, changes in row order or record count from the source data will cause the manual entries to misalign or be overwritten because Excel doesn't inherently link your typed data to the refreshed rows.
Can I add data validation (dropdowns) directly to a Power Query output table?
While you can technically apply data validation to the columns of a Power Query output table, refreshing the query may clear these validation rules or cause them to apply to incorrect rows if the dataset size changes. It is much safer to apply dropdowns in a separate linked table.
What is a self-referencing Power Query table?
A self-referencing query is an advanced technique that involves loading the Power Query output back into Power Query as a new source, and then merging it with the original external data. This creates a loop that preserves manual data entry alongside refreshed automated data.
Is there a way to avoid using a secondary table for comments entirely?
If you only need static comments that will never change, you can append them directly to the source data (e.g., updating the actual SAP database) before importing. However, for dynamic, user-maintained fields managed entirely within Excel, using a secondary mapping table is the only reliable method.
