Spreadsheets

Refresh a Power Query workbook when a new statement month arrives

A routine for adding a new month to a Power Query workbook: drop in the file, Refresh All, check row counts, and fix a slightly different layout.

Editorial illustration for Refresh a Power Query workbook when a new statement month arrives

When a new month's statement arrives, save it into the same folder as the earlier ones, open your workbook, choose Data, Refresh All, and check that the row count rose by the number of rows in the new file. That is the whole routine when the layout has not changed, and the rest of this article is the checking and what to do when it has.

What is the routine each month?

  1. Download the statement and save it into the statement folder, named like the others, for example savings-2025-07.csv. Do not edit it.
  2. Close the file, then open the workbook. Having the statement open in Excel leaves a temporary file that can upset a folder query.
  3. Choose Data, Refresh All (or Ctrl+Alt+F5). Wait for the Queries pane to show loaded rows.
  4. Compare the rows loaded with your before count plus the new file's count.
  5. Glance at the latest date in the combined table, and at the month grid.

How do you verify the refresh worked?

Keep a small check block on a sheet called Checks. Type last month's total in a cell as a constant, say 1,842 rows. After refreshing, =ROWS(Combined) should read that plus the new file's rows. If the new file has 156 rows, expect 1,998, because 1,842 plus 156 is 1,998. These numbers are invented, but writing down the expected figure before you refresh is what catches problems.

Also check the latest date: =MAX(Combined[Date]) should fall inside the new month. And check that no month is repeated: if a month's count doubles, an old file was probably copied in twice under another name.

What if the refresh shows an error?

Open the failing query, or click the warning in the Queries pane, to see the message. A common one reads along the lines of: The column 'Debit' of the table wasn't found. It means the new file calls that column something else, or has a different layout. Note the name that is different, and decide between two fixes.

  • If it is a heading rename, add the new heading to your mapping table, or rename the column in the sample-file query.
  • If extra lines appeared above the header, adjust Remove Top Rows, or switch to a rule that skips until the header word appears.
  • If a column vanished, use a step that tolerates missing columns, such as Table.SelectColumns with MissingField.UseNull.

Should the workbook refresh by itself?

You can ask Excel to refresh when the file opens. Right-click the table, choose Query Properties and tick Refresh data when opening the file. That is convenient, but it means a broken new file produces an error as you open the workbook, not when you chose to refresh. Many people leave it off and refresh deliberately, so they are looking at the numbers when they do.

What if the file is not in the right place?

The folder path is saved inside the query. If you rename or move the folder, or open the workbook on another computer with a different path, refresh fails. Edit it under Data, Queries and Connections, right-click the query, Edit, and change the Source step. A neat habit is to keep the folder path in one cell and read it through a parameter.

Habits that prevent trouble

  • Never rename or edit old statement files.
  • Keep one folder per account, so layouts do not mix.
  • Keep a Checks sheet with expected row counts and the latest date.
  • Make a copy of the workbook before changing queries.

An alternative to a refresh routine is SpendixAI, where you upload each new statement as a PDF, CSV or Excel file and see its status in the import history; it does not promise every layout will read perfectly, so you still review the rows.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports