'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Excel Power Query Keeping Deleted Source Rows After Refresh
Question details
The user is experiencing an issue where Excel Power Query successfully adds new rows during a refresh but fails to remove rows that were deleted from the source table.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Refreshing a Power Query connected to an Excel table after deleting rows from the source data.
- Observed behavior
- Newly added rows appear correctly after a workbook refresh, but deleted rows persist in the query output.
Ensure that you have saved your workbook and that the rows you intend to delete are completely removed from the actual Excel table range, rather than just clearing their cell contents.
Verify Source Table Range and Refresh Query Preview
The most common reason deleted rows reappear is that they were cleared instead of deleted, or they still exist within the defined boundary of the source table.
Power Query reads the entire defined table range. If you only press the 'Delete' key on your keyboard, the cell contents are cleared, but the blank rows remain part of the table object.
Open your source worksheet, select the rows you want to remove, right-click, and choose 'Delete' > 'Table Rows' to completely remove them from the table object.
Check the bottom-right corner of your formatted Excel table. Ensure the small resize handle is immediately after your last row of actual data.
Go to the 'Data' tab on the Excel ribbon and click 'Get Data' > 'Launch Power Query Editor'.
In the Power Query Editor, go to the 'Home' tab and click 'Refresh Preview' to force the editor to pull the latest table state.
Click 'Close & Load' in the top left corner to apply the updated query preview to your workbook.
Inspect Query Steps and Merged Sources
Sometimes later steps in Power Query or appended/merged queries reintroduce data that was successfully removed from the primary source.
Try WPS Office for Seamless Data Management
If you are tired of complex query errors, caching issues, and extensive troubleshooting in Microsoft Excel, consider switching to WPS Office. It provides a lightweight, highly compatible, and user-friendly spreadsheet environment for all your daily data management needs.
- 1. Download and install: Visit the official WPS website and click 'Download WPS Office Free' to install the suite on your device.
- 2. Open WPS Spreadsheet: Launch the application and select 'Spreadsheet' from the home screen.
- 3. Import your files: Click 'Open' or simply drag and drop your existing Excel workbooks into the workspace to continue working seamlessly.
Frequently Asked Questions
Why does Power Query add new rows but keep deleted ones?
This typically occurs if the rows in the source Excel table were only cleared of their contents (using the Delete or Backspace key) rather than completely removed using the 'Delete Table Rows' command. Power Query reads the blank rows as part of the table.
Does using =Excel.CurrentWorkbook() affect how rows are refreshed?
Yes. Using =Excel.CurrentWorkbook() pulls in all tables or named ranges present in the current file. If you have backup copies of tables, hidden worksheets, or overlapping named ranges, the query might pull in the old deleted rows from those other sources without you realizing it.
How do I force Power Query to completely clear its cache?
You can clear the cache by opening the Power Query Editor, navigating to File > Options and settings > Query Options. Under the 'Global' section, select 'Data Load' and click the 'Clear Cache' button.
