Power Query Problems

How to Keep Manual Excel Columns Synchronized with Power Query

Steven Parker Oct 16, 2026 870 views

Question details

The user needs to maintain manually added data columns alongside Power Query results without losing or misaligning the data during updates.

How to Keep Manual Excel Columns Synchronized with Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Adding manual reporting columns to an Excel table that is generated and constantly updated from an SQL database via Power Query.
Observed behavior: Manually added Excel columns are deleted, disconnected, or no longer aligned with the source rows when the Power Query data is refreshed.

Before you start

Ensure your source data contains a unique and stable identifier (such as a Product ID or Invoice Number) to accurately match the manual data with the refreshed query rows.

Solution 1Recommended

Use a Self-Referencing Table in Power Query

Reference the final Excel table back into Power Query and merge it with the original source data to permanently preserve manual entries.

This method creates a loop where your final output table (which includes your manual columns) is fed back into Power Query. By merging this self-referenced data with the fresh SQL data using a unique ID, your manual inputs stay locked to the correct rows even when the source data updates.

Load Source Data

Import your source data from the SQL database into Excel using Power Query and load it into your worksheet as a standard Table.

Add Manual Columns

In Excel, add your manual columns directly next to the loaded Power Query table and type in your custom data.

Create Self-Reference

Select the newly expanded table in your worksheet, go to the 'Data' tab, and click 'From Table/Range' to load this combined table back into Power Query.

Merge Queries

Merge the newly created self-referencing query with your original SQL source query. Ensure you join them using a unique ID column to keep rows perfectly aligned.

Clean and Load Final Table

Remove any duplicate columns in the Power Query editor, finalize the query steps, and load it back into your Excel worksheet.

Use a Self-Referencing Table in Power Query

Row Alignment: Using a unique ID instead of relying on row positions ensures that even if the SQL database sorts data differently or adds new rows, your manual data will always stick to the correct record.

Solution 2

Maintain Manual Data in a Separate Excel Table

Store your manual inputs in an entirely separate table and merge it with the SQL data within Power Query.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

If you regularly work with complex datasets and need a lightweight, cost-effective solution, WPS Office offers comprehensive spreadsheet tools with excellent Microsoft Excel compatibility. It handles large data files smoothly without heavy resource usage.

1. Download WPS Office: Visit the official WPS website and download the free installer for your operating system.
2. Install the Software: Run the downloaded installer and follow the simple on-screen instructions to set up WPS Office on your device.
3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel files to experience seamless compatibility and fast data handling.

Fully compatible with Microsoft Excel (.xlsx) formatsLightweight design for faster data processing and stable performanceBuilt-in advanced formulas and data manipulation toolsFree to use with a familiar, easy-to-navigate interface

microsoft office alternative - wps office

Frequently Asked Questions

Why do my manual Excel columns disappear when Power Query refreshes?

When Power Query refreshes, it completely rebuilds the output table based strictly on the source query. Any columns added manually directly to the output table in Excel are overwritten or deleted because they are not part of the query's defined steps.

What is a stable key in Excel data merging?

A stable key is a unique identifier, such as a Product ID, Employee Number, or Order Code, that remains constant for a specific record. It ensures that when tables are merged, the data aligns perfectly regardless of the row's physical position in the spreadsheet.

Can I edit the data directly in the Power Query editor?

Power Query is primarily an ETL (Extract, Transform, Load) tool designed to shape data from external sources. While you can add custom columns using formulas or conditional logic, it does not support manual, cell-by-cell data entry like a standard Excel worksheet.

Excel & Data Problems