A Power Query refresh over a big statement folder usually slows down because it opens every file, reads every column and repeats the work for each query that depends on it. The fastest wins are to filter the file list before combining, drop unused columns as early as possible, stop several queries from rereading the folder, and load to connection only where you do not need a sheet.
Start by measuring so you know what helped. Time one full refresh with a stopwatch and write the number down. Say you have 36 monthly statement files and the refresh takes 150 seconds, which is about 4 seconds per file. Every change below should be judged against that figure.
What should I change first?
- Filter the folder listing before you expand anything. In the file list view, keep only the extension you need, say csv, and exclude names containing "old" or "copy". Rows you drop here are never opened at all.
- Choose Columns early. If a statement has 12 columns and you need date, narration, debit and credit, remove the rest in the first few steps so later steps carry less data.
- Filter rows next. Remove blank rows, repeated header rows and any opening balance lines as soon as the table exists, before sorting, merging or adding columns.
- Set data types once, near the end, not after every step. Each automatic Changed Type step is another pass over the rows.
Why does my refresh read the same folder twice?
If you built a monthly summary and a merchant summary as two queries that each start from the folder, the folder is read for each one. Build one cleaned query, then use Reference for the summaries, and set the cleaned query to Only Create Connection. This stops an extra copy being loaded to a sheet. Remember that Excel may still evaluate shared steps again per dependent query, so test the timing before assuming a gain.
Also avoid merging a huge table with itself or with another large table just to look up a category. Use a small category list and merge against that instead.
Does the background preview matter?
It can. While you edit, Power Query may keep downloading previews behind the scenes, which uses time and memory. In the Query Options dialog under Data Load, you can untick the setting that allows data preview to download in the background. Your refresh results are unchanged, but the editor feels less busy on large folders.
What about where the files are stored?
- Keep the folder on your own drive. If the files sit in a cloud folder set to download on demand, each file may need to be fetched before it can be read.
- Prefer CSV or Excel exports over PDF where your bank offers them. Reading text from PDF pages generally takes longer than reading a plain CSV.
- Avoid subfolders inside the statement folder unless you mean to include them, because the folder source normally includes files from every level below it.
- Move statements from closed years into a separate archive folder outside the one the query reads, if you only report on the current year.
| Change | What it saves | Where to do it |
|---|---|---|
| Filter file list by extension | Opening files you do not need | Folder listing step |
| Choose Columns early | Memory and later step time | First steps of the combined query |
| Reference plus connection only | Extra loaded copy of the rows | Load To dialog |
| Turn off background preview | Editor lag while you work | Query Options, Data Load |
How do I find the slow step?
In the Power Query editor, the Tools tab offers Diagnostics if you need detail. A simpler route is to disable steps one at a time, refresh and compare with your baseline. A step that adds 30 seconds on its own is your target.
When is the query simply too big?
A folder of 10 years of statements, with every narration kept as text, may be more than a worksheet-based query is comfortable with. Consider loading only the last two financial years from April to March, keeping older years as a saved summary. If refreshing still takes minutes, a tool built for statements can help; SpendixAI accepts batches of statements in PDF, CSV or Excel and keeps an import history, so you are not rebuilding a heavy query every month.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
