Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power BI : Sharepoint List Slow Refresh

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 

Need Help

6 Replies

    • dfwilcox's avatar
      dfwilcox
      New Member
      3 years ago

      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's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    - 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's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        5 years ago

        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"