Forum Discussion
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!
Anonymous,
Try List.Generate() Function.
Blog - https://exceed.hr/blog/list-generate-and-looping-in-powerquery/
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.
3 Replies
Anonymous,
Try List.Generate() Function.
Blog - https://exceed.hr/blog/list-generate-and-looping-in-powerquery/
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
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.
