Forum Discussion
Issue with List.Generate
Hi Guys,
Got the top part of the code via CoPilot.
It works fine on SharePoint Lists with less than 5000 records but as soon as I try it on a list with more that 5000 records it gives an error.
Can someone help fix the code and help me understand what was wrong?
Code
let
Source = SharePoint.Tables("https://clarkcontracts.sharepoint.com/sites/CE4080-PitfodelsWoodCareHomeAberdeen", [Implementation="2.0"]),
List = Source{[Title="Site Sign In"]}[Items],
PageSize = 5000,
GetPage = (PageNumber as number) =>
let
Skip = PageNumber * PageSize,
Page = OData.Feed("https://clarkcontracts.sharepoint.com/sites/CE4080-PitfodelsWoodCareHomeAberdeen/_api/web/lists/getbytitle('Site Sign In')/items?$top=" & Number.ToText(PageSize) & "&$skip=" & Number.ToText(Skip))
in
Page,
Pages = List.Generate(() => 0, each _ < 10, each _ + 1, each GetPage(_)),
CombinedPages = Table.Combine(Pages),
#"Trimmed Text1" = Table.TransformColumns(CombinedPages,{{"Title", Text.Trim, type text}, {"Company", Text.Trim, type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Trimmed Text1",{{"Company", Text.Proper, type text}, {"Title", Text.Proper, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Capitalized Each Word",null,"CE4080",Replacer.ReplaceValue,{"Contract"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Modified", type datetime}, {"Created", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Title", "Name"}, {"SignInLocation", "Sign In Location"}, {"SignOutLocation", "Sign Out Location"}, {"PostcodeStart", "Postcode Start"}, {"ProjectPostcode", "Project Postcode"}, {"VehicleRegistration", "Vehicle Registration"}, {"SignInDateandTime", "Sign In Date and Time"}, {"SignOutDateandTime", "Sign Out Date and Time"}, {"ModeofTransport", "Mode of Transport"}, {"AreyouaDriver/Passenger", "Are you a Driver or Passenger"}, {"Distance(includingreturnjo", "Distance including return journey"}}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns","Ccl","Clark Contracts",Replacer.ReplaceValue,{"Company"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Clark Contracts Ltd","Clark Contracts",Replacer.ReplaceValue,{"Company"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Clark Cclcontracts","Clark Contracts",Replacer.ReplaceValue,{"Company"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value3","mid size car","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Car","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Crane ","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Passenger","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","Car ","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","Drive","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","Van ","Van",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12"," Van ","Van",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","Crane","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","Vn","Van",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value15",{{"Created", Order.Descending}})
in
Error
DataSource.Error: OData: Request failed: The remote server returned an error: (400) Bad Request. (The $skip and $skiptoken cannot be specified at the same time.)
Details:
DataSourceKind=OData
DataSourcePath=https://clarkcontracts.sharepoint.com/sites/CE4080-PitfodelsWoodCareHomeAberdeen/_api/web/lists/getbytitle(%27Site%20Sign%20In%27)/items
sprequestguid=cf04a0a1-40a9-c000-7e49-b78fbea9880b
Url=https://clarkcontracts.sharepoint.com/sites/CE4080-PitfodelsWoodCareHomeAberdeen/_api/web/lists/getbytitle('Site Sign In')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d4017&%24top=4000&%24skip=0
Cheers
Hi jbrines ,
Thank you for the followup.I would be happy to assist you!
when working with large SharePoint lists, it is best not to use $skip or $skiptoken manually. The recommended way is to use SharePoint.Tables, which automatically manages paging and avoids the 5,000-item threshold issues. You may find this approach in Microsoft’s documentation on SharePoint.Tables, and more about handling large lists here.
Hope this helps.If so,consider accepting it as solution.
5 Replies
Hello jbrines
try this code
let
CombinedPages = OData.Feed(
"https://clarkcontracts.sharepoint.com/sites/CE4080-PitfodelsWoodCareHomeAberdeen/_api/web/lists/getbytitle('Site Sign In')/items",
null,
[Implementation = "2.0"]
),
#"Trimmed Text1" = Table.TransformColumns(CombinedPages,{{"Title", Text.Trim, type text}, {"Company", Text.Trim, type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Trimmed Text1",{{"Company", Text.Proper, type text}, {"Title", Text.Proper, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Capitalized Each Word",null,"CE4080",Replacer.ReplaceValue,{"Contract"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Modified", type datetime}, {"Created", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{
{"Title", "Name"},
{"SignInLocation", "Sign In Location"},
{"SignOutLocation", "Sign Out Location"},
{"PostcodeStart", "Postcode Start"},
{"ProjectPostcode", "Project Postcode"},
{"VehicleRegistration", "Vehicle Registration"},
{"SignInDateandTime", "Sign In Date and Time"},
{"SignOutDateandTime", "Sign Out Date and Time"},
{"ModeofTransport", "Mode of Transport"},
{"AreyouaDriver/Passenger", "Are you a Driver or Passenger"},
{"Distance(includingreturnjo", "Distance including return journey"}
}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns","Ccl","Clark Contracts",Replacer.ReplaceValue,{"Company"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Clark Contracts Ltd","Clark Contracts",Replacer.ReplaceValue,{"Company"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Clark Cclcontracts","Clark Contracts",Replacer.ReplaceValue,{"Company"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value3","mid size car","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Car","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Crane ","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Passenger","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","Car ","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","Drive","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","Van ","Van",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12"," Van ","Van",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","Crane","Medium Petrol Car",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","Vn","Van",Replacer.ReplaceValue,{"Mode of Transport"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value15",{{"Created", Order.Descending}})
in
#"Sorted Rows"
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
No didn't work but came back with a different error.
DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed: OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (The expression "web/lists/getb... Sign In')/items" is not valid.) OData Version: 4, Error: The remote server returned an error: (400) Bad Request. (The expression "web/lists/getb... Sign In')/items" is not valid.) OData Version: 3, Error: The remote server returned an error: (400) Bad Request. (The expression "web/lists/getb... Sign In')/items" is not valid.) Details: DataSourceKind=OData DataSourcePath=https://clarkcontracts.sharepoint.com/sites/CE4080-PitfodelsWoodCareHomeAberdeen/_api/web/lists/getb...%20Sign%20In')/items sprequestguid=cd3ea0a1-106f-c000-7e49-bc7694a5b8a5, cd3ea0a1-e072-c000-7e49-b70f581d6b38, cd3ea0a1-8076-c000-d89c-0cb46ef6a1feHi jbrines ,
Thank you for the helpful response pankajnamekar25 !When working with SharePoint lists that exceed 5000 items, list thresholds may introduce complexity—especially when using $skip and $skiptoken.
To better understand the limitations and recommended approaches, here are some helpful Microsoft documentation links:
https://learn.microsoft.com/en-us/power-query/connectors/odata-feed
https://learn.microsoft.com/en-us/sharepoint/dev/solution-guidance/modern-experience-site-classification#handling-large-lists-and-libraries
https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest
Hope this helps.If so,give us kudoa and consider accepting it as solution.
Regards,
Pallavi.
