Power Query taking forever to perform even simple steps

[justinh]

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.

[v-yingjl]

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]

@justinhplease untick the following and try

[justinh]

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%.

[smpa01]

@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.

[justinh]

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. 

[smpa01]

@justinhglad it worked out for you !!!

[justinh]

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.