Forum Discussion
Issue with List.Generate
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.
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 | LinkedIn
If 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.Hi Anonymous so I shouldn't use $skip or $skiptoken?
Any chance you can give me an example of what I should be using?
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.