How to Keep Manual Comments Linked to Shop IDs in Excel Power Query
Question details
User wants to retain manual comments associated with specific Shop IDs when an Excel Power Query output table is refreshed.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Adding manual comments next to a Power Query output table and then refreshing the data connection.
- Observed behavior
- Comments are either lost entirely or assigned to the wrong Shop IDs because the query refresh reorders or replaces the output rows.
Ensure you have a unique identifier, such as a Shop ID, for each record in both your query data and your manual comments to accurately link them together.
Use a Separate Comments Table and VLOOKUP
The most reliable method is to store comments externally and link them back to the query output using an exact-match lookup function.
Typing directly into a Power Query output table is risky because refreshing the query recreates the table. Any manual data not part of the query source will be overwritten or misaligned if row orders change. By separating the data, you protect your manual inputs.
Create a new Excel worksheet in your workbook dedicated to storing your manual comments.
Set up a two-column list with 'ShopID' in the first column and 'Comment' in the second column.
Select this range and convert it into an official Excel Table by pressing Ctrl+T on your keyboard.
In your Power Query output table, add a new column header for comments directly adjacent to the query data.
Use the VLOOKUP function to retrieve the comment by typing an exact-match formula like: =VLOOKUP([@ShopID], Comments!A:B, 2, FALSE).
Merge the Comments Table inside Power Query
You can pull the manual comments directly into your Power Query model by merging the queries inside the editor.
Manage Your Data Smoothly with WPS Office
While Power Query is a specialized Microsoft feature, WPS Office provides powerful data management tools, including VLOOKUP, XLOOKUP, and Pivot Tables, in a lightweight and free package. It is fully compatible with Microsoft Excel formats, making your transition completely seamless.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open Your Spreadsheets: Open your existing .xlsx files directly in WPS Spreadsheet without worrying about formatting loss.
- 3. Link Data Seamlessly: Use WPS Spreadsheet's built-in formulas to easily link tables and manage your manual comments.
Frequently Asked Questions
Why do my comments disappear when I refresh Power Query?
When a Power Query is refreshed, Excel drops the old table data and loads a fresh copy from the source. Any manual columns added directly to the output table that are not part of the source query will be overwritten, deleted, or misaligned depending on how the rows are updated.
Can I edit Power Query data directly in the Excel sheet?
No, Power Query output tables are read-only reflections of the source data. You should make changes in the original data source or use helper columns with lookup formulas to append new information alongside the query output.
Is VLOOKUP the only way to link manual comments?
No, while VLOOKUP is very common, you can also use INDEX/MATCH, the newer XLOOKUP function, or directly merge the comment table within the Power Query Editor itself by loading the comment table as a separate data connection.
