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
mramstead1
Frequent Visitor

Power Query Model Optimization

Hello All!

 

I am working on a model to track the activities completed by our service team to see whether they are being over/underworked. This is done by taking data from our system and using a query to link it to Excel. The model is complete but I am somewhat new to using PowerQuery/Pivot and I don't believe that it is optimized enough.

 

I need to start tacking on historical information so that I can compare month to month information. Currently I am just increasing the size of the source data every month in order to eliminate extra connections (Query starts as Jan 1 - Jan 31, next month becomes Jan 1 - Feb 28). My concern with this method is that each month contains around 10,000 - 15,000 rows of data. Currently the model is floating around 20,000 lines of data in the query, and it takes just over 7 minutes to refresh the workbook, and I'm worried that this will increase exponentially as the months go on. 

 

Is there a more efficient way to query this type of data in general? I know that Excel is supposed to be able to handle millions of rows of data, but I'm not sure if it can handle this in queries. I am more than happy to post a copy of the workbook if the inefficiencies involve my query steps themselves putting a strain on the workbook.

 

Thank you in advance for your help!

1 REPLY 1
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @mramstead1 .  Do you have access to Power BI?  There is an option to incrementally load data to avoid re-query every day since the being of time.

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