How to create a custom dynamic list for REST API pagination?

[bikelley]

Hello, 

I am trying to create a dynamic list that increments by 2000 until it meets the Total Result Count. Please see below for my initial API result. 

I have tried a few options and did not work.  Please let me know how to do this, and my API knowledge with Power BI is little to non. 
Below the option I tried hard coding it to test, it works but is not dynamic. 

Option 2 is not working, it will give me an error message. One more thing I can not do is start page 1,2,3... It will give me the same result.

All I want is to add a dynamic list like follow until it meets TotalResultCount on API.

Json.Document(Web.Contents("https://rally1.rallydev.com/slm/webservice/v2.0/defect?workspace=https://rally1.rallydev.com/slm/web...start="&[Column1], [Headers=[zsessionid="***********************"]]))),

List = 
Start
1
2000
4000
6000
8000
10000
12000
14000
Please help me to achieve this in the best way. 

I also tried the following methods that I found online, unfortunately, did not work for me.

http://sqlcodespace.blogspot.com/2017/09/power-bipower-query-api-response.html

https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

https://adatis.co.uk/Loop-through-Multiple-Web-Pages-using-Power-Query/

Thank you so much for your help & time  

[PhilipTreacy · Accepted solution]

Hi @bikelley 

Download example PBIX file

This code will create your list

let
    Source = Record.FromList({12958},{"TotalResultCount"}),
    TotalResultCount = Source[TotalResultCount],
    DynamicList = List.Generate(() => 2000, each _ < TotalResultCount, each _ + 2000),
    FinalList = List.Combine({DynamicList, {TotalResultCount}})
in
    FinalList

I've set up a dummy record in the Source step so that I can simulate getting the TotalResultCount.  You can modify this to suit your own query structure.

Regards

Phil

[PhilipTreacy · Accepted solution]

Hi @bikelley 

Change the FinalList step to this

FinalList = List.Combine({{1}, DynamicList, {TotalResultCount}})

Regards

Phil

[bikelley]

Thank you so much for your help and time. 

[PhilipTreacy · Accepted solution]

Hi @bikelley 

Download example PBIX file

This code will create your list

let
    Source = Record.FromList({12958},{"TotalResultCount"}),
    TotalResultCount = Source[TotalResultCount],
    DynamicList = List.Generate(() => 2000, each _ < TotalResultCount, each _ + 2000),
    FinalList = List.Combine({DynamicList, {TotalResultCount}})
in
    FinalList

I've set up a dummy record in the Source step so that I can simulate getting the TotalResultCount.  You can modify this to suit your own query structure.

Regards

Phil

[bikelley]

Hi  @PhilipTreacy 
Thank you so much for your help. 
This is working, but the only issue is the List not starting from 1. Can please tell me is there any way we can add 1 before 2000. Now I am not getting the first 2000 records. 

List

1
2000
4000
6000
8000
10000
12000
12958

This my Power Query with your code,

let
  Source = Json.Document(Web.Contents("https://rally1.rallydev.com/slm/webservice/v2.0/defect?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/331029809496&query=&fetch=true&start=1&pagesize=20000", [Headers=[zsessionid="**************"]])),
    QueryResult = Source[QueryResult],
    TotalResultCount = QueryResult[TotalResultCount],
    DynamicList = List.Generate(() => 2000, each _ < TotalResultCount, each _ + 2000),
    FinalList = List.Combine({DynamicList, {TotalResultCount}})
in
    FinalList

Please let me know the way I can add 1. Thank you so much