Forum Discussion

yurop85's avatar
yurop85
Helper I
1 year ago
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 ...
Need Help
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    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
lbendlin's avatar
lbendlin
Super User
1 year ago

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's avatar
    yurop85
    Helper I
    1 year ago

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

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      The documentation that explains how to use RelativePath?

    • yurop85's avatar
      yurop85
      Helper I
      1 year ago

       

      = (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