Forum

Slow to refresh and...
 
Notifications
Clear all

Slow to refresh and load

6 Posts
2 Users
0 Reactions
719 Views
 Luna
(@ruru000)
Posts: 19
Eminent Member
Topic starter
 

I am new to Power Query and must be doing something quite not right.  I have apppended 40 tables from 40 workbooks in Sharepoint by adding each one to a new connection, and end result that I want to see is 2 rows from each table x 40 table = 80 rows.

Here is my problem.  It takes 4 minutes to load just 80 rows, I must be doing something not right!  Is 40 connections making slow to refresh?

 
Posted : 05/02/2019 4:00 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Luna,

Yes, it'll be because you have 40 queries, irrespective of the amount of data in the final query.

If you don't need to preview all of the queries you can turn off Background Data; go to Query Options > Data Load > deselect 'Allow data preview to download in the background'. This should speed things up.

Mynda

 
Posted : 05/02/2019 7:23 pm
 Luna
(@ruru000)
Posts: 19
Eminent Member
Topic starter
 

I did turn off 'Allow data preview to download in the background' but still very slow.  40 queries making so slow to load - confirmed!

Thank you so much!

 
Posted : 05/02/2019 8:58 pm
(@mynda)
Posts: 4761
Member Admin
 

I suspect it's because 40 queries are referencing SharePoint at the same time. They don't refresh sequentially so either your PC or SharePoint or both are choking. You could try Table.Buffer and see if that helps. Just wrap Table.Buffer around your data source like so:

Source = Table.Buffer( Excel.CurrentWorkbook(){[Name="Table1"]}[Content] ) ,

Mynda
 
Posted : 06/02/2019 12:48 am
 Luna
(@ruru000)
Posts: 19
Eminent Member
Topic starter
 

I tried Table.Buffer and now refresh time is 40 sec! Thank you so much for your advice, really REALLY helped!  I think that I will register for the upcoming course, cant wait! Thanks again!

 
Posted : 06/02/2019 11:45 pm
(@mynda)
Posts: 4761
Member Admin
 

You're welcome, Luna. It's great to know I could help. I look forward to seeing you in my course 🙂

 
Posted : 07/02/2019 9:02 pm
Share: