Forum Discussion
Pagination with dynamic string
This video is pretty close to what you need. You will just need to check for the "next" field in your condition term. Since it is not there is the final call, you may need to use try ... otherwise to avoid an error.
(1) How to Use List Generate to Make API calls in Power Query - YouTube
Pat
Thank you for Pat. It helped steer me in the right direction, but seems I'm not quite there yet. I also found this link https://datachant.com/2016/06/27/cursor-based-pagination-power-query/ which has also helped, but seems it is still only generating 64,000 rows for me and not the other 34,000 that I still require.
I'm sure it is something silly, but this is what I have code wise:
let
url = "https://api.cloudability.com/v3/reporting/cost/run?end_date=yesterday+at+23%3A59%3A59&start_date=2022-01-01&dimensions=category1&dimensions=category4&dimensions=year_month&dimensions=category11&dimensions=enhanced_service_name&dimensions=usage_family&metrics=total_amortized_cost&limit=0&token=",
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url, [Headers = [Authorization="xxxxx"]])),
data = try Source[results] otherwise null,
next = try Source[pagination][next] otherwise null,
res = [Data=data, Next=next]
in
res,
GeneratedList =
List.Generate(
() => [i=0, res = FnGetOnePage(url)],
each [res][Data] <> null,
each [i = [i] + 1, res = FnGetOnePage([res][Next])],
each [res][Data]
)
in
GeneratedList
If anyone has any pointers as to what I am missing and solve this, I would be hugely grateful! 😁
Thanks
