cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Consuming Rest APIs through Power BI

@svishwanathan,

 

Web.Contents only accept two arguments.

https://community.powerbi.com/t5/Desktop/Stopping-when-looping-through-a-paginated-API-feed/m-p/3103...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Highlighted
Community Support Team
Community Support Team

Re: Consuming Rest APIs through Power BI

@svishwanathan,

 

Web.Contents only accept two arguments.

https://community.powerbi.com/t5/Desktop/Stopping-when-looping-through-a-paginated-API-feed/m-p/3103...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.