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?
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
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!
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
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!
You're welcome, Luna. It's great to know I could help. I look forward to seeing you in my course 🙂