Forum Discussion
aukev
7 years agoHelper III
Pagination API until no results
I have this API call. There's pagination and a counter. But Ideally I would like it to stop whenever there's no results left. I've tried a couple of things and I guess it needs to be done during the List.Generate where I currently have each [Counter] < 50. But I`m not sure what to put there. I.e. each [WebCall] <> null ?
This is the code I have
Let
Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
each [Counter]<50,
each [ WebCall = Function.InvokeAfter(
()=>Json.Document(Web.Contents(Url,
[Query=[limit="" & Limit & "",page="" & Text.From([Page]) & "", #"filter[1][attribute]"="created_at", #"filter[1][gt]"=StartDate],
Headers=[Authorization="OAuth
oauth_consumer_key=" & consumerKey & ",
oauth_token=" & Token & ",
oauth_signature_method=" & SignatureMethod & ",
oauth_timestamp=" & TimeStamp & ",
oauth_nonce=" & Nonce & ",
oauth_version=""1.0"",
oauth_signature=" & Signature & "
"]
])),
#duration(0,0,0,0.5)),
Page = [Page]+1,
Counter = [Counter]+1
]
) ,1),
Table = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
column = Table.ExpandRecordColumn(Table, "Column1", {"WebCall"}, {"Column1.WebCall"}),
// convert EACH record to the table of columns Name and Value^
RecordsToTables = Table.TransformColumns(
column,
{"Column1.WebCall", Record.ToTable}
),
// expand these tables
ExpandTables = Table.ExpandTableColumn(RecordsToTables, "Column1.WebCall", {"Name", "Value"}, {"WebCall.Id", "Value"}),
// get all field names of all inner records of Value column:
Recs = List.Buffer(ExpandTables[Value]),
FieldNames = List.Union(List.Transform(Recs, Record.FieldNames)),
// expand these records with the full list of field names:
ExpandRecords = Table.ExpandRecordColumn(ExpandTables, "Value", FieldNames, FieldNames),
in
ExpandRecords 4 Replies
- v-chuncz-msftCommunity Support
- aukevHelper III
Thanks, I`ll look into it.
- aukevHelper III
v-chuncz-msft I looked at the resources but sadly these won't help me. These use functions and my query should be able to be used on power bi service.