# Reuse Power Query for Monthly Statements, Free

- Author: CheapBankConvert (https://cheapbankconvert.com)
- Published: 2026-10-17
- Category: Free tools you already own
- Canonical: https://cheapbankconvert.com/blog/reuse-power-query-for-monthly-statements

> Reuse Power Query for monthly statements: build the import once, point it at next month's PDF, refresh, and skip paying to convert each file.
You can reuse Power Query for monthly statements from the same bank by building the PDF import and its cleanup once, then pointing the query's source at each new month's file and choosing Refresh. Every recorded step reruns on the new PDF. It works while the bank's layout stays the same and breaks quietly when it changes.

## Can you reuse Power Query for monthly statements from the same bank?

Power Query can be reused for monthly statements because a query is a saved list of steps, not a one-time copy of data. Change the file the first step reads, refresh, and the same cleanup runs on the new statement. One bank, one account and one layout is the case where this pays off.

Three conditions decide whether it will work for you:

- **A text PDF each month.** Power Query's PDF connector reads tables from text. [Microsoft's PDF connector reference](https://learn.microsoft.com/en-us/power-query/connectors/pdf) describes it as returning the tables found in a PDF file. Scans return nothing useful.
- **The same layout each month.** Banks keep a statement design for years, so this usually holds, until a redesign.
- **Desktop Excel with From PDF.** You need Get Data, From File, From PDF on the Data tab of the Excel on your computer. Microsoft lists that connector for Microsoft 365 on Windows, not for Excel on a Mac or in the browser.

Different banks need different queries. If you have a checking account at one bank and a card at another, build one query for each and keep them in the same workbook.

## Building the monthly statement query once, with steps that survive a refresh

Build the monthly statement query on one real statement, and choose steps that depend on content rather than position. A step that says "remove rows where Date is not a date" survives next month. A step that says "remove the top 7 rows" breaks the month the bank adds a notice to page one.

The one-time build, using an example checking statement:

1. Save the statement to a fixed folder, for example Documents\Statements\Checking, and name it with the month, such as 2026-09.pdf.
2. In Excel, choose Data, Get Data, From File, From PDF, and select the file.
3. In the Navigator, pick the item that holds the transactions and choose Transform Data.
4. Promote the first row to headers, if it holds the column names.
5. Filter out repeated header rows by removing rows where the Date column holds the word Date.
6. Remove blank rows.
7. Set Date to a date type and the amount columns to decimal numbers.
8. Add a check column, if you like, that recomputes the balance.
9. Close & Load the result to a sheet.

The repeated header rows in step 5 come from each page of the PDF printing the column titles again. [Removing repeated headers in Excel from each PDF page](/blog/remove-repeated-headers-in-excel) covers the other ways to do it, including the formula route for data that never passed through Power Query.

### What each step depends on

The Applied Steps pane on the right of the Power Query window lists every step in order. Read it with one question in mind: what does this step assume about next month's file? The table is an example of a typical list.

| Applied step | Depends on | Survives next month? |
| --- | --- | --- |
| Source | The file path | Only if you update the path or keep the same name |
| Navigation | The table's Id, such as Table002 | Breaks if the transactions move to a different table |
| Promoted Headers | The first row holding titles | Usually |
| Filtered Rows (Date is not "Date") | The header text | Yes |
| Removed Top Rows (7) | A fixed row count | Often breaks |
| Changed Type | Column names | Breaks if a column is renamed or added |

Replace fragile steps before you rely on the query. A filter on content beats a count of rows every time.

## Pointing a saved Power Query at next month's statement PDF

To point a saved query at next month's PDF, change the path in its Source step and refresh. Open the query in Power Query, select Source in Applied Steps, and edit the file path in the formula bar or through the step's settings. Then choose Close & Load, and Excel replaces last month's rows with the new month's.

There are two habits that make this easier:

- **One file name, replaced monthly.** Save each new statement over a file called current.pdf in the same folder, and keep a dated copy elsewhere. The query never needs editing. You only refresh.
- **A path cell.** Advanced users put the file path in a worksheet cell and have the Source step read it. Changing the month is then a matter of typing a new path into the sheet.

After every refresh, compare the loaded rows with the statement. Count the transactions and check the closing balance. A refresh that finishes without an error is not proof that it read everything.

### Keeping each month

A refresh replaces the old rows. If you want a running history, copy each month's loaded rows to an archive sheet before you refresh, or use the folder method below, which keeps every month in one table.

## The folder method: a year of monthly statements in one Power Query

The folder method loads every statement in a folder into one table, and picks up new files on refresh. Use Get Data, From File, From Folder, then combine the files. Microsoft's [overview of combining files in Power Query](https://learn.microsoft.com/en-us/power-query/combine-files-overview) says this works for files with the same file type and structure.

Microsoft's PDF connector page points the same way: to import multiple PDF files at once, it recommends a multi-file connector such as Folder.

### The steps

1. Put all the statements for one account into one folder, and nothing else.
2. Choose Data, Get Data, From File, From Folder, and select the folder.
3. Choose Combine and Transform. Power Query uses one file as the example, by default the first in the list.
4. In the dialog, pick the table that holds the transactions in that example file.
5. Power Query creates a query called Transform Sample File. Do your cleanup there: headers, blank rows, types.
6. Close & Load. The result has one row per transaction across all files, plus a Source.Name column naming the file each row came from.

Microsoft's overview explains why step 5 matters. The sample file query is linked to a function that runs on every file, so a change made to the sample is applied to all of them. It also notes that a Change Type step at the end of the output query can cause errors, so check that last step if column names change.

Next month, drop the new PDF into the folder and refresh. Filter the file list by extension so a stray spreadsheet in the folder is ignored, as Microsoft's overview suggests.

## Why does a monthly statement refresh fail?

A monthly statement refresh fails when the new PDF differs from the one the query was built on. The usual causes are a moved table, a renamed or extra column, a file that is not where the Source step looks, or a scanned statement mixed in with text ones. Most show up as errors; some show up as missing rows.

Work through the causes in this order:

- **The file moved or was renamed.** The Source step reports that it cannot find the file. Fix the path.
- **The transactions are in a different table.** A longer month can split the list differently, so the Navigation step picks the summary box instead. The error, or the strange data, appears at the Navigation step. Pick the right table again.
- **A column changed.** A new column or a renamed heading breaks the Changed Type step. Remove and redo that step.
- **Year-end dates.** Statements that print 12/30 and 01/02 without years may get the wrong year. Check January rows each December.
- **A scan in the folder.** A scanned statement produces no table, which can break the combine. Microsoft's combine dialog offers Skip files with errors, but a skipped month is a missing month, so look at what was skipped.

The PDF connector page has one more option worth knowing for long files: MultiPageTables, on by default, combines similar tables on consecutive pages. If a refresh suddenly merges the wrong blocks, that setting is where to look.

The quiet failure is worse than the loud one. A refresh can finish, load 61 rows instead of 64, and say nothing. The closing balance check catches it. So does a second pass, done independently, on any month that matters, which is the idea behind [double-key data entry](/blog/double-key-data-entry): two separate captures, compared line by line.

## When reusing Power Query costs more than converting each statement

Reusing Power Query costs more than converting when the build and the repairs take longer than the statements are worth. If you have three statements a year, or your bank changes layouts often, or you have a different bank every month, the one-time build never pays back. It pays back with many months from one stable layout.

A rough way to judge it, with example times you should replace with your own:

- **Build:** one to two hours the first time, including testing on two months.
- **Each month:** five minutes to drop the file in and refresh, plus a few minutes to check the balance.
- **Repairs:** an hour each time the bank redesigns its statement.

Twelve statements a year from one bank fits the reuse case well. Statements from five different banks, a few times a year each, do not. For a mix like that, [the four free ways to convert a PDF to Excel, ranked](/blog/can-i-convert-pdf-to-excel) may serve you better, or a per-statement converter. [CheapBankConvert](/) plans to charge once per statement, from $2.99 for up to 10 pages, with the first 2 pages previewed free. It is not open yet.

Mac users without From PDF have a different route to the same automation. [Extracting PDF text with Automator on a Mac](/blog/automator-extract-pdf-text-on-mac) builds a reusable workflow from free built-in tools, though it hands you text, not a table.