Power Query Problems

Replace Monthly CSV Data in Excel Power Query Without Stale Rows

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 868 views

Question details

Replace old invoice data with the complete contents of a monthly CSV file without retaining stale rows or disrupting worksheet references.

Replace Monthly CSV Data in Excel Power Query Without Stale Rows
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.
Before you start

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.

Solution 1Recommended

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.

1
Import the CSV

In Excel, select Data > From Text/CSV, choose the current invoice CSV, and select Transform Data to review it in Power Query.

2
Check the columns

In Power Query Editor, confirm the column headers and data types. Apply any needed transformations, then select Home > Close & Load To.

3
Create a dedicated output table

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.

4
Update dependent formulas

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.

5
Replace the source and refresh

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.

Load the CSV into a dedicated Excel table and refresh it monthly
If the CSV path changes: Update the source path in the query before refreshing. In Data > Queries & Connections, right-click the query, select Edit, and adjust the Source step in Power Query Editor.
Free Microsoft Office alternative

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.

Open and review invoice spreadsheets in WPS SpreadsheetsWork with Microsoft Excel formats such as XLSXUse a familiar interface for everyday spreadsheet tasksMove existing Office files into a lightweight, free suite
microsoft office alternative - wps office

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.