Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Power Query Very Slow using get files from folder

Brass Contributor

I have a Master file that uses power query to import all xlsx files from a folder. Pretty simple setup, since all the files are setup (have the same headers 55 columns) and formatted the same way. Currently the folder that holds the files is up to 103 files at 145MB. When I open the Master file and select refresh all it takes up to five minutes for it to update. This is way too long. I used power query because I figured it would be the fastest way to update the master file as new files/data is added. Any suggestions on making it faster?

I have already selected fast data mode, and data options within power query to make it as fast as I could. I have recently received the compatibility warning when I try and edit the query Queries in this workbook might be incompatible with your current version of Excel. Queries were authored with a newer version of Excel or Power Query and might not work in your current version. PQ version is 2.96.842.0

My PC specs easily exceed the requirements to refresh this data. I am using 365 Enterprise 3.

1 Reply

@heylookitsme 

- XLSX files are slower to read than (i.e.) CSV files due to the complexity of the file structure
- Sheets are generaly slower to read than Tables
- If you rely on the From Folder wizard that initially creates Helper Queries you can expect slightly better performances (not much due to #1) if you write your own code
- See Speed/Performance aspects from ImkeF, there are probably a few things for you...
- Just in case also have a look to Excel.Workbook() And The delayTypes Option from ChrisWebb