cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matthias93
Helper III
Helper III

Slow 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

 

Capture.PNG

 

 

 

 

1 ACCEPTED SOLUTION

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/)  

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

12 REPLIES 12
Gally
New Member

Here's details of another approach that provides a direct query like refresh for SharePoint data :

 

 

Power BI direct query workaround for SharePoint

Matthias93
Helper III
Helper III

Edit: 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,

MatCapture.PNG

hugoberry
Responsive Resident
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.

  

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

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?

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/)  

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Uimari
Regular Visitor

I am very new to Power BI and am facing a similar issue and don't know where to try and use the buffer.table step.

 

I have a sharepoint site that currently contains 562 files with multiple rows of data, each week the number of files grows each week (up to 300 per week).

 

It can take up to 20min refresh data now, so I can imagine it will only get slower as the number of files increase week over week. This behavior only appears to be an issue with connecting to Sharepoint. I tested with the same dataset on One Drive and there were nowhere near the latency issues - unfortunately I have to use Sharepoint and would love to be able to speed up the refresh time.

 

I saw other blogs suggesting to uncheck "Auto Date/Time" but that didn't seem to make any impact, so I'm hoping your suggestion is what i need, I just don't know where it goes or how to add it.

 

Thank you!

My data is taking forever and a day to refresh. How can I make it faster. Data is below.

 

let
Source = SharePoint.Tables("https://activestirling793.sharepoint.com/AShub/Kit", [ApiVersion = 15]),
#"ada6df12-8ddd-48d5-ada7-56a1d0236a7c" = Source{[Id="ada6df12-8ddd-48d5-ada7-56a1d0236a7c"]}[Items],
#"Expanded Category" = Table.ExpandRecordColumn(#"ada6df12-8ddd-48d5-ada7-56a1d0236a7c", "Category", {"Title"}, {"Category.Title"}),
#"Expanded Product" = Table.ExpandRecordColumn(#"Expanded Category", "Product", {"Title"}, {"Product.Title"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Product",{{"Category.Title", "Product"}, {"Product.Title", "Size"}}),
#"Expanded lookup" = Table.ExpandRecordColumn(#"Renamed Columns", "lookup", {"Full Name", "Job Title", "Line Manager", "Status", "Team", "Location", "Request Date", "Approved", "ID"}, {"lookup.Full Name", "lookup.Job Title", "lookup.Line Manager", "lookup.Status", "lookup.Team", "lookup.Location", "lookup.Request Date", "lookup.Approved", "lookup.ID"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded lookup",{"FileSystemObjectType", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ContentTypeId", "ComplianceAssetId"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Quantity", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"lookup.Full Name", "Full Name"}, {"lookup.Job Title", "Job Title"}, {"lookup.Line Manager", "Line Manager"}, {"lookup.Status", "Status"}, {"lookup.Team", "Team"}, {"lookup.Location", "Location"}, {"lookup.Request Date", "Request Date"}, {"lookup.Approved", "Approved"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Request Date", type date}})
in
#"Changed Type1"

hugoberry
Responsive Resident
Responsive Resident

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 🙂

Hi,

 

After some more testing I find that my referenced query is not at all being updated based on the source query. I disabled my referenced query from refreshing too make the refreshing on the report faster. It does not work with or without the Table.Buffer function in front of the referenced query. 

 

Do any of you know what I can do to make this work, I'm getting quite frustrated with it.

 

Regards,

Matt

Thanks for helping guys. I pulled in the raw list data through a query and then created a reference query with the Table.Buffer added  where I do the pivots and other operations. The total refresh time went down from 3 minutes to around 18 seconds, which is quite nice.

 

Regards,

Matt

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-...

 

If that doesn't help, try buffering the step that goes into the pivot.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors