Issue with List.Generate

[jbrines]

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

[pankajnamekar25]

Hello @jbrines 

try this code

let

    CombinedPages = OData.Feed(

        "https://clarkcontracts.sharepoint.com/sites/CE4080-PitfodelsWoodCareHomeAberdeen/_api/web/lists/getb... 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.

[jbrines]

Hi @pankajnamekar25 

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-0cb46ef6a1fe

[Anonymous]

Hi @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-classifica...
https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-r...

Hope this helps.If so,give us kudoa and consider accepting it as solution.

Regards,
Pallavi.

[jbrines]

Hi @Anonymous so I shouldn't use $skip or $skiptoken?

Any chance you can give me an example of what I should be using?

[Anonymous · Accepted solution]

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.