Forum Discussion

bikelley's avatar
bikelley
Helper IV
4 years ago
Solved

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

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/webservice/v2.0/workspace/331029809496&query=&fetch=true&pagesize=2000&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  

Need Help
Show and Tell
Tips and Tricks
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    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's avatar
    PhilipTreacy
    4 years ago

    Hi bikelley 

     

    Change the FinalList step to this

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

     

    Regards

     

    Phil

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    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's avatar
      bikelley
      Helper IV
      4 years ago

      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 

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    Hi bikelley 

     

    Change the FinalList step to this

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

     

    Regards

     

    Phil

    • bikelley's avatar
      bikelley
      Helper IV
      4 years ago

      Thank you so much for your help and time.