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.
Solved! Go to Solution.
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?
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"
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.
I wrote a blog post about that a while back
Let me know how you fare!
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.
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.