Data Refresh Time - Share Point

[EbyEaso]

Hello Everyone,

I have a problem here. I'm new to the community and still learning Power BI

I have share point list connected to my PBI. Rerport works fine and all Visualization is good. But the problem is while refreshing it takes long time. On avarage 40 to 50 minutes.

I have connected two share point online list. One list is data input by users and my team verify the data and approves it then directly goes to another list whcih is archive. The archive folder now has arround 7000 items. That is 7000 rows. I downloaded the list from share point and verified the sixe and it shows only 1047KB. But while refreshing the data source in Power BI it shows around 45 MB. I filtered out unwanted column when transform.

Can anyone help me out here.

Thank You,.

[lbendlin]

Sadly that is expected behavior with sharepoint lists, especially when you have dozens of columns (and expecially lots of lookup columns).  

General rule of thumb is to keep the list as narrow as you can, avoid lookup columns where possible (combine the tables in Power BI instead) and to keep row count below 5000.

Hopefully one of the upcoming Power BI versions will improve the performance of the list connector.

[EbyEaso]

Thnak You @lbendlin  for the info and update. Appreciate it.

So with that said currently there is no solutiuon for this type of issue? Even if I select only needed columns when transforming data, Power BI will go through all column availbale from list while refreshing?

[lbendlin]

Not just that, it will also traverse all lookup column relationships.

[EbyEaso]

ok, Thank You @lbendlin . Apprecaite it.

[EbyEaso]

Does Incremental Refresh help in this situation? For share Point online list.

[lbendlin]

That's a great question.  What would you base that on?  Created Date? Last Modified Date?

Incremental refresh is good if you have immutable data - pretty much the opposite of a sharepoint list which is supposed to be a living document with frequent changes.

What you're looking for is differential refresh. Only update stuff that has changed/been deleted, and add new items. The Last Modified Date is perfect for that.  You could envision a pseudo differential refresh where you add the items that were last modified in the last x days, and then deduplicate the IDs. The caveat here would be that you would miss out on the deletions. Meaning in your sharepoint list you should never actually delete items, only change their status to flag them as "include/exclude"

[EbyEaso]

Yes,

That would be based on the Modifed date. My data us a living data in a archive library. So there will be no changes or deletion will be happen to the items. 

So will the Power BI support for incremental refresh for the data from Share Point? While I was doinfg research on this I red something related to "View Native Query".  Only if this option available for the data sourse then only the Incremental refresh will work. 

Any idea?

[lbendlin]

I wrote a blog post about that a while back

https://community.powerbi.com/t5/Community-Blog/Incremental-Refresh-with-Slow-Changing-Source-Data/b...

Let me know how you fare!

[EbyEaso]

Hello @lbendlin 

Thank you for the blog, It helps me understand little more. But Does the Incremental refresh helps for share Point library? 

Also what do you say about "View Native Query" in my case it is fadded out, It is not enable.

[lbendlin]

What I am saying (also in the blog) is that query folding (view native query) is nice to have but not an absolute requirement.

[EbyEaso · Accepted solution]

Ok. Thank You @lbendlin . Appreciatte your kind assistance. So there is full cure for this issue.

Hopefully Microsoft will come with a cure for this in future updates.

Thanks again.