Sharepoint list taking 20+ mins to refresh only 4 rows of data with Oauth connection

[KD123]

Hi,

I am seeing Sharepoint list in gen 2 dataflows is taking 20+ mins to refresh the data. I am using Oauth connection, 2.0 connector and default fields from list. If i use REST API, I was not able to see the Oauth authentication. Also, i am using gateway connection to sharepoint list so not sure if that is reason for long time to refresh.

let

  Source = Table.Buffer(SharePoint.Tables("https://mycompany.com/sites/Reporting", [Implementation = "2.0", ViewMode = "Default"])),

  #"Navigation 1" = Source{[Id = "62b8a5ef-379b-4ff4-baa1-0c841b33cd96"]}[Items],

  #"Expanded Brand Selection" = Table.ExpandListColumn(#"Navigation 1", "Brand Selection"),

  #"Removed columns" = Table.RemoveColumns(#"Expanded Brand Selection", {"ID"})

in

  #"Removed columns"

[TomMartens]

Hey @KD123 ,

please keep in my that Sharepoint is not the fastest data source.

Nevertheless, I'm wondering if it's Sharepoint online though. I'm not an expert in regards to Sharepont sources, but the Sharepoint online url will look like this:
mycompany.sharepoint.com/...

In case we are talking about Sharepoint online, an on-premises data gateway is not required.

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[Anonymous · Accepted solution]

Hi,@KD123 .I am glad to help you.

According to your description, when you refresh the report whose data source is Sharepoint list, the refresh is very slow, it took 20 minutes to refresh four rows of data, which is quite strange, please check whether the data of your data source can be optimized or not, and if possible, optimize the data source.
At the same time, you proposed to try to use Rest API to realize the data source refresh operation, I think it is a better solution.
If you want to connect from Power BI to the REST API using OAuth2 authentication, you need to create custom
connectors.
You can find documentation on how to implement an OAuth2 flow in a custom connector here.
URL:
Develop a connector using the Power Query SDK - Power Query | Microsoft Learn
https://blog.crossjoin.co.uk/2021/08/29/connecting-to-rest-apis-with-oauth2-authentication-in-power-...
Luckily I found similar threads that had the same problem as you and have solved it, so I hope that helps!
URL:Solved: Power BI : Sharepoint List Slow Refresh - Microsoft Fabric Community

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.