Forum Discussion

Begbie's avatar
Begbie
Helper I
3 years ago

Pagination with dynamic string

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

Need Help

4 Replies

    • Begbie's avatar
      Begbie
      Helper I
      3 years ago

      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