Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Tricky API pagination with random page values and unknown number of pages

Here is the case: Each project has issues/tasks assigned to them and these issues/tasks are fetched in bulks of 100 issues each. Each bulk of issues is contained within a bookmark (similar to a page)...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

Please this query as an example of how to approach this. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = List.Numbers(0,150,1000),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"Column1", each "[" & _, type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Column1", each _ & "]", type text}}),
    #"Added Custom" = Table.AddColumn(#"Added Suffix", "WebCalls", each Web.Contents("https://api.dotmailer.com/v2/contacts?$select=[1000]&$skip=" & [Column1]))
in
    #"Added Custom"

You can try to first create a List of numbers that increment by 1000 (API limit), convert it to a table, convert the numbers to text, and then add a custom column that concatenates the API call (with $skip or $skiptoken) and the number. You should then be able to expand the returned tables and have your 150k rows.

 

Hope it helps you.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.