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/)
Responsive ResidentHi 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.
Helper IIIOk 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?
- ImkeF
Community Champion
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/)
- hugoberry
Follow ImkeF example. If you need more details, post your query and I'll try to point where exactly you can benefit from Table.Buffers.
As for the reason why you need to have a Buffer in place, it is almost like forcing Power Query to firstly download all of the content from SharePoint first (into the Buffer) and then perform all of the other operations.
I assum that in your case, Power Query downloads portions of the list from SharePoint and then performs some pivoting, then fetches some more data from SharePoint again, etc. This way you are wasting a lot of time on waiting, and possibly on duplicate HTTP requests to SharePoint.
Of course this explanation is worth reading if the Table.Buffer works :)