I built a power query Parse function (this is a rather complex function with approx. 50 lines of M-code) which can parse my complex excel sheet.
I have another query that:
1. returns a list of all the relevant excell sheets in the folder
2. invokes Parse function on each of them (returns of parsed excel sheets in form of appended table)
This is working really well up to about 10 excel files (takes approx. 20-30s to load in 32-bit excel and less in 64-bit PBI). I tried to run it with a folder of 300 files and after 5 minutes it just crashed.
Is there a way I could optimize this? I reckon if a function would be invoked sistematically sheet after sheet this could work much better. I know PBI is probably not a tool for this but it was hard to write parse function in M-code and it is going to be a nightmare to write it in Python.