Power Query Problems

How to Fix Power Query Linked Table Moving Data to the Wrong Row in Excel

Maira Mehtab Sep 24, 2026 869 views

Question details

Users experience an issue where adding a new row to an Excel source table causes an expanded Power Query table to shift the previous row's data into the new row.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Updating an Excel source table linked to Power Query by adding new data rows.
Observed behavior: The expanded Power Query table maps the previous row's data to the newly inserted row, resulting in mismatched or shifted data alignment in the output.

Before you start

Before modifying your queries, ensure your source table contains a unique identifier (such as an Index or ID column) to establish accurate row mapping during data updates.

Solution 1Recommended

Implement a Self-Referencing Table

Creating a self-referencing query helps lock data to specific rows by relying on a unique key rather than physical row position.

When you manually add data next to a Power Query output table, the manual data does not natively link to the query rows. If the query refreshes and inserts a new row, the manual data stays in place while the query data shifts, causing a mismatch.

To resolve this, you must load the output table back into Power Query and merge it with the original source data.

Ensure a Unique ID

Open your original source table in Excel and ensure it has a unique 'ID' or 'Index' column. If it doesn't, create one and populate it.

Load the Output Table

Select your Power Query output table (the one containing your manually entered data), go to the Data tab, and click 'From Table/Range' to load it into the Power Query Editor.

Merge Queries

In the Power Query Editor, open your main source query. Click 'Merge Queries' on the Home tab, select the newly loaded output table, and highlight the unique 'ID' column in both tables to join them.

Expand and Load

Expand the joined table to include your manual data columns. Click 'Close & Load' to apply the self-referencing structure.

Row Alignment Protected: By merging on a unique ID, your manual data will now securely track the correct record even if new rows are inserted into the source data.

Solution 2

Review Query Joins and Expansion Logic

Verify that your existing merge operations and column expansions are referencing the correct matching criteria rather than relying on sequential ordering.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

If complex Excel Power Query configurations and row alignment issues are slowing down your productivity, consider switching to WPS Office. It provides a highly compatible, lightweight, and intuitive spreadsheet environment for managing datasets effectively.

1. Download and Install: Visit the WPS Office website to download the free installation package for your operating system.
2. Open Your Workbooks: Launch WPS Spreadsheet and open your existing Excel files directly without conversion.
3. Manage Data Easily: Use the built-in Data and PivotTable tools for reliable data organization and analysis.

Fully compatible with Microsoft Excel formats including .xlsx, .xls, and .csv.Lightweight installation ensures fast performance even when handling large spreadsheets.Intuitive data filtering, sorting, and pivot table features without steep learning curves.Free to use with a familiar user-friendly interface that makes migrating seamless.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query shift manual data when I refresh?

This happens because Power Query overwrites the output table based on the source data during a refresh. If new rows are added and the query isn't using a unique ID to lock manual entries via a self-referencing table, the manual data stays in its physical Excel row while the dynamic query data shifts down.

What is a self-referencing table in Power Query?

A self-referencing table is an advanced technique where a query's output table is loaded back into the Power Query Editor and merged with the original source data using a unique identifier. This ensures that any manual additions typed next to the output table remain firmly attached to the correct record.

How do I add a unique ID to prevent row shifting?

In your original Excel source data, insert a new column named 'ID' or 'Index' and fill it with sequential numbers or unique alphanumeric codes. Ensure this ID column is included in your Power Query load so it can act as the primary key for all merges.

Does sorting the source data affect Power Query row alignment?

Yes, if your query logic or external manual data relies on row order instead of matching unique keys, sorting the source data will change the output order. This inevitably causes the output rows to misalign with any adjacent manual data.

Excel & Data Problems