To speed up a Power Query PDF import, make it read less: load only the tables you need, limit the pages with StartPage and EndPage, and turn off MultiPageTables for one huge table. Then cut editor overhead by managing preview downloads, and use Table.Buffer only where a test shows it helps. Each change shortens refresh on its own.
Why is a Power Query PDF import slow?
A Power Query PDF import is slow because Excel has to read every page you point it at, find the tables on each, and run every step of your query on the result. The PDF connector does table detection itself, which is heavy work on a long document. The more pages and tables involved, the longer each refresh takes.
What happens during a refresh
In Excel, the From PDF import is available in Microsoft 365 on Windows; Excel 2016 and 2019, Excel for Mac and Excel for the web do not list it, though Power BI Desktop has the same connector. When you refresh, the Pdf.Tables function, which is the engine behind Excel's From PDF import, opens the file and examines its pages. By default it looks at every page and tries to stitch similar tables on consecutive pages into one. Your later steps then filter, append, split and rename columns. If an earlier step is evaluated more than once, the PDF can be read more than once.
Where long statements hurt most
A bank statement of many pages produces many detected tables: transactions, summary boxes, headers and footers. Each one is work. Microsoft's Power Query PDF connector documentation has a section on accessing large PDF files that recommends reading pages one small range at a time, which is the core of the fixes below.
Loading fewer tables from the Power Query PDF Navigator
Loading fewer tables from the Navigator speeds up the import because each table you load becomes work in every refresh. The Navigator lists every table and page Power Query found, and it is tempting to tick them all. Pick only the transaction tables, and leave summary boxes and page headers out.
Tables versus pages
The Navigator shows two kinds of entries: detected tables and whole pages. Detected tables are already split into columns. Page entries hold everything on the page, which is useful when detection misses a table but means more cleanup. Prefer the detected transaction tables when they are complete.
One query, not twelve
If a statement has a transaction table on every page, avoid loading each as its own query. Load the file once, filter the list of tables to the ones you want, and expand them together. One query that reads the file once is faster than many queries that each open it.
Many files
For a folder of monthly PDFs, the connector documentation points to multi-file connectors such as the Folder connector. Combining from a folder keeps one query definition for all months instead of a copy per file.
Limiting pages with StartPage and EndPage in Pdf.Tables
StartPage and EndPage are options of Pdf.Tables that tell Power Query which pages to examine. The connector documentation describes them: StartPage sets the first page in the range, defaulting to 1, and EndPage sets the last, defaulting to the end of the document. Reading only the pages with transactions skips the rest entirely.
A before-and-after example
The first query reads the whole file and stitches tables across pages. The file path is an example.
let
Source = Pdf.Tables(File.Contents("C:\Statements\2026-03.pdf"))
in
Source
The second reads pages 2 to 6 only, and turns off multi-page stitching:
let
Source = Pdf.Tables(
File.Contents("C:\Statements\2026-03.pdf"),
[StartPage = 2, EndPage = 6, MultiPageTables = false]
)
in
Source
You edit this in the formula bar or the Advanced Editor, since the Navigator does not show these options.
When to switch off MultiPageTables
MultiPageTables controls whether similar tables on consecutive pages are combined automatically, and it is on by default. The documentation notes that for a PDF that is one single huge table, this option can collect very large intermediate values, so disabling it might help. With it off, you get one table per page and append them yourself, which is often quick.
Reading a long file in pieces
For a very long document, the documentation suggests selecting pages one at a time or a small range at a time and iterating over the document. In practice, that means a small function that takes a page number, calls Pdf.Tables for that page, and is run across a list of page numbers.
Preview downloads that slow the Power Query editor
Preview downloads slow the Power Query editor because the editor fetches sample data for each query and step so it can show you results. Microsoft's Power BI guidance on Power Query background refresh says Power Query caches up to 1,000 rows of preview data per query, and that with many queries, retrieving it can extend refresh time.
What the background setting does
That guidance describes an option called "Allow data preview to download in the background", found in Power BI Desktop under File, Options and settings, Options, Data Load, and set only for the current file. The article is written for Power BI Desktop, so treat it as a guide to how preview caching behaves rather than a menu path for Excel. It controls when preview data is fetched, separately from the data your query loads. The trade-off it mentions is that preview data can become out of date, and the editor then warns you. A Refresh Preview command on the editor's Home ribbon updates it on demand.
Habits that keep the editor quick
- Close the editor while a big refresh runs, rather than clicking through steps.
- Work on a short sample, such as one page set with StartPage and EndPage, then widen the range when the steps are final.
- Keep the number of queries low, since each one has its own preview.
Does Table.Buffer speed up a Power Query PDF refresh?
Table.Buffer can speed up a Power Query PDF refresh, but it can also slow it down, so test before keeping it. Microsoft's Table.Buffer reference says it buffers a table in memory, isolating it from external changes during evaluation, and warns that using it might or might not make queries run faster.
When buffering tends to help
Buffering helps when the same intermediate table is used several times later in the query, for example when a cleaned transaction list feeds both a merge and a group step. Holding it in memory once can stop the earlier steps, including the PDF read, from being evaluated again.
When it hurts
The reference names two costs: reading all the data and storing it in memory, and preventing downstream folding. A PDF source does not fold to a database, so the second cost matters less here, but the first applies to every row. On a small table used once, buffering only adds work.
How to test it
Time a refresh, add Table.Buffer after the step that cleans the PDF tables, and time it again. Keep the change only if the second time is clearly lower. Then try it with a second month's file to confirm.
Loading the PDF query to a table or only to the data model
Where you load the PDF query affects how long Excel takes after Power Query finishes. Loading to a worksheet table writes every row into cells, applies formatting and recalculates formulas that depend on them. Loading only as a connection, or into the data model, skips writing cells you may not need to see.
Choosing the destination
- Worksheet table. Best when you want to read, sort and filter the transactions directly.
- Connection only. Best for staging queries that feed another query, such as a per-page query that a final append uses.
- Data model. Useful when you build PivotTables over many months of rows.
Calculation after the load
Formulas that point at the loaded table recalculate after each refresh. On a large workbook, that can take longer than the import itself. Switching to manual calculation while you work on the query helps; the guide to setting Excel calculation to manual shows how and when to switch back. If the source is a big CSV export rather than a PDF, read how to open a large CSV file in Excel instead.
When the Power Query PDF route is the wrong tool
The Power Query PDF route is the wrong tool when the file is scanned, when layouts change every month, or when your tuning time exceeds the time you save. Pdf.Tables reads the PDF's text, so a page that is only an image gives it little to detect. A query that breaks with each redesign is not fast.
Signs to stop tuning
- The Navigator shows pages but no tables on a scanned statement.
- Columns shift position from month to month, so your steps fail.
- Rows wrap across lines and need hand-written M code to rejoin.
- Refresh is still minutes after every step in the checklist below.
Scanned pages bring a different problem: recognition errors in the digits. The guide to bank statement OCR digit errors explains how to catch them.
A tuning checklist, in order
- Load only the transaction tables you need.
- Set StartPage and EndPage to the pages with transactions.
- Set MultiPageTables to false if the file is one long table.
- Combine monthly files from a folder in one query.
- Close the editor during refresh, and manage preview downloads.
- Test Table.Buffer on any table used more than once.
- Load staging queries as connection only.
- Set calculation to manual while you edit.
If a statement converter suits you better than maintaining a query, FastBankConvert is getting ready to open, built to read a typical digital statement in seconds and to flag balances that do not add up before you download. Get started to hear when trial conversions open.