Forum Discussion
SharePoint list query alternative or optimization
hjaf FYI that I finally made a video to describe this approach, and am adding it here for others that may find this post. It also gets the count of items and makes the right number of API calls.
Get SharePoint List Data with Power BI ... Fast - YouTube
Also, a reminder to mark one of these as the solution.
Regards,
Pat
Sharepoint List data sources can be slow. Fortunately, there is a much faster way. Try a blank query with this formula as your Source.
= Json.Document(Web.Contents("https://<YourTenantName>.sharepoint.com/sites/<YourSiteName>/_api/web/lists/GetByTitle('<YourListName>')/items?$top=5000", [Headers=[Accept="application/json"]]))
Replace all the parts in < >, including the < >. You will get a JSON response.
There is another version that does pagination if your list is >5000 items. Please let me know if you need that one.
If this solution works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
mahoneypat Awesome!
yes, I most definately need to use pagination 🙂 near 20k items in the lists 🙂Replace after the ? with the following
?$skipToken=Paged=TRUE%26p_ID=30&$top=5000", [Headers=[Accept="application/json"]]))
I would make a list with = {0, 5000, 10000, 15000, 20000} or something more dynamic for when the list gets bigger. Convert that to a table and add a custom column that concatenates the list value in place of the 30 in red text above. Then expand the table to get all your data.
Sharepoint Lists can be slow. This approach has saved much refresh time.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
mahoneypat you are awesome!
I have tried replying and asking for guidance only to have my post marked as spam. But I eventually came up with a solution. Do you concur with this? Quote or correct it, then I'll mark mark your reply as a solution so that other people may get the whole picture 🙂
The query i ended up at that seems to be working(replaced RED values):- Column1 values is created with: List.Generate(() => 0, each _ < 120000, each _ + 5000)
- SPItems: Json.Document(Web.Contents("https://TennantShortName.sharepoint.com/sites/SiteName/_api/web/lists(guid'ListGUID')/items?$skipToken=Paged=TRUE%26p_ID="&Text.From([Column1])&"&$top=5000", [Headers=[Accept="application/json"]])))
Complete commented query:let Source = List.Generate(() => 0, each _ < 120000, each _ + 5000), // Generate a list that increments 5000 up to max value 120 000 #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), // Convert the list into a table #"Added Custom" = Table.AddColumn(#"Converted to Table", "SPItems", each Json.Document(Web.Contents("https://<TennantShortName>.sharepoint.com/sites/<SiteName>/_api/web/lists(guid'<ListGUID>')/items?$skipToken=Paged=TRUE%26p_ID="&Text.From([Column1])&"&$top=5000", [Headers=[Accept="application/json"]]))), //custom column that does the actual query to sharepoint //Note: replace <TennantShortName>, <SiteName> and <ListGUID> //Instead of using list guid, you can use list names with GetByTitle(): "https://<TennantShortName>.sharepoint.com/sites/<SiteName>/_api/Web/Lists/GetByTitle('<List Title>')/items?$skipToken=Paged=TRUE%26p_ID="&Text.From([Column1])&"&$top=5000" #"Expanded SPItems" = Table.ExpandRecordColumn(#"Added Custom", "SPItems", {"odata.metadata", "odata.nextLink", "value"}, {"odata.metadata", "odata.nextLink", "value"}), //expand the results #"Removed Duplicates1" = Table.Distinct(#"Expanded SPItems", {"odata.nextLink"}), // Remove the duplicated nextLink items to get unique items #"Expanded value" = Table.ExpandListColumn(#"Removed Duplicates1", "value"), // Expand the results from queries into new rows #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"Id", "Title"}), // Expand wanted columns in the sharepoint list #"Removed Columns" = Table.RemoveColumns(#"Expanded value1",{"Column1", "odata.metadata", "odata.nextLink"}), // Remove columns from the initial query. #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Id", Int64.Type}, {"Title", type text}}) // Type setting in #"Changed Type"
