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

[Anonymous]

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) and the only way to know if there are more issues/tasks assignet to a given project than one bookmark is to use the value of the nextBookmark column as a parameter in a new API request. So far i have created a parameter and a function which invokes the projectID column of the Projects-table and uses this parameter in the API request to fetch all issues in the first bookmark of each project. I would love to be able to fetch all issues in all bookmarks of all projects in the same table. Is there a way to solve this is PowerBI alone? 
As a dummy-way to solve this i have manually copyed and pasted each nextBookmark of issues of a project until i reached a blank list then created a table for every bookmark and then appended all of these into one table that represent every issue for one given project. This works ok, but when the last bookmark is filled up i would have to manuelly create a new table with the nextBookmark value from the current table. Also, this method only gives me the issues of one project at a time. I have searched through reddit, stackoverflow and these powerbi forum posts and i have yet to solve this.  I see alot of youtuber use list.generate to solve API pagination but they often know the total number of pages or the pages will have sequencial numbers like page 1, page 2, page 3. In this case each bookmark has a "random" value and it is impossible to foresee what the next of next bookmark value will be. Any clues to solve this would be greatly appreciated.

[Anonymous]

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.