Replace Monthly CSV Data in Excel Power Query Without Stale Rows
Question details
Replace old invoice data with the complete contents of a monthly CSV file without retaining stale rows or disrupting worksheet references.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A workbook needs to refresh its invoice data from a new monthly CSV file while keeping dependent formulas usable.
- Observed behavior
- The desired result is a query output that reflects only the current CSV data, including when the new file has fewer rows.
Check that each monthly CSV has consistent column names and data types. Decide on a stable file path and filename for the current CSV, or be prepared to update the query's source path each month.
Load the CSV into a dedicated Excel table and refresh it monthly
Use a Power Query output table so each refresh replaces the previous result and formulas can refer to the table by name.
In Excel, select Data > From Text/CSV, choose the current invoice CSV, and select Transform Data to review it in Power Query.
In Power Query Editor, confirm the column headers and data types. Apply any needed transformations, then select Home > Close & Load To.
In Import Data, choose Table and New worksheet, then select OK. Keeping the output on its own sheet gives it room to grow or shrink without interfering with nearby cells.
Select the output table and use Table Design > Table Name to give it a descriptive name, such as Invoices. Where applicable, change formulas to structured references such as =SUM(Invoices[Amount]) instead of fixed cell ranges.
When the next CSV arrives, place it at the same path with the same filename as the original. Open the workbook and select Data > Refresh All. The output table will reflect the current query result, including removing rows left over from a longer previous file.

Work with invoice spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Microsoft Office formats, making it easier to move existing files. It does not fix Excel Power Query refresh issues.

Frequently Asked Questions
What happens if the new CSV has fewer rows?
When you refresh a Power Query output table with Data > Refresh All, the table reflects the new query result rather than retaining rows from the previous result.
How do I point the query to a different CSV file?
Open Data > Queries & Connections, right-click the query, and select Edit. In Power Query Editor, change the Source step to use the new file path, then close and load the query.
Why should formulas use the table name?
Structured references such as Invoices[Amount] refer to a table column as the output changes size. Fixed cell ranges may not cover all rows when the table grows.
Can I place other data below the query output?
It is safer to load the table onto a separate worksheet. Data directly below it can prevent the output table from expanding when a later CSV contains more rows.




