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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors