Paginate API POST Response

[yuvapraveen]

I am trying to accumulate API response from an URL. Initial response contains three JSON objects 1. events-containing the list that I wanted to accumulate and create a dataset 2. next_page- containing json object that needs to be passed for the subsequent JSON request 3. total_count - containing total events. I am using the code given below and getting an Expression.Error in the line highlighed. It is not able to identify the initial value I have set in list.generate and throwing this error. How do I set the initial value and subsequently change the value based on the response from the previous request. Can you please help and suggest if there are any other approach for this challenge

//Working Code
let
// Create a camoflouged secret from POSTMAN and plug in here
AuthKey = "Basic <your-token>",
EntitiesPerPage = 100,
FirstTime = 0,
Url = "https://adb-xxxxxxxx.3.azuredatabricks.net/api/2.0/clusters/events",
Offset_init = "{
""cluster_id"":""xxxxxxxxxx"",
""event_types"": [""STARTING"",""TERMINATING"",""CREATING""],
""limit"":" & Text.From(EntitiesPerPage) & "}",
GetJson = (FirstTime, Offset_passed) =>
let
offset_set = if FirstTime = 0 then Offset_init else Offset_passed,
RawData = Web.Contents(Url,[
Headers = [#"Authorization"=AuthKey ,
#"Content-Type"="application/json"],
Content = Text.ToBinary(offset_set), Timeout=#duration(0,2,0,0)
]
),
Json = Json.Document(RawData)
in Json,

GetOffset = (Offset_out) =>
let
set_Offset = if FirstTime = 0 then Offset_init else Offset_out,
Json = GetJson(FirstTime, set_Offset),
Offset_new = Json[#"next_page"],
FirstTime = FirstTime+1
//Offset_res = [now_Offset = Offset_new]
in
Offset_new,

GetEntities = () =>
let Url = Url,
FirstTime = 0,
Json = GetJson(FirstTime,Url),
EntityCount = Json[#"total_count"]
in
EntityCount,

GetFirstPage = () =>
let Url = Url,
Json = GetJson(Url),
events = Json[#"events"]
in
events,

GetPage = (Index, pg_Offset) =>
let
Offset_pg = if Index = 0 then Offset_init else pg_Offset,
Json = GetJson(Index, Offset_pg),
Value = Json[#"events"],
res = [now_value = Value]
in
res,

EntityCount = GetEntities(),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
offsets = List.Generate(
() => [counter=0, Offset_curr = Offset_init],
each [counter] < PageCount,
each [counter=[counter]+1,
Offset_Curr = {GetOffset(Offset_curr)}],
each Offset_curr),
Pages = List.Generate(
() => [i=0, res_now = GetPage(i, offsets{i})],
each [i] < PageCount,
each [i=[i]+1, res_now = GetPage(i, offsets{i})],
each [now_value])
in
Pages

[mahoneypat]

Hard to suggest a change from the code alone (no hands on trial/error).  Does this API have a count method to just get the total count directly (e.g., /$count), so you can use that value in an approach like the video (just using offset and count, instead of pagination)?

Regards,

Pat

[mahoneypat]

Since you can pass an offset in your web call, please see if the approach demonstrated in this video, using List.Numbers, would be simpler.

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

Regards,

Pat

[yuvapraveen]

@mahoneypat thanks for pointing to the link. The API server I am working is slightly different 1. It expects the query parameters in the content (like I mentioned in my code). 2. The subsequent call needs an extra parameter end_time that is within next_page (I am passing the entire json object in next_page for my next calls). Do you have code snippet for such scenario please?

[yuvapraveen]

I have also tried the below snippet, however the result only gives the content from the first page. There are in total 217 responses however I only get response for the first 100.

let 
    // Create a camoflouged secret from POSTMAN and plug in here
    AuthKey = "Basic <token-here",
    EntitiesPerPage = 100,
    Url = "https://adb-xxxxxx.3.azuredatabricks.net/api/2.0/clusters/events",
    Offset1 = "{
    ""cluster_id"":""xxxxx"",
    ""event_types"": [""STARTING"",""TERMINATING"",""CREATING""],
    ""limit"":" & Text.From(EntitiesPerPage) & "}",
    GetJson = (Offset_passed) =>
        let
            offset_set =  Offset_passed,
            RawData = Web.Contents(Url,[
                                   Headers = [#"Authorization"=AuthKey ,
                                              #"Content-Type"="application/json"],
                                   Content = Text.ToBinary(offset_set), Timeout=#duration(0,2,0,0)
            ]
            ),
            Json    = Json.Document(RawData)
        in  Json,       
 
    GetEntities = () =>
        let ent_Offset = Offset1,
            Json  = GetJson(ent_Offset),
            EntityCount = Json[#"total_count"]
        in  
            EntityCount,

    FnGetOnePage =
        (cur_Offset) as record =>
        let
            Json  = GetJson(cur_Offset),
            data = try Json[#"events"] otherwise null,
            next = try Json[#"next_page"] otherwise null,
            res = [Data=data, Next=next]
        in
            res,
 
    EntityCount = GetEntities(),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    GeneratedList = List.Generate(
        ()=>[i=0, res = FnGetOnePage(Offset1)],
        each [i]<PageCount and [res][Data]<>null,
        each [i=[i]+1, res = FnGetOnePage([res][Next])],
        each [res][Data]),
    Entities = List.Union(GeneratedList)
in
    Entities