Forum Discussion
Helper IIISlow refresh of SharePoint list data
Hi everybody,
I have a dashboard that is getting data from multiple SharePoint lists. Some of these lists contain around 3000 to 4000 lines. When I want to refresh my dashboard it takes about 3 minutes. A list with about 3000 lines is easily around 10mb (which I find quite large). Some lists contain quite a lot of lookup columns that get information from different lists. I am using the standard 'SharePoint list' connector provided by PowerBI and my lists are coming from a on-prem SharePoint.
I find it odd that this refresh should take so long, I am doing some operations on my queries to clean op the data, but no really heavy ones such as expanding and merging. I have the impression the downoading of a 10mb list takes a lot of time even though my internet connection itself is quite fast.
Attached you can see a screenshot of my 'Data Load Settings', these should all be optimal for speedy refreshing.
Could anyone please help me understand why this is happening. I tried cleaning up my queries and removing columns I'm not using, unfortunately without much succes.
Kind regards,
Matt
Yes, pls try that.
Fetch your raw-data in one query and then reference this query. This will create a first step like so:
Source = NameOfTheQueryWithRawData
Change that step into this:
Source = Table.Buffer(NameOfTheQueryWithRawData)
Unfortunately, there's not much informations available around performance tuning at the moment (here are some additional details: https://blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/)
12 Replies
Matthias93Edit: I found that I am unpivoting 9 columns in one of my queries. Removing this query for a minute makes my refresh time 6 times faster. I have a list in sharepoint where there is a line for each person with his answer to 9 questions. One column per question with the answer. As you can see in the screenshot below. I unpivoted these column so I could properly show the data in the bar chart. I realise I am making 9 lines out of each line on SharePoint. What would be a better way to achieve this?
Regards,
Mat
- ImkeF
Community Champion
Hi Matt,
pls check if disabling "Background Data" improves performance: https://blog.crossjoin.co.uk/2016/12/05/power-query-power-bi-and-the-allow-data-preview-to-download-in-the-background-option/
If that doesn't help, try buffering the step that goes into the pivot.
- hugoberry
Responsive Resident
Hi Matthias93. I bet that if you enable the trace on your query and check your logs you'll see multiple requests going to SharePoint. I believe that is because Power Query is trying to paralelize the execution of your query and doesn't cache the data acording to your scenario. I would recommend caching the results of SharePoint list into a Table.Buffer. And then continue with data pivot/unpivot/whatnot.
- Matthias93
Ok I tried enabling the 'Data Preview' option, but it made no difference to the fresh time. I enabled tracing, but unfortunately I cannot get any wiser out of this. Say I would want to buffer my query or a step in my query, how would I go about this? I have no experience with buffer tables and do not find that much information online.
Thanks already for helping me out.
Kind regards,
Matt
Here's details of another approach that provides a direct query like refresh for SharePoint data :
