Power BI : Sharepoint List Slow Refresh

[Anonymous]

Hi All, I have Sharepoint List as my source for which I am using direct connector provided by Power BI. Data is small in size but it is taking approax 2-3 hours to refresh which is absolutely not acceptable for my business people. They need it at 30 mins frequency.

After doing some research, I realized this is quite common problem everyone has faced over the years but didn't come across any concrete solution for it. My main pain point area is this one table which is around 70 MB in size (Growing every day) which alone takes 2-3 hours to get refreshed. 
I can try below solution but this seems specific to Power BI Desktop and might not work when I deploy to the Service.

https://community.powerbi.com/t5/Desktop/Slow-refresh-of-SharePoint-list-data/td-p/134425

Can someone please help me what can I do to have faster refresh at least for this one table. There is 1 Expand operation also in this table which I need, probably that's the reason for slower refresh but I don't have any workaround for it.

Does Table.Buffer help faster refresh in the Service as well ?

Please help @GilbertQ  @ImkeF 

[mahoneypat · Accepted solution]

@Anonymous  Please see this post with instructions on how to get data from a SharePoint list much faster.

https://community.powerbi.com/t5/Desktop/SharePoint-list-query-alternative-or-optimization/m-p/1092677

Regards,

Pat

[dfwilcox]

Pat, your video and code template are changing my life! Thank you for helping me out of the slow refresh rut I was in. I found a typo in your Query Version you may want to fix. In the third fieldselect line, $select=Id should have spaces before/after the =, like so: fieldselect = "&$top=5000&$select = Id...

Thanks again for sharing your knowledge! -Dan

[lbendlin]

- Consider alternatives like using a SQL server table instead of the Sharepoint list (or as a storage for historical data from the list)

- drastically cut down on the number of columns in your list, and eliminate all unnecessary lookup columns. Do NOT do the expand in the query, instead fetch the other list too and combine them in the data model.

- use ODATA queries and/or the Graph API

- wait for Microsof to come up with performance improvements for the connector

[ImkeF]

Hi @lbendlin ,

have you ever managed to use ODATA with Organizational Accounts-Auth agains SP online sources?
I'm getting auth-errors (401), although this method works fine for the native SP-connectors.

[lbendlin]

@ImkeF I think so, I'll ask my ODATA specialist.

Edit:  confirmed.  The specialist also conveyed: "two things to try for troubleshooting... one is Fiddler and the other is to connect with a web browser instead of with PBI"

[ImkeF]

Thanks @lbendlin ,

got the error message from Fiddler, actually.

Task is to connect from Power BI Desktop as the development tool.

Makes no sense to me to play with any direct URL queries, tbh.