Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
davidz106
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. 

2 REPLIES 2
ImkeF
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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors