@mahoneypat eres increíble!
He intentado responder y pedir orientación sólo para tener mi publicación marcada como spam. Pero finalmente se me ocurrió una solución. ¿Estás de acuerdo con esto? Citar o corregirlo, entonces marcaré marcar su respuesta como una solución para que otras personas puedan obtener toda la imagen 🙂
La consulta en la que terminé que parece estar funcionando (valores RED reemplazados):
- Los valores de Column1 se crean con: List.Generate(() á> 0, each _ < 120000, each _ + 5000)
- SPItems: Json.Document(Web.Contents("https://TennantShortName.sharepoint.com/sites/NombreSitio/_api/web/lists(guid'ListGUID')/items?$skipToken,Paged,TRUE%26p_ID"&Text.From([Columna1])&"&$top 5000", [Encabezados[Aceptar?"application/json"]])))
Consulta comentada completa:
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"
