cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mramstead1
Regular 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
Super User
Super User

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors