Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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
Solved! Go to 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]
)
)
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.
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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.