cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justinh
Advocate III
Advocate III

Power Query taking forever to perform even simple steps

All, 

 

My Power Query is taking forever to perform simple tasks. In the screenshot below, it is trying to create a date by first pulling the min year from a list of years. That list of years is a simple query itself that uses Date.Year(DateTime.LocalNow()) and creates a list. 

 

For this particular value, there are no databases and no excel files involved, just DateTime.LocalNow() and math. The yellow dots just keep scrolling for minutes on end. I've cleared all 3 caches. I've restarted my machine. I don't know what else to do. 

 

This file has a lot of queries and they are all taking forever. I just chose this one to illustrate how even simple queries are taking a long time. 

 

Please help. Any advice or tips?

 

Thank you, 

-Justin

 

Yellow Dots for minutes on end for a simple query.Yellow Dots for minutes on end for a simple query.

 

7 REPLIES 7
v-yingjl
Community Support
Community Support

Hi @justinh ,

You can try to use Buffer() funtion to optimize your previous query like Table.buffer(), List.buffer().

Please refer: 

  1. Table.Buffer 
  2. List.Buffer 

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

smpa01
Super User
Super User

@justinhplease untick the following and try

Capture.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Thank you, but that did not help. I already had all but the "Background Data" option unchecked, and unchecking that one did not do anything. 

 

Also, I should note, my CPU is running at 100% the whole time, with PBI accounting for 75% of that, and within PBI there are 3 instances of "Microsoft Masup Evaluation Container" each taking about 25%.

@justinhList.Generate has perfomance issue and I gfaced that too Are you pulling the background table from SQL, if yes, you can run theloop there, would be faster.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Unfortunetly, it looks like there is something borked with this file. I created a brand new PBIX file and wrote the same Date.Year(DateTime.FixedLocalNow()) in both. New file gave me the result instantly, old file is still spinning its wheels. 

 

justinh_0-1612215516265.png

 

@justinhglad it worked out for you !!!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


It hasn't worked out quite yet. I really don't want to have to start over with a new file. 

Thank you for your time, though. I appreciate it. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors