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"