cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Power BI Desktop automatic refresh queries one-by-one

Hi,

 

I have multiple queries and when refreshing, Power BI evaluate and refresh all queries at the same time.

And some time, it crashs for memory issues or what ever reason.

I may refresh the queries one-by-one manually but it implies to stay in front of the file.

Is there no "automatic" refresh button or script which could refresh the various queries one after each other?

 

Thanks for your tips

 

JB

1 ACCEPTED SOLUTION
GilbertQ
Super User
Super User

Hi @Anonymous

 

There is a way to do this, in the Power BI Desktop click on File, Opions & Settings, then Options.

 

Then once you see the options deselect Parallel Loading of tables as shown below.


Power BI - Parallel Loading.png

 

That will then not load the data in parallel but rather one at a time.





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

Proud to be a Super User!







Power BI Blog

View solution in original post

18 REPLIES 18
GilbertQ
Super User
Super User

Hi @Anonymous

 

There is a way to do this, in the Power BI Desktop click on File, Opions & Settings, then Options.

 

Then once you see the options deselect Parallel Loading of tables as shown below.


Power BI - Parallel Loading.png

 

That will then not load the data in parallel but rather one at a time.





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

Proud to be a Super User!







Power BI Blog

View solution in original post

I've researched this a ton but can't find the answer to these questions:

  • When parallel loading is disabled, what determines the load order?
  • Is it the order of the queries in the query list under Edit Queries?
  • How do query dependencies come into play?
  • Also, when parallel load is enabled, does the refresh give any priority to predecessors in the dependency network?

I THINK the load order is only based on the order of the query list when this parallel load is disabled. But, I am not sure how query dependencies play into that.

Besides this thread, here were a couple other links I found useful: 

https://community.powerbi.com/t5/Desktop/Query-Dependencies/td-p/28306 (Makes good points about how caching order effects what is "current" when the loads happen)

https://www.youtube.com/watch?v=YfdypVSYb9c  (Shows how to introduce delays to manually force the order)

In my situation I have a table which is a join of two predecessor tables. I have an index column which is created after a sort which is done after the join. If the refresh of this table hits the cached version of one or both of the predecessor tables then the index value will be incorrect. This creates some odd behavior in the report. It usually resolves itself with multiple refreshes but makes the data feel very unreliable. It would be great to get some more clarity on these questions!

naubrey
Frequent Visitor

Thanks so much. Smiley Very Happy

PBI can be very cryptic with their error messages but your advice has solved a long term issue that I have been expereiencing. 

Hi @naubrey

 

Glad to hear that it solved your issue!





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

Proud to be a Super User!







Power BI Blog

I am unchecking the option Enable Parrallel loading of tables but this is not working and both my tables are loading in parrallel. My data source is two tables from Sql Server. 

 

Capture2.PNG

 Capture.PNG

 

Hello @Murtuzahm ,

 

we are experiencing some similar behaviour. If I am unchecking the "Enable Parrallel loading of tables" it works fine in PBI Desktop but if I am publishing the report to the report server and scheduling a job there it ingnores the setting an loads in parrallel.

 

Did you find a solution for this in the meantime?

 

Thank you,

Andreas

Hi there

If you have a table that is referenced in Power Query it will have to load the data into the table in order to be able to load the data into a subsequent table.

That could possibly be why you are seeing them loading at the same time?




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

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ ,

 

thanks for your answer. I am not talking about the parallel processes shown in Power BI Desktop, i am talking about parallel processses running in the backend source system (in my case SAP BW).

When I am loading data from SAP BW to Power BI Deskop with unchecked parallel data loading it works as expected and there is only one process running in SAP BW.

If I am refreshing the same Power BI Applictation from Power BI Report Server there a several parallel processes in SAP BW running.

 

So I am wondering why my settings reagaring parallel loading are ingnored by the Power BI Report Server?!

 

Maybe someone has an idea...

 

BR & Thank you!

Andreas

Hi Guavaq,

 

Very helpful. but in excel 2016, the below option of "Parallel loading of tables" option is removed.  And process requires, the refresh of tables need to be in parallel. Else it take more than 5 times it was taking me earlier.  Is there any option of enable the option.  Thanks you in advance.

 

Regards

Deepak

 

 

Hi there, I know that there is that option in Power BI Desktop, not sure if there

There is the option in Excel also once you open the Power Query Editor.




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

Proud to be a Super User!







Power BI Blog

Hi Guavaq, 

 

The post on enabling/disabling parallel data load was very useful.  But in Excel 2016, the below option is unavailable and due to that my process is taking 4 times higher than earlier.  My process requires enabling the "Parallel loading of tables".

 

 

Hi @GilbertQ

 

Does this also mean when I publish the report onto the PowerBI server and schedule the refresh, the server will also do the same thing -- refreshing the query one by one?

 

Thanks

Hi @martinhyy

 

I am not 100% sure, it might possibly keep what ever settings you have in place when refreshing the data.





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

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ

 

Just tested. Seems to be working somehow, but I'm not sure how the data is refreshed in the backend.

 

Thanks

Hi @GilbertQ 

Is there a way to do this in 'EXCEL' ? I could not find the 'Enable parallel loading of tables' in Excel ? 
I have certain number of tables which I want them to load in a particular order.

Example: I have two tables:

Table 1
Table 2

 

Now, I want the Table 2 to be loaded only after Table 1 is loaded completely (i.e it should not refreshed parallely)

Any help or workaround for this in 'EXCEL' would be appreciated.

Thank you !

Anonymous
Not applicable

Thank you Guavaq.  It makes it and no crash any more.

v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

Current power bi seems not support refresh one by one. I'd like to suggest you can post your requirement to ideas to help improve power bi.
Perhaps you can vote below ideas which has the similar requirement:

Refresh priorities

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

I have multiple queries.

When pushing "refresh button", all queries are evaluated and refreshed at the same time.

It may result "crash" due to memories.

Would it not be possible to run the queries one by one, sequentially?

Obviously, i can run the queries one-by-one manually but I would like an automatic refresh button which can refresh these queries one-by-one.

Possible?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!