Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ITSNev
Frequent Visitor

Nested Generate List for Web API call

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

 

 

 

ITSNev_0-1650418577567.png

 

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

 

 

 

ITSNev_1-1650419068073.png

 

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

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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]
     )
)

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Resident Rockstar
Resident Rockstar

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]
     )
)

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

 

Thanks

Nev

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors