Power Query Problems

How to Keep Manually Entered Comments When Refreshing Power Query in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a method to retain manual comments added to a Power Query output table so they are not deleted during data refreshes.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Adding custom, manual data directly into a table generated by Power Query and subsequently refreshing the connection.
Observed behavior: Manual comments entered directly into the Power Query output table are overwritten and lost upon refresh because Power Query completely recreates the table from the source data.

Before you start

Ensure your main dataset contains a Unique Identifier column (such as a Transaction ID, Product Code, or Employee ID), as this is required to accurately link your manual comments back to the correct rows.

Solution 1Recommended

Store Comments in a Separate Table and Merge Queries

The only reliable way to keep manual data in a refreshed query is to store the comments in a dedicated Excel table and merge it with your main Power Query source using a unique ID.

Power Query is designed to overwrite the destination table on every refresh, meaning any data typed directly into the output grid is not persistent. By keeping your comments in a separate helper table, you protect them from being overwritten while seamlessly joining them back to your main data using a common key.

Create a separate comments table

In a new worksheet, create an Excel table (Insert > Table) containing at least two columns: your Unique ID (matching your source data) and a 'Comments' column.

Enter your manual data

Type your manual comments corresponding to the respective Unique IDs in this new helper table.

Load the comments table to Power Query

Select any cell inside your new comments table, navigate to the 'Data' tab, and click 'From Table/Range' to load it into the Power Query Editor.

Merge with the main query

In the Power Query Editor, select your main data query. Go to the Home tab, click 'Merge Queries', and select your comments table from the dropdown menu.

Define the relationship

Click on the Unique ID columns in both the main table preview and the comments table preview. Choose a 'Left Outer' join kind and click OK.

Expand and load

Click the expand icon at the top of the newly merged column, select only the 'Comments' field, and uncheck 'Use original column name as prefix'. Finally, click 'Close & Load' to output your combined table.

Do Not Modify the Output Directly: Never enter persistent manual data directly into a query output table. Always use a standalone helper table linked by a unique identifier to maintain data integrity.

Free Microsoft Office alternative

Looking for a Lightweight Data Management Solution? Try WPS Office

If managing complex Power Query connections and data models in Microsoft Excel becomes overwhelming or resource-heavy, WPS Office provides a free, highly compatible, and user-friendly spreadsheet environment. You can easily manage and merge datasets using intuitive lookup formulas without navigating complex query interfaces.

1. Download and Install: Get WPS Office for free from the official website and install it on your PC or Mac.
2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx workbooks with zero formatting loss.
3. Merge Data Easily: Use built-in VLOOKUP or XLOOKUP functions to seamlessly combine your comments table with your main dataset.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Familiar and intuitive interface for straightforward data merging and pivot tables.Lightweight application that runs smoothly even on older devices.Free alternative to Microsoft Office with seamless multi-platform support.

microsoft office alternative - wps office

Frequently Asked Questions

Can I lock cells in a Power Query output table to prevent comments from being overwritten?

No, locking cells or protecting the sheet will cause the Power Query refresh to fail. The query engine must have complete write access to overwrite the output range, which is why a separate, merged table is the only Microsoft-supported solution.

What happens to my manual comments if a row is deleted from the source data?

If you use a separate comments table merged via a unique ID, your comments remain safely stored in the helper table. They simply won't appear in the merged output table if the corresponding unique ID no longer exists in the primary source data.

Is there a way to make Power Query append new data instead of refreshing the entire table?

By default, a standard Power Query refresh completely recreates the table. To append new data historically without losing old records, you would need to set up a self-referencing query that merges the previous output with the new source data, but this requires more advanced M code.

Excel & Data Problems