Power Query Problems

How to Prevent Power Query from Overwriting Manual Categorization in Excel

Natalie Taylor Oct 10, 2026 868 views

Question details

The user needs a method to retain manually entered data, such as transaction categorizations, which currently get erased or misaligned when Power Query refreshes.

How to Prevent Power Query from Overwriting Manual Categorization

Product: Excel
Device & OS: not provided
Scenario: Importing dynamic datasets like bank transactions into Excel using Power Query, where users need to manually assign and save categories to the imported records.
Observed behavior: When Power Query refreshes, it completely replaces the calculated output table, causing any manually typed categorizations in adjacent columns to disappear or misalign.

Before you start

Identify the unique, unchanging columns in your raw dataset—such as transaction date, account number, and exact amount—that will serve as the foundation for your stable transaction identifier.

Solution 1Recommended

Use a Separate Lookup Table and Merge Queries

Create a stable unique identifier for your imported data and store your manual categorizations in a separate table, then merge them back together in Power Query.

Because Power Query rebuilds the output table from scratch upon every refresh, any data manually typed next to it is at risk of being lost or misaligned. The most robust solution is to detach the manual data entry from the query output entirely.

Create a Unique Identifier in Power Query

Open the Power Query Editor. Hold the Ctrl key and select immutable identifying columns (e.g., Account, Date, Amount, and Description). Go to the 'Add Column' tab and select 'Merge Columns' to create a single Unique Key column.

Set Up a Manual Categorization Table

In a standard Excel worksheet, create a new table (Ctrl + T). Create one column named 'Unique Key' (matching the keys generated in Power Query) and a second column named 'Category' for your manual inputs.

Load the Categorization Table into Power Query

Click anywhere inside your new manual categorization table, go to the Data tab, and select 'From Table/Range' to load it into the Power Query Editor.

Merge the Queries

Select your main imported data query. On the Home tab, click 'Merge Queries'. Select your manual categorization query from the dropdown, highlight the 'Unique Key' column in both panes, and click OK.

Expand the Merged Data

Click the expand icon on the newly merged column header, select only the 'Category' column, and uncheck 'Use original column name as prefix'. Close and Load your finalized table.

Use a Separate Lookup Table and Merge Queries

Avoid Relying Solely on Index Columns: Sorting the data and adding a simple Index column (1, 2, 3...) is not reliable. If older transactions are later inserted into the source data, the index values will shift and misalign your entire categorization table.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

While advanced Power Query features belong to Microsoft Excel, WPS Office provides a highly compatible, lightweight, and free alternative for managing complex spreadsheets, organizing data tables, and performing advanced data analysis without expensive subscriptions.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open Your Spreadsheets: Open your existing .xlsx files directly in WPS Spreadsheet with seamless formatting retention.
3. Manage Data Efficiently: Utilize built-in data sorting, filtering, and lookup functions to categorize and manage your datasets easily.

Completely free and lightweight office suiteHigh compatibility with Microsoft Excel (.xlsx) formatsFamiliar user interface for immediate productivityBuilt-in data analysis, filtering, and pivot table tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query delete my extra columns on refresh?

Power Query is designed to completely clear and rebuild the output destination table based on the source data and query steps. Manual data typed directly into the Excel sheet alongside the query is not part of the data model, so it gets overwritten or disconnected during this rebuilding process.

Can I just use an Index column to track manual categorizations?

It is highly discouraged. An index generated after sorting may change if older transactions or delayed records are added later. When the index numbers shift, your manually entered categories will attach to the wrong rows.

How do I update categories for new transactions in the future?

Once the merge setup is complete, you simply copy the unique keys of any newly imported transactions into your separate manual categorization Excel table, assign the categories there, and refresh your main Power Query to pull the updates in.

Excel & Data Problems