cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors