Analistable

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.

Rough cost of each stage (illustrative, 300 workbooks of 5,000 rows each)
StageShare of refreshWhat drives it
Listing the folderSmallNetwork latency, SharePoint paging
Opening and parsing each .xlsxLargestFile count and size, xlsx unzip and XML parsing
Per-file transform stepsMediumNumber of steps, type changes, sorts
Stacking and final stepsSmall to mediumSorts, 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

Settings worth checking
SettingWhereEffect
Allow data preview to download in the backgroundExcel: Query Options → Current Workbook → Data LoadStops previews of every query evaluating while you edit
Background data (previews)Power BI Desktop: Options → Current File → Data LoadSame idea for Power BI
Enable loadRight-click a helper queryHelper queries that only feed others should not load to the sheet or model
Privacy levelsOptions → PrivacyPrivacy 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.Contents navigating to one folder is often quicker than SharePoint.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.

Refresh log for a 300-file folder (example)
ChangeRefresh timeSaving
Starting point6 min 10 s-
Filter out Archive subfolder3 min 40 s2 min 30 s
Remove duplicate Changed Type and sort3 min 05 s35 s
Background previews off3 min 05 s0 s (editing feels faster)
Older files archived into one CSV1 min 20 s1 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.

Related guides