Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CountingPeople
Frequent Visitor

PowerQuery Rest API Pagination

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!

2 ACCEPTED SOLUTIONS
SivaMani
Resident Rockstar
Resident Rockstar

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.

 

View solution in original post

3 REPLIES 3
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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
Resident Rockstar
Resident Rockstar

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors