Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Begbie
Helper I
Helper I

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

2 REPLIES 2
ppm1
Solution Sage
Solution Sage

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

Microsoft Employee

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors