Re: Apply Query Hangs

JillHenninger · ‎12-21-2020

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 · ‎12-21-2020

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

JillHenninger · ‎12-21-2020

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."