Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Paginate through API call in M

Hi,

I'm pulling data using an API call in M Query. The record count is in the tens of thousands, but the API can only retrieve 2000 records per page. How can I retrieve all records? (If by using Table.GenerateByPage, how would I incorporate that function into the query below?)

let
Source = Json.Document(Web.Contents(
    "endpointURL&$top=2000",
    [Headers=
        [   
            #"key1" = "abc",
            #"id1" = "123",
            #"header1" = "def",
            #"key2" = Text.Combine({parameter}),
            #"response-cache" = "false",
            #"header2" = "x",
            #"header3" = "y",
            #"header4" = "z"
            ]]
        )
    ),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{0}[Value],
Custom1 = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Custom1, "Column1",
{"Column1", "Column2"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Column1",{{"Column1", Order.Ascending}})
in
    #"Sorted Rows"

Thank you!

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      That video was super helpful - thanks so much!

       

      One thing I'm still unclear on: If I parameterize the URL, what would be the best way to incorporate the API call Headers?

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee

        I'm assuming you would use the same headers for each web call.  Is that not the case?  Can you share your query from the Advanced Editor?

         

        Pat