Data Refresh wont remove rows

[jamescosten]

I have a Dataset then when I refresh it does not remove rows that have been deletd from an Excel Sheet.

My Excel List compared to my Data.

My rows are not hidden, and I have it connected to a data source that when refreshed deletes rows and adds rows if not in the table.

[v-jialongy-msft · Accepted solution]

Hi @jamescosten 

If deleted rows in the Excel sheet are still appearing in your Power BI dataset post-refresh, this can be due to several reasons:

1. Check the Query Settings in Power Query Editor

Open Power Query Editor: Go to 'Home' > 'Transform Data' in Power BI Desktop.
Examine the Applied Steps: On the right side of the Power Query Editor, review the steps applied to the dataset. Ensure no steps are inadvertently preventing the deletion of rows. For example, steps that might filter out rows or merge queries might need adjustment.

2. Refresh Behavior
Full Refresh: Ensure that you're performing a full refresh, which reimports the entire dataset rather than an incremental refresh, which might only update parts of the data.

3. Data Source Settings
Path and Connection: Verify the path and connection settings to the Excel file. Ensure that the connection is not to a previous version of the file or a cached copy.
Data Range: Check if the defined data range in the query is correct. If you have specified a static range (e.g., Sheet1!A1:D100), it might not include new rows or exclude rows that have been deleted. Changing this to a dynamic named range or a table reference in Excel can help.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.