Forum Discussion
Solved
Problems 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 try this
let
GetSharePointData = (StartRow as number) =>
let
Source = Json.Document(
Web.Contents("https://nomesito.sharepoint.com/_api/search/query?",
[
Query = [
querytext = "(IsDocument:1)",
trimduplicates = "false",
rowlimit = "50000",
startrow = Text.From(StartRow) // Usare il parametro StartRow qui
]
])),
Results = Source[PrimaryQueryResult][RelevantResults][TableRows] // Estrarre i dati
in
Results
in
GetSharePointData
and
let
PageSize = 50000, // 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)), // Ottieni tutte le pagine
AllResults = Table.Combine(Pages) // Combina tutte le pagine in un'unica tabella
in
AllResults
But don't work
Can you help me ?
Thanks
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
24 Replies
You don't seem to specify a site URL. Do you want to fetch all documents from all your SharePoint sites?
No need to specify the question mark at the end of the URL. But you will want to use the RelativePath parameter.
Personally I would take the page size down quite a bit, for example to 1000.
. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1
