Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

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. 

Super User
Super User

Hi @davidz106 ,
there can be really many reasons. Please check out my collection of potential bottlenecks here: Speed/Performance aspects – The BIccountant


Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors