Nested Generate List for Web API call

[ITSNev]

I'm trying to get a list of Records based on a specific offset, but generate from another list based on a month range.

I have a restapi connection working to pull all records and paginate the records based on a offset.

eg. Start = "2022-04-01", End = "2025-12-31", Offset = 500

This is my FnGetBookings, and produces the desired results and works perfectly.

= (offset) =>
let
    start_date=eStartOfMonth, // 2022-04-01
    end_date=MaxProjectEndDate, //2025-12-31
    records = Number.ToText(offset),
    header = [  #"Authorization"="Bearer ZZZZZZZ",
                #"Content-Type"= "application/json"],
    //content = "{""project"":{""id"":"&project_id&"}}",
    url = "https://app.XXXXX.cloud/rest/v1/",
    response = Web.Contents(url, 
        [RelativePath = "bookings?",
            Query=[
                start=start_date,
                end=end_date,
                offset = records
                ],
        Headers=header]),
    out = Json.Document(response,1252)
in
    out

 The above function is called from this power query and the results are as expected.

let
    Source = List.Generate (
    () => [offset = 500, actuals = FnGetBookings( 0 ) ] ,
    //each [actuals][total_count] <> 0,
    each not List.IsEmpty([actuals][data]),
    each [offset = [offset] + 500, actuals = FnGetBookings ([offset]) ],
    each [actuals]
)
in
    Source

What I would like to do is the same, but pass a start, end data from a table, and call the paginate function based on each month.

So I generate a month list of bookings for each motnh, which increments the month until MaxProjectEnd date is reach, but I'm not getting the results I need and I beleive its because the offset value is not starting as 0 and incrementing correctly.

= (offset as number, start_date as text, end_date as text) =>
let
    //start_date=eRSActualsStartOfMonth,
    //end_date=MaxProjectEndDate,
    records = Number.ToText(offset),
    header = [  #"Authorization"="Bearer ZZZZZZZZ",
                #"Content-Type"= "application/json"],
    //content = "{""project"":{""id"":"&project_id&"}}",
    url = "https://app.XXXXXX.cloud/rest/v1/",
    response = Web.Contents(url, 
        [RelativePath = "bookings?",
            Query=[
                start=start_date,
                end=end_date,
                offset = records
                ],
        Headers=header]),
    out = Json.Document(response,1252)
in
    out

if I pass a value of 0 of the offset, e.g. FnBookings(0, [start], [End]) I see records, but this is not paginating correctly.

I pretty sure I need ensure that pagination starts at 0 and would like a pointer on how to start a 0, but paginate based of 500 records for each month. I'm 90% there, but missing this one final piece of the puzzel.

Thanks

Neville

[Anonymous]

It is not clear to me what the problem is, but to verify that there are no problems on the API side, I would do some manual tests by calling the function with values entered by hand, ad esempio

FnBookings( 200, "2022/01/01" , "2022/04/01")

If this works, the rest should be pretty straightforward.

[Daryl-Lynch-Bzy · Accepted solution]

Hi @ITSNev - It would be nice to have a file to work with and test, but I am thinking you need to something like:

Replace

= Table.AddColumn( #"Rename Columns", "FnBookings", each FnBookings( 200, [start] , [end] ) )

 With

= Table.AddColumn( #"Rename Columns", "FnBookings", (x) =>  FnBookings( 200, [start] , [end] ) )

= Table.AddColumn( #"Rename Columns", "FnBookings", (x) =>  
     List.Generate (
        () => [offset = 500, actuals = FnBookings( offset, x[start] , x[end] ) ] ,
        each not List.IsEmpty([actuals][data]),
        each [offset = [offset] + 500, actuals = FnBookings( offset, x[start] , x[end] )  ],
        each [actuals]
     )
)

[ITSNev]

Thank you Darly, your example pointed me in the right direction to achieved what I needed.  

Thanks

Nev