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 ...
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

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