Forum Discussion
how to create a query that paginates?
Hi all (& specifically ImkeF as he is clearly a superhero!)
I'm very new to PowerBI, a couple of weeks at best, and very new to querying APIs. I appreciate that's a recipe for trouble, but i'm a relatively quick learner, so bear with me 😉
I'm querying an Rest API (specifically GoCardless if anyone is also needing a solution) which returns results in JSON. I'm trying to get a list of items to build a dataset.
Unfortunately, a single call is limited to 500 records, and I need in the region of 50,000.
I've researched the API Documentation and it's possible to fetch the next set of 500 by passing an ID from the first response.
The initial request is just a standard URL with no parameters - something like:
Subsequent calls must include the 'after' parameter and the id so would look something like this:
https://www.blah.com/events?after=X0001
The JSON response from the first call looks like this:
{
"events": [
{
"id": "X0001",
"created_at": "2020-03-24T08:04:27.593Z",
"resource_type": "mandates",
"action": "cancelled",
"links": {
"mandate": "MD0005CS4YKG3P"
},
"details": {
"origin": "bank",
"cause": "mandate_cancelled",
"scheme": "bacs",
"reason_code": "ADDACS-1",
"description": "The mandate was cancelled at a bank branch."
},
"metadata": {}
},
],
"meta": {
"cursors": {
"before": null,
"after": "X0001"
},
"limit": 500
}
}
I've done lots of reading so my assumption is I need to write a query that does something like:
- Initial API call is as standard
- Pick after value from JSON and write to a temp table
- Additional API call adding parameter including value in table
- Loop through this until 'after' value becomes 'null'
Does that sound about right, and could someone help me? I've got a bit lost in all the comments although I think I grasp the principle.
Thanks,
Chris
- ImkeF
Community Champion
Hi Anonymous
you can check after each step whether a new should be made by checking if the "after"-value isn't null:
[Result][meta][cursors][after] <> null
For the development/testing phase , I've added another condition as well, that prevents running this query into an an infinite loop:
[Counter] < 200. You should delete that condition once the query runs as you expect it.:
let Source = " {#(cr)#(lf) ""events"": [#(cr)#(lf) {#(cr)#(lf) ""id"": ""X0001"",#(cr)#(lf) ""created_at"": ""2020-03-24T08:04:27.593Z"",#(cr)#(lf) ""resource_type"": ""mandates"",#(cr)#(lf) ""action"": ""cancelled"",#(cr)#(lf) ""links"": {#(cr)#(lf) ""mandate"": ""MD0005CS4YKG3P""#(cr)#(lf) },#(cr)#(lf) ""details"": {#(cr)#(lf) ""origin"": ""bank"",#(cr)#(lf) ""cause"": ""mandate_cancelled"",#(cr)#(lf) ""scheme"": ""bacs"",#(cr)#(lf) ""reason_code"": ""ADDACS-1"",#(cr)#(lf) ""description"": ""The mandate was cancelled at a bank branch.""#(cr)#(lf) },#(cr)#(lf) ""metadata"": {}#(cr)#(lf) },#(cr)#(lf)#(tab)],#(cr)#(lf) ""meta"": {#(cr)#(lf) ""cursors"": {#(cr)#(lf) ""before"": null,#(cr)#(lf) ""after"": ""X0001""#(cr)#(lf) },#(cr)#(lf) ""limit"": 500#(cr)#(lf) }#(cr)#(lf)}", #"Parsed JSON" = Json.Document(Source), Custom2 = List.Generate( () => [Result = #"Parsed JSON", Counter = 0], each [Result][meta][cursors][after] <> null and [Counter] < 1000, each [ Result = Json.Document(Web.Contents("https://www.blah.com/events?after=" & [NextPage])), NextPage = Result[meta][cursors][after], Counter = [Counter] + 1 ] ) in Custom2You can also check out this video to learn more about the technique: https://www.youtube.com/watch?v=vhr4w5G8bRA
Thank you very much ImkeF, your answer greatly helped me get the list of pagination tokens for Twitter API v2!
If I may, for your code to work and based on what you wrote it should look like this :
#"Parsed JSON" = Json.Document(Source), Custom2 = List.Generate( () => [Result = #"Parsed JSON", Counter = 0], each [Result][meta][cursors][after] <> null and [Counter] < 1000, each [ Result = Json.Document(Web.Contents("https://www.blah.com/events?after=" & Result[meta][cursors][after])), Counter = [Counter] + 1 ] ) in Custom2Personnally, my code looks like this :
Source = List.Generate( () => [Result = Fx_GetData(P_URL)], each [Result]<>null, each [Result = try Fx_GetData(P_URL&"&pagination_token="&[Result][meta][next_token]) otherwise null] ),This way it handles error cases.
Thank you again 🙏
Thanks very much ImkeF I think that makes sense to me.
However, isn't this assuming I start with the JSON? The JSON I actually get as an API response, the existing query (that returns the JSON) just looks like this:
let Source = Json.Document(Web.Contents("https://api.gocardless.com/events", [Headers=[Authorization="Bearer ???accesstoken???", #"GoCardless-Version"="2015-07-06"]])), events = Source[events], #"Converted to Table" = Table.FromList(events, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "created_at", "resource_type", "action", "links", "details", "metadata"}, {"id", "created_at", "resource_type", "action", "links", "details", "metadata"}), #"Expanded links" = Table.ExpandRecordColumn(#"Expanded Column1", "links", {"mandate"}, {"mandate"}), #"Expanded details" = Table.ExpandRecordColumn(#"Expanded links", "details", {"origin", "cause", "scheme", "reason_code", "description"}, {"origin", "cause", "scheme", "reason_code", "description"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded details",{"metadata"}) in #"Removed Columns"Obviously this has me converting the data into tables too.
- ImkeF
Hi Anonymous
Have you considered adjusting your query?
