Forum Discussion
how to create a query that paginates?
Community ChampionHi 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
Custom2
You can also check out this video to learn more about the technique: https://www.youtube.com/watch?v=vhr4w5G8bRA
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?
ImkeF I absolutely have 😆
I've rewritten it about 15 times today in notepad++ but the but I can't seem to figure is how to capture the JSON output and feed it back into the query.
I understand in the query you've written as in you're feeding in the JSON as the source directly, then parsing that, then using part of that to build the next URL and looping through until done.
And in mine i'm just calling the API, capturing the JSON results and converting to a table.
The bit I don't understand is how i'd get the results from my query and feed them into what you've written - in one query - because the JSON has to be recieved from the original call. Unless i'm missing something really obvious, as I say, I'm very new to this so could well be.
btw, side note, is there any reliable syntax highlighting for this?
Scratch that, found a Power Query XML import for Notepad++
Happy to share if anyone requires.