Forum Discussion
yurop85
Helper I
1 year agoProblems paging sharepoint rest api
Hello! I'm a several problem with the pagination and the rest api sharepoint on power bi. The single call under 50000 row work, but if I create a script according to pagination don't work. I ...
- 1 year ago
GetSharePointData:
(StartRow as number, rl as number) => let Source = Json.Document(Web.Contents("https://companyz.sharepoint.com/_api/search/query", [Headers=[Accept="application/json", #"Content-Type"="application/json"], Query=[ querytext = "'(IsDocument:1)'", rowlimit = Text.From(rl), startrow = Text.From(StartRow) // Usare il parametro StartRow qui ]])), Rows = Table.FromRecords(Source[PrimaryQueryResult][RelevantResults][Table][Rows]), #"Added Custom" = Table.AddColumn(Rows, "Custom", each let #"Removed Columns" = Table.RemoveColumns(Table.FromRecords([Cells]),{"ValueType"}) in Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Key]), "Key", "Value")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Cells"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", Table.ColumnNames(#"Removed Columns"[Custom]{0})) in #"Expanded Custom"Query:
let PageSize = 1000, // Limite di risultati per ogni pagina MaxPages = 3, // Ad esempio, otteniamo 3 pagine (cambialo secondo necessità) Pages = List.Transform({0..MaxPages-1}, each GetSharePointData(_ * PageSize,PageSize)), // Ottieni tutte le pagine AllResults = Table.Combine(Pages) // Combina tutte le pagine in un'unica tabella in AllResults