Power Query Problems

Fix Excel Power Query Refresh Moving Data Validation Values to a New Row

Maira Mehtab Sep 24, 2026 870 views

Question details

The user needs to prevent data validation drop-down values from shifting to newly added rows when an Excel Power Query table is refreshed.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Refreshing a Power Query table that contains appended manual data validation columns.
Observed behavior: When a refresh adds new rows to the table, a value manually entered in the previous last row is removed and incorrectly shifts to the new last row.

Before you start

Ensure you have a backup copy of your workbook before altering query properties or table structures, as a query refresh directly overwrites the existing data ranges.

Solution 1Recommended

Adjust External Data Range Properties

Modify how the Excel table handles structural changes during a Power Query refresh to prevent manual data from shifting.

By default, Excel tables might insert entirely new rows when query data expands. Changing the data range properties to overwrite cells instead of inserting full rows can prevent adjacent manual data from being displaced.

Select the Query Table

Click on any cell inside the Power Query result table that is experiencing the row shift issue.

Open Data Range Properties

Right-click the selected cell, hover over 'Table', and select 'External Data Properties' from the context menu.

Modify Refresh Settings

In the External Data Range Properties dialog, look under the 'If the number of rows in the data range changes upon refresh' section. Select 'Overwrite existing cells with new data, clear unused cells'.

Preserve Layout and Confirm

Ensure the checkbox for 'Preserve column sort/filter/layout' is checked. Click 'OK' to save the settings, then refresh your query to test the behavior.

Solution 2

Use a Self-Referencing Query for Manual Columns

Merge the query table with itself using a unique ID to strictly lock manual data validation entries to specific records.

Solution 3

Isolate the Issue with a Sample Workbook

Upload a sanitized sample file to identify conflicting VBA scripts or complex table expansion behaviors.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

Dealing with complex Excel query errors and data shifting can be frustrating. WPS Office offers a free, lightweight, and highly compatible alternative for your daily spreadsheet needs, featuring familiar interfaces and robust data validation tools without the heavy processing overhead.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open WPS Spreadsheets: Launch WPS Spreadsheets, which features an interface instantly familiar to Excel users.
3. Load Your Workbook: Open your existing .xlsx file to continue working with your data validation lists seamlessly.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Lightweight design ensures fast loading and smooth data manipulation.Built-in advanced data validation and drop-down list capabilities.Free to use with a familiar, easy-to-navigate interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does manual data shift when I refresh my Power Query?

When Power Query refreshes, it essentially clears and rewrites the data in the destination table. If manual columns are placed next to the query output without a unique identifier link, Excel's table auto-expansion can displace or shift the manual data asynchronously.

Can I use data validation in a Power Query table?

Yes, you can apply data validation to columns adjacent to a Power Query table. However, to prevent data from shifting upon refresh, it is highly recommended to use a unique ID and a self-referencing query to bind the manual selections to specific rows.

Will 'Overwrite existing cells' fix the shifting rows issue?

Changing the External Data Properties to 'Overwrite existing cells with new data, clear unused cells' often resolves minor shifting issues by preventing the table from inserting entirely new rows that push adjacent manual data down.

How do I stop an Excel table from expanding automatically?

Go to File > Options > Proofing > AutoCorrect Options. Under the 'AutoFormat As You Type' tab, uncheck the box for 'Include new rows and columns in table' and click OK.

Excel & Data Problems