Paginate through API call in M

[dkpcr5]

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!

[mahoneypat · Accepted solution]

Please see this video for a good way to do this.

Power BI - Tales From The Front - REST APIs - YouTube

Pat

[dkpcr5]

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]

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

[dkpcr5]

The query from the Advanced Editor has evolved a bit since asking the original question. Here's the newest version, with keys removed. (Notice the inclusion of $top=50 in the OData call, because otherwise, the call times out.)

let
    Source = List.Numbers(0, Count/50, 50),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents(
"https://endpointURL?$filter=Type%20eq%20%27Base%27%20and%20Date%20eq%20%272021-09-26%27%20and%20Value%20ne%200&$top=50&limit=50&offset=" &[Column1],
[Headers=
        [   
            #"Key" = "abc",
            #"id" = "def",
            #"Content-Type" = "application/json",
            #"key2" = Text.Combine({key2parameter}),
            #"response-cache" = "false",
            ]]
        )
))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Custom"}, {"Column1.1", "Custom.1"})
in
    #"Expanded Custom"

I'm also attaching a screenshot of my attempt at using your limit = + offset = method for paginating API calls. From the image, you can see what I'm returning. It looks like, rather than getting 50 records for Column1 = 0, another 50 records for Column1 = 1, etc. as you do in your video (at the 16:55 timestamp), it looks like I'm trying to get all available records (~15k) once within each Column1 (so, 15k records returned for Column1 = 0, another 15k records returned for Column1 = 1, etc.). Any thoughts on where I'm going wrong with the limit = & offset = method?

[dkpcr5]

Found it!!! I was doing "Table.AddColumn" twice. (Somehow did this when adding the custom column.)

Now that that's resolved, it works! Pat, you're the man - thank you so much!!

[dkpcr5]

@mahoneypat I may have spoken too quickly here... I got the API to return results, but including that $top=50 parameter in the call just meant I get the top 50 results over and over again. The call now looks like this:

https://endpointURL?$filter=Type eq 'Base' &Date='2021-09-26'&$top=50&limit=50&offset=" &[Column1]

But, if I remove the $top=50, then the API returns a 500 error (even with the limit & offset functions included).

Thoughts on what I might be doing wrong? (If I get a 500 error in my browser or Postman, I can get a log ID response that our development team can use internally to track a more precise error message. Is there any way to retrieve a response ID from within Power BI when it throws a 500 error?)

[dkpcr5]

Figured it out. User error; needed to switch limit / offset to top / skip.

Thanks again! Cheers!