Forum Discussion

davidz106's avatar
davidz106
Icon for Helper III rankHelper III
3 years ago

Power query optimization

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. 

Need Help

2 Replies

    • davidz106's avatar
      davidz106
      Icon for Helper III rankHelper III
      3 years ago

      Thanks. I read the list. Step one and delayTypes are two things that could be beneficial to my case. My excel files are in range of 200-400kB so this is not really lightweight either. 

       

      Is there a tool I can use that would tell me a time needed to process a line of code (even inside of a function)? Maybe this way I could find a culprit easier.