SharePoint list query alternative or optimization

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"

Ok, this is promising! 

I successfully got the first 5000 item in a breeze, however, the way you suggest iterating / paging the query is somewhat unclear to me.
In my case, the lowest ID starts at 5495, item # 5000 has ID around 80k. The "density" of the id range varies because of creations and deletions over time, and that sharepoint doesn't immediately re-use ID's. I also noticed that sharepoint provides an odata.nextLink value for the next 5k items, maybe I can somehow  create an iteration that continues until this property is not appearing. Right now this appears to happen at about 120 000 (even though the item list contains just around 20k items).

I can probably use a list as you suggested and have the list stop at 200 000 in 5000 increments, but not exactly sure how to make a query that iterates around the this list. can you provide an example?

PS:
I had to add "/items?" in the uri so the current uri for web.contents() in my case is as follows, could you use these uris in your answer so that when I tag the post as a solution, other people would have a better understanding 🙂

= Json.Document(Web.Contents("https://<TennantShortName>.sharepoint.com/sites/<SiteShortName>/_api/Web/Lists/GetByTitle('<List Title>')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d<StartAtId>&%24top=5000" , [Headers=[Accept="application/json"]]))

or using list guid the url looks like this :
https://<TennantShortName>.sharepoint.com/sites/<SiteShortName>/_api/web/lists(guid'<item list guid>')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d<StartAtId>&%24top=5000

mahoneypat  you are a true hero! Thank you for the video and for the comment! Worked for me like a charm 😊