Forum Discussion

aukev's avatar
aukev
Helper III
7 years ago

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