Excel Query Not Removing Row Once Deleted from Source Table with Refresh

When refreshing an Excel Power Query, deleted rows from the source table often persist in the query results instead of disappearing. This behavior typically occurs because the query is not configured to dynamically detect structural changes or new rows in the underlying data source. By adjusting specific settings in the Query Editor and ensuring your connection properties are correctly aligned with your refresh requirements, you can resolve this synchronization issue and ensure that your dashboard reflects the current state of your source data accurately.

Understanding Why Rows Persist

The primary reason for this discrepancy lies in how Power Query handles data loading versus data retrieval. When you initially load a query, it captures a snapshot of the data at that moment. If you delete a row in the source file (such as an Excel workbook or CSV) but do not modify the schema or the range definition, the query may simply ignore the deletion during a standard refresh. This is particularly common when working with static ranges rather than dynamic tables. Additionally, if the query was saved with a "Keep Connection Alive" setting, it might be caching previous states depending on the environment, although this is less common in modern Excel versions compared to older iterations. The core issue is usually a mismatch between the source definition and the query's expectation of what constitutes the "current" dataset.

Configuring Dynamic Source Ranges

To ensure that deletions are recognized, you must move away from fixed cell references like A1:Z100 and toward dynamic structures. If your source is an Excel worksheet, convert your data range into an official Table using the Ctrl+T shortcut. Tables automatically expand to include new rows and contract when rows are deleted, providing a stable anchor for Power Query. In the Query Editor, verify that the source step references this table name rather than a specific sheet range. If you are pulling data from a folder, ensure that the filter applied to the folder path is not excluding files that contain the missing data, although this is less relevant for single-file deletions. For CSV files, you cannot use Tables, so you must ensure that the text import settings are set to treat the entire file as a single block of data without hardcoded row limits.

Adjusting Connection Properties

A critical step in troubleshooting persistent rows involves checking the connection properties within Excel. Right-click on the query in the Queries & Connections pane and select Properties. Look for the option labeled "Refresh every X minutes" or "Refresh data when opening the file." More importantly, check the checkbox for "Enable background refresh." While this does not directly cause row persistence, it can lead to stale views if multiple refreshes are triggered simultaneously. However, the most vital setting here is related to how the connection interacts with the source. Ensure that you are not using a cached version of the data. If you have previously loaded the data into the Data Model, try creating a fresh connection to see if the issue persists. This helps isolate whether the problem is with the query logic itself or the way Excel caches the result.

Using Advanced Editor for Precision

For complex scenarios where UI settings fail, editing the M code directly can provide greater control. Open the Advanced Editor in the Query Editor and inspect the Source step. If you see hardcoded values such as Table.RowCount or specific index filters, these might be preventing the query from seeing the updated structure. Removing unnecessary transformations that lock in data shapes can help. For example, if you have a step that filters out nulls based on a previous state, deleting that step might allow the query to re-evaluate the source dynamically. You can also explicitly add a step to remove blank rows or duplicates after the source step to ensure clean data. This approach requires familiarity with M language but offers the highest degree of reliability for dynamic datasets.

Verifying Source File Integrity

Sometimes the issue is not with Excel but with the source file itself. If you are working with a shared network drive, ensure that no other user is currently locking the file, which could cause Excel to read a stale version. Similarly, if the source is another Excel file, make sure that the deleted row was actually saved and closed before you attempted the refresh. Opening the source file, confirming the deletion, saving, and closing it is a necessary precondition for Power Query to detect the change. If the source is a database, ensure that the transaction containing the deletion has been committed. These external factors are often overlooked but are essential for maintaining data integrity across systems.

Integrating Related Resources

For users dealing with complex queries that involve multiple data sources, understanding how to reference columns by name stored as a text parameter can prevent errors when column positions shift. You can learn more about this technique in our guide on Power Query: Refer to a Column by Name Stored as a Text Parameter. Additionally, if you are manipulating large datasets that experience performance issues during refresh, consider optimizing your data handling strategies. Our article on How to Manipulate Hundreds of Thousands of Rows of Data in Excel Without Freezing provides valuable insights into maintaining performance while keeping your queries responsive.

Frequently Asked Questions

Why does my query show old data even after I save and close the source file?

This usually happens because Excel is caching the connection or the query is set to use a local copy of the data. Check the connection properties and ensure that "Use as a PivotTable report" or similar caching options are disabled if you need live data. Also, verify that the source file is not open in another program that might be locking a different version.

Can I force Power Query to delete rows that were removed from the source?

Power Query does not have a direct "delete missing rows" button, but it will naturally reflect deletions if the source range is dynamic (like an Excel Table). If you are using a static range, switch to a Table object. Alternatively, you can add a step in the Advanced Editor to filter out any rows that no longer exist in the source, though this requires custom M code.

Does enabling background refresh affect data accuracy?

Background refresh allows Excel to remain responsive while data loads, but it can sometimes lead to viewing stale data if you interact with the sheet before the refresh completes. It does not inherently cause rows to persist, but it can create a perception of lag. Disabling it ensures you always see the latest data upon refresh, albeit with a potential pause in interface responsiveness.

What should I do if my query fails after deleting rows?

If the query fails, it is likely because a transformation step relies on a specific row index or count that no longer exists. Review the Applied Steps pane and remove any steps that assume a fixed number of rows. Replacing index-based operations with value-based filtering, such as filtering by a unique ID, will make the query robust against row deletions.

Is there a way to log changes in the source file for auditing?

Power Query itself does not log historical changes. To track deletions, you would need to implement version control in your source file or use a database that supports audit trails. You can also export the current state of your query results to a separate sheet before each refresh to maintain a history of what the data looked like at specific points in time.