How many workbooks can Power Query handle?

Hi.

My employer want me to set ut a log so we can track how many items our different suppliers deliver, that we have either not ordered, or where they deliver too few or too many items, which we have to return.

For every return, we have to fill out a report, which is sent to the supplier we return the goods to.

I am thinking that I can create a worksheet called "Datadump", use simple VBA to transfer data from the report and then import it all to one excelfile using the Excel.Workbook.

My question is how many different excel files can Power Query handle without getting slow?

Usually there will be 1 or 2 rows of data in each file, and approximately 2.500 files a year. So few rows in totalt, but many different files.

edit: and of course: thanks a lot in advance for answering this post

I'd say the answer to this is "That depends". It depends on the combination of your RAM, processor, hard drive (SSD vs HDD), Excel version (2010/2013/2016), Excel bitness (32/64 bit) and probably a couple of other things.

Overall, I'd say that it may get slow, but will it be slower than VBA... don't know.

Should be easy enough to test though... Create a folder with 10 files in it. Copy that folder 25 times and drop it in a subfolder. Copy that folder 10 times and drop it into a different folder. Run your PQ script against the top folder.

I don't think you need different data, just some kind of data to run a test on it.

thank you for answering.

I will test it as you suggest. Will let you guys know how it went


Kristian

Just to expand on what Ken says I've had Power Query handling over a 1000 text files each with between 10 and 400 lines of data and it coped very well. Whether it's quicker than VBA, well it felt like it and it took considerably less time to create and test.

Paul Christie

I have now tested as you suggested, Ken, and for me it seems to be no problem for PQ to handle import from so many files.