Problems paging sharepoint rest api

[yurop85]

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

[lbendlin]

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

[yurop85]

I try to read the link, but I don't uderstand..

[yurop85]

= (StartRow as number) =>
    let
        // URL di base del sito SharePoint
        SiteUrl = "https://sito.sharepoint.com",
        
        // Parametro che definisce il percorso relativo per la query
        RelativePath = "_api/search/query",

        // Esegue la richiesta API usando il percorso relativo
        Source = Json.Document(
            Web.Contents(
                SiteUrl,
                [
                    RelativePath = RelativePath,  // Usa il parametro RelativePath qui
                    Query = [
                        querytext = "'(IsDocument:1)'",
                        trimduplicates = "false",
                        rowlimit = "50000",
                        startrow = Text.From(StartRow)  // Usa il parametro StartRow dinamico
                    ]
                ]
            )
        ),

        // Estrazione dei risultati dalla risposta JSON
        Results = Source[PrimaryQueryResult][RelevantResults][TableRows] 
    in
        Results

Not work because

An unexpected character was detected in the JSON input. Details:

Value=<
Position=0

[lbendlin]

Fair enough. Sometimes RelativePath misbehaves.

Anyway, I think your search URL is not entirely correct.

SharePoint Search REST API overview | Microsoft Learn

Pagination for large result sets | Microsoft Learn

[yurop85]

I try single call with odata import work. 
The script in M no.

Single call

= OData.Feed(TenantUrlRoot & "_api/search/query?querytext='(IsDocument:1)'&trimduplicates=false&rowlimit=50000&startrow=0", null, [Implementation="2.0"])

[lbendlin]

Don't use Json.Document. Use Xml.Tables

[yurop85]

not work

[lbendlin]

works for me

let
    Source = Json.Document(Web.Contents("https://company.sharepoint.com/_api/search/query", [Headers=[Accept="application/json", #"Content-Type"="application/json"], Query=[
                    querytext = "'sharepoint'",
                    rowlimit = Text.From(100),
                    startrow = Text.From(0)   // Usare il parametro StartRow qui
                ]])),
    PrimaryQueryResult = Source[PrimaryQueryResult],
    RelevantResults = PrimaryQueryResult[RelevantResults],
    Table = RelevantResults[Table],
    Rows = Table[Rows],
    #"Converted to Table" = Table.FromList(Rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Column1 = #"Converted to Table"{0}[Column1],
    Cells = Table.FromRecords(Column1[Cells])
in
    Cells

[yurop85]

This is one row with 58 field. 
I've need all filed

[lbendlin]

it's just an example to show that the query works and returns Json (thanks @Expiscornovus ).  You can do the unpivoting and paging yourself.

[yurop85]

ok, the table get this

= Json.Document(Web.Contents("https://sito.sharepoint.com/_api/search/query", [Headers=[Accept="application/json", #"Content-Type"="application/json"], Query=[
querytext = "'sharepoint'",
rowlimit = Text.From(50000),
startrow = Text.From(0) // Usare il parametro StartRow qui
]]))

but I'don't undestand the pagination

[lbendlin · Accepted solution]

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

[yurop85]

Thanksssss!

[yurop85]

but I've some problems with the pagination.

for example

How intercept this exception ?
I don't get all the file in a tenant with this script

[lbendlin]

Either choose "Remove Errors"  as a transform or use "try ... otherwise ..."

[yurop85]

Works but with this call api rest I haven't all files in share point tenant.. why ?
I must add parameter in the call ?

[lbendlin]

Are you a tenant admin? 

[yurop85]

yes

[lbendlin]

how do you know you are not seeing all the files in the tenant?  How many files do you have?  (I know in our tenant we have hundreds of millions of files)

[yurop85]

Exists a microsoft dashboard the use the feed report.office.com that show the numer of file in the tenant.
This number not equas with the mine, also i play with this parameters and result is not the same

let
    PageSize = 1000,  // Limite di risultati per ogni pagina
    MaxPages = 3,      // Ad esempio, otteniamo 3 pagine (cambialo secondo necessità)