Why Power Query refresh is slow with many files, and how to speed it up
By the Analistable team · Updated · 5 min read
A folder combine is slow because Power Query opens and parses every file on every refresh, and Excel workbooks are slow to parse. Filter the file list on name, extension or date before the Transform File step, remove repeated type and sort steps, keep files on a local or fast drive, and turn off background data previews. Converting old files to CSV, or combining them once into an archive file, gives the biggest gains.
Part of our guide: How to merge data in Power BI, Power Query and Tableau
Where the time goes
A combine query from a folder does the same work for each file: read the bytes, unpack the workbook, find the sheet, apply your steps, then stack the result. With 20 files that is quick. With 400 monthly exports it can take many minutes, and each extra step multiplies across every file.
| Stage | Share of refresh | What drives it |
|---|---|---|
| Listing the folder | Small | Network latency, SharePoint paging |
| Opening and parsing each .xlsx | Largest | File count and size, xlsx unzip and XML parsing |
| Per-file transform steps | Medium | Number of steps, type changes, sorts |
| Stacking and final steps | Small to medium | Sorts, merges, Group By on the full table |
The proportions vary with your machine and storage, so measure before and after each change. In Power BI Desktop, View → Performance analyzer does not time Power Query; use the refresh dialog timings or Query Diagnostics (Tools → Start Diagnostics in the Power Query editor) instead.
1. Filter files before you open them
The cheapest file is the one you never open. The folder listing already has Name, Extension, Date modified and Folder Path, so filter on those before Invoked Custom Function:
Source = Folder.Files(FolderPath),
Filtered = Table.SelectRows(Source, each
Text.Lower([Extension]) = ".xlsx"
and not Text.StartsWith([Name], "~$")
and not Text.Contains([Folder Path], "\Archive\")
and [Date modified] >= #datetime(2025, 1, 1, 0, 0, 0)),
Invoked = Table.AddColumn(Filtered, "Data", each #"Transform File"([Content]))Folder.Files reads subfolders too, so a forgotten Archive or Backup folder can double the work. Filtering on date is the closest Excel gets to incremental refresh; Power BI has a proper incremental refresh feature, which needs date-based RangeStart and RangeEnd parameters and works best with sources that can filter by date.
2. Trim the per-file transform
- Remove duplicate Changed Type steps. Combine Files adds one in the sample transform and another in the main query; you usually need only one, at the end.
- Remove columns as early as possible, ideally right after promoting headers, so later steps handle less data.
- Do not sort inside the transform. Sorting forces the whole table to be read before the next step; sort once at the end if you need it.
- Avoid merges inside the per-file function. Merge once, after stacking, so the lookup table is read once rather than once per file.
3. Change the settings that cause extra evaluations
| Setting | Where | Effect |
|---|---|---|
| Allow data preview to download in the background | Excel: Query Options → Current Workbook → Data Load | Stops previews of every query evaluating while you edit |
| Background data (previews) | Power BI Desktop: Options → Current File → Data Load | Same idea for Power BI |
| Enable load | Right-click a helper query | Helper queries that only feed others should not load to the sheet or model |
| Privacy levels | Options → Privacy | Privacy checks can stop query folding and add work when sources mix; only relax them where your data policies allow |
Option names and locations move between versions of Excel and Power BI Desktop. If a label differs on your screen, search for it in the options dialog.
4. Use Table.Buffer carefully
Table.Buffer holds a table in memory so that later steps don't re-read the source. It helps when a small table, such as a list of valid codes, is referenced many times, for example inside each file's transform. It can make things slower when used on the large stacked result, because it blocks streaming and folding and uses memory. Buffer small lookups; leave the big table unbuffered unless you have measured a gain.
// buffer a small lookup once, outside the per-file function
ValidRegions = Table.Buffer(Excel.CurrentWorkbook(){[Name="Regions"]}[Content]),
Checked = Table.SelectRows(Combined, each List.Contains(ValidRegions[Region], [Region]))5. Change the files themselves
The biggest gains often come from outside Power Query:
- CSV parses faster than .xlsx. If exports can be CSV, ask for CSV.
- Archive closed periods. Combine last year's 250 files once into one file, then combine that file with this year's folder. Refresh then opens 1 + n files, not 250 + n.
- Local beats network. SharePoint and network drives add latency per file. For SharePoint,
SharePoint.Contentsnavigating to one folder is often quicker thanSharePoint.Files, which lists the whole site.
If the goal is to answer questions over the files rather than to maintain a refresh, Analistable loads each file into DuckDB in your browser and runs SQL on it, without uploading the data. See Power Query vs Analistable for when each fits, and the Power BI, Power Query and Tableau guide for the rest.
Measure before and after
Change one thing at a time and time a full refresh, not a preview. Previews read only the first rows of a few files, so they can look fast while a full refresh is still slow. Keep a short log so you know which change helped.
| Change | Refresh time | Saving |
|---|---|---|
| Starting point | 6 min 10 s | - |
| Filter out Archive subfolder | 3 min 40 s | 2 min 30 s |
| Remove duplicate Changed Type and sort | 3 min 05 s | 35 s |
| Background previews off | 3 min 05 s | 0 s (editing feels faster) |
| Older files archived into one CSV | 1 min 20 s | 1 min 45 s |
The background preview setting rarely changes the refresh itself but makes the editor much more responsive. Your timings will differ; the pattern of fewer files and fewer steps holds.
Frequently asked questions
- Why is my folder query slower in Excel than in Power BI?
- They use the same engine, but settings, memory and background previews differ. Turning off background data previews and disabling load for helper queries often closes the gap.
- Does Table.Buffer speed up combining files?
- Sometimes. It helps for small lookup tables referenced repeatedly. On the large combined table it can slow things down and use a lot of memory.
- Can Excel Power Query do incremental refresh?
- Not as a built-in feature. The practical substitute is filtering the folder list by date and archiving older files into one combined file.