Power Query Problems

Keep Manual Excel Data Aligned After a Power Query Refresh

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs a method to prevent manual data columns in an Excel table from misaligning when a Power Query refresh modifies or adds new rows.

Product: Excel
Device & OS: not provided
Scenario: Refreshing imported Dataverse rows via Power Query while retaining adjacent manual entry columns in an Excel workbook.
Observed behavior: Manual columns lose their alignment with the correct contact records when new rows are inserted, deleted, or re-sorted during the Power Query refresh.

Before you start

Ensure your source data includes a unique, stable identifier (such as a Contact ID or Email Address) for each row before attempting to merge tables.

Solution 1Recommended

Use a Separate Manual Data Table with a Unique ID

Store your manually entered values in a separate Excel table linked by a stable contact ID, then merge it back with the Power Query result to guarantee alignment.

Power Query dynamically reconstructs tables during a refresh. If manual data is typed directly next to a query result, Excel does not inherently link those manual cells to the adjacent row. When the query imports new data or changes the sort order, the manual data stays statically in place, causing misalignment.

To resolve this, you must treat your manual data as a separate data source and use a primary key to relate it to your imported Dataverse records.

Create a manual data table

Create a new worksheet and set up an Excel table containing the unique 'Contact ID' from your source data, alongside your manually entered columns (e.g., status, notes, dates).

Load the manual table into Power Query

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

Merge the queries

In the Power Query Editor, open your primary query that imports data from Dataverse. Go to the Home tab and click 'Merge Queries'.

Configure the join settings

Select your primary query as the first table and the manual entry table as the second. Click the 'Contact ID' column in both tables to link them, and select 'Left Outer (all from first, matching from second)' as the Join Kind.

Expand and load

Click the expand icon at the top of the newly merged column to select the manual fields you want to display. Finally, click 'Close & Load' to output the perfectly aligned dataset back into Excel.

Self-Referencing Technique: By maintaining manual data in a separate table, your manual inputs will always match the correct record ID, regardless of how many rows are added or shifted during a refresh.

Free Microsoft Office alternative

Need a Lightweight Alternative for Data Management? Try WPS Office

While advanced Power Query integrations like Dataverse are specific to Microsoft's ecosystem, WPS Office provides a highly compatible, free, and lightweight spreadsheet tool for handling large datasets, merging tables, and managing manual records seamlessly.

1. Download and install: Visit the official WPS Office website to download and install the free software suite.
2. Open your workbook: Launch WPS Spreadsheets and open your existing .xlsx data files without any format conversion.
3. Match data using formulas: Use powerful lookup functions like VLOOKUP or XLOOKUP to tie manual entry tables to your primary dataset using unique IDs, keeping everything perfectly aligned.

Fully compatible with Microsoft Excel (.xlsx, .csv) file formats.Lightweight software with fast installation and smooth performance on older hardware.Comprehensive suite offering Writer, Presentation, and Spreadsheets in one unified app.Robust VLOOKUP, XLOOKUP, and pivot table functionalities for matching and analyzing manual data.

microsoft office alternative - wps office

Frequently Asked Questions

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

Power Query completely rebuilds the data table based on the external source data. If new rows are inserted or the sorting changes, Excel does not automatically tie your adjacent, manually typed cells to the imported records, causing the rows to misalign.

Can I edit Power Query results directly in Excel?

Any direct edits made to the cells generated by a Power Query will be overwritten and lost the next time you refresh the query. You should always store manual inputs in a separate table and link them.

What is a stable identifier (Unique ID)?

A stable identifier is a column containing unique values that never change for a specific record. Examples include an Employee ID, Email Address, Product SKU, or Database Primary Key.

How do I merge queries in Excel?

Open the Power Query Editor, navigate to the Home tab, and select 'Merge Queries'. You will be prompted to select two tables and click the matching columns (like an ID column) that link them together.

Excel & Data Problems