Forum Discussion
Consuming Rest APIs through Power BI
Hello Power BI Gurus
This is my first attempting at consuming Rest APIs through power query. After much struggle, i am able to make an API call successfully. It returns only 30 records. The documentation says that I have to append page number at the end of the query string to succesfully paginate
Something like this
let
Source = Json.Document(Web.Contents("https://xxxxxx.freshservice.com/helpdesk/tickets/filter/all_tickets?format=json&page=1", [Headers=[Authorization="Basic xxxxxxxx", Content_Type="application/json"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Essentially I need to code to dynamically change the "page=" over any number of pages it may be
I called the function GetFresh
(page as number) as table =>
let
Source = Web.Page(Web.Contents("https://xxxxxxx.freshservice.com/helpdesk/tickets/filter/all_tickets/" , [Headers=[Authorization="Basic xxxxxxxxx", #"Content_Type"="application/json"]],
[RelativePath="?page=" & Number.ToText(page),
Query=[Headers=
[Authorization="xxxxxxxxxxxxxxxxxxxxxxxxxxxx",
Content_Type="application/json"]]]))
in
Source
To invoke this function
let
Source = List.Generate( ()=>
[Result=try GetFresh(1) otherwise null, page=1],
each [Result] <> null,
each[Result=try GetFresh([page]+1) otherwise null, page=[page]+1],
each [Result])
in
Source
To loop through pages I created this query
let
Source = List.Generate( ()=>
[Result=try GetFresh(1) otherwise null, page=1],
each [Result] <> null,
each[Result=try GetFresh([page]+1) otherwise null, page=[page]+1],
each [Result])
in
Source
There is no syntax error on these
I need help to close this
Swati
1 Reply
- v-chuncz-msftCommunity Support