Apply Query Hangs

[JillHenninger]

Hi all. I have a PBIX that connects to a SharePoint list. It refreshes every morning at 1am and takes roughly 15 minutes. I now need to expand an additional column - and I cannot get it to apply changes in my query. The List is rather large, but I've removed as many columns as possible. I've tried it all hours of the day (thinking it could be a server load issue) - no luck. Am I missing something? Is the only way to reduce the list size?

Here are my steps - I've tried to remove what I can to reduce steps

Source - this took a ton of experimenting and seems like the only way to connect.

[mahoneypat]

Please see this post for another way to get SharePoint List data that is very fast.

SharePoint list query alternative or optimization - Microsoft Power BI Community

Regards,

Pat

[JillHenninger]

Thank you for the idea! I tried it but I'm guessing I'm doing something wrong:

#"Added Custom" = Table.AddColumn(#"Converted to Table", "SPItems", each Json.Document(Web.Contents("https://XXXX.sharepoint.com/sites/XXX/_api/web/lists(guid'{XXXX}')/items?$skipToken=Paged=TRUE%26p_ID="&Text.From([Column1])&"&$top=5000", [Headers=[Accept="application/json"]]))),

I'm sure it's something obvious but - it's not coming to me. My list is well over 5K. Oh, and the error it throws is "Expression.Error: Access to the resource is forbidden."