Forum Discussion
Helper IIISlow refresh of SharePoint list data
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/)
Helper IIIEdit: 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
- Matthias93
I was thinking about another possibility, but I don't know if it will work. Could a just get my sharepoint list with raw data, then reference that query and then do all of my query steps in that referenced query and not enable this referenced query in the refresh?
