cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EbyEaso
Helper I
Helper I

Data Refresh Time - Share Point

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.

 

EbyEaso_0-1602501848171.png

 

EbyEaso_1-1602502112278.png

Can anyone help me out here.

 

Thank You,.

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

View solution in original post

11 REPLIES 11
lbendlin
Super User
Super User

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.

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?

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

ok, Thank You @lbendlin . Apprecaite it.

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

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"

 

 

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?

 

EbyEaso_0-1602944292464.png

 

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!

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.

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

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.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors