Pagination with dynamic string

[Begbie]

Hi All,

Got the usual problem of pulling data from an API with pagnation, but with a slight twist. To help set things, the API call being made is:

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

Which in itself is fine, albeit it only returns 64,000 rows, but there is another 34,000 to fetch which is where the pagnation comes in. The endpoint then changes to have &token=xxxxxxxx on the end and this is a random token is generated with the following JSON:

    },
    "offset": 0,
    "pagination": {
        "next": "21651bd5"
    }

So the endpoint for the next set of records would be:

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=21651bd5

Most articles that I've read are all assuming a number and to build a numbered list out, but I cannot do this due to the "next" being a random token each time.

Not sure if it is relevant, but when you reach the end of the pagnation, the JSON changes the pagination to be previous field:

    },
    "offset": 0,
    "pagination": {
        "previous": "460963e4"
    }

So if a list is being built out, we obviously don't want to circle back onto the same data again.

Any helps or pointers would be greatly appreciated 😊

Thank you

[ppm1]

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

[Begbie]

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

[MattKav]

@Begbie did you get this resolved in the end..?

[Begbie]

Hi Matt.

No, I never got this to work. Sorry