PowerQuery Rest API Pagination

[CountingPeople]

Hi folks

I am sure I am not the first one with the issue but I havent come across a viable solution for me. I am querying a Web API that returns pages by 200 rows. This API specifically also returns the max number of pages already in the first response (pagination.pages), so I should be able to implement the NextPage function easily (?). I've come across multiple examples where the number of pages first need to be calculated but I don't want to blow up the code.

The basic query looks as follows:

let
        APIKey = API_Key,
        APPKey = APP_Key,
        Source = Json.Document(Web.Contents("https://api.SOFTWARE.com/api/v3/deals.json?api_key="&APIKey&"&app_key="&APPKey)),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded entries" = Table.ExpandListColumn(#"Converted to Table", "entries"),
    #"Expanded pagination" = Table.ExpandRecordColumn(#"Expanded entries", "pagination", {"pages"}, {"pagination.pages"})
in
    #"Expanded pagination"

The pagination in the url can be done by adding ?page=1 at the end of the URL, as generally common. 

How can I implement a simple loop that will combine all pages into one table?

Thanks a lot for the help!

[SivaMani · Accepted solution]

@CountingPeople,

Try List.Generate() Function.

Blog - https://exceed.hr/blog/list-generate-and-looping-in-powerquery/

[mahoneypat]

Once you have the number of pages, you can use it to make a list of number from 1 to that number with {1..pagenumber}  (assuming the query that results in the drill down to the number of pages).  You can then expand that list to new rows, convert it to a table and then concatenate your url with the column with the numbers to get a table with the results of each URL call.  

For example  Web.Contents("... rest of url?page=" & Text.FromNumber([ColumnWithPageNumber]))

Pat

[CountingPeople · Accepted solution]

Morning everyone!

Thanks for the hints! After some trial & error I got it to work using a function PageRunner incorporating it into the main query. Note that I was lazy enough to defined the MaxPages first (as this is returned on each page) which I then used to limit the number of loops.

PageRunner:

(InputNumber)=>

let
    
    APIKey = API_Key,
    APPKey= APP_Key,
    Page="&page="&Number.ToText(InputNumber),
    Source = Json.Document(Web.Contents("https://api.SOFTWARE.com/api/v3/deals.json?api_key="&APIKey&"&app_key="&APPKey&Page)),
    #"Converted to Table" = Table.FromRecords({Source})
    in
    #"Converted to Table"

MainQuery:

let
    MAXPages = let
    
    APIKey = API_Key,
    APPKey= APP_Key,
    Page="&page="&Number.ToText(1),
    Source = Json.Document(Web.Contents("https://api.SOFTWARE.com/api/v3/deals.json?api_key="&APIKey&"&app_key="&APPKey&Page)),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{1}[Value],
    pages1 = Value[pages]
in
    pages1,
    
    Source = List.Generate(
        ()=> [Page=1, Funct = PageRunner(1)],
        each [Page]<=MAXPages,
        each [Page=[Page]+1, Funct = PageRunner([Page]+1)]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
    #"Converted to Table"

This works just fine. Any hints for makinf it more simple / efficient is highyl appreciated.

[SivaMani · Accepted solution]

@CountingPeople,

Try List.Generate() Function.

Blog - https://exceed.hr/blog/list-generate-and-looping-in-powerquery/