Forum Discussion
how to create a query that paginates?
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.
Hi Anonymous
Have you considered adjusting your query?
- Anonymous6 years agoNot applicable
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?
- Anonymous6 years agoNot applicable
Scratch that, found a Power Query XML import for Notepad++
Happy to share if anyone requires.- noorul6 years agoRegular Visitor
Anonymous thanks for the posts, did you manage to get your code working? I am using the same source as you (GC) and having trouble manipulating the code so that it refreshes in Power BI online, I have managed to create my report so that i can refresh it in Power Bi desktop but obviously this is not the ideal solution.