Forum Discussion
PowerQuery Rest API Pagination
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.
Microsoft EmployeeOnce 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.