Reply
Highlighted
Frequent Visitor
Posts: 10
Registered: ‎08-15-2015
Accepted Solution

Parameters for JSON API data - Maintain Query daily

[ Edited ]
How can I maintain a report from a JSON API service when the JSON URL I am using is parameterized and needs to be updated to retrieve new data.
 
The current working URL I have is Source = Json.Document(Web.Contents("https://api.tatts.com/sales/vmax/web/data/racing/2017/4/12/sr/full")),
 
The api as described here(http://media.tatts.com/documentations/IData/IData.html#Data/Racing) follows this pattern
 
data/racing/{year}/{month}/{day}/{meetingcode}/full
 
where I need the users to be able to put in a date or choose from available dates and supply or use a dropdown (like excel data validation lists) to choose the meeting code.
 
Manually editing the query in the advanced editor didn't work how do I achieve this?
data/racing/{year}/{month}/{day}/{meetingcode}/full

Accepted Solutions
Moderator
Posts: 3,119
Registered: ‎03-10-2016

Re: Parameters for JSON API data - Maintain Query daily

[ Edited ]

Hi @Sayth,

Add a new blank query in Power BI Desktop, then paste  the following code in Advanced Editor.

let sqltext = (Date as text) =>

let
    Source = Json.Document((Web.Contents("https://api.tatts.com/sales/vmax/web/data/racing/" & Date & "/sr/full")))
in
    Source  
in
    sqltext


Then you can input date in the sqltext function to return result of that date. See my example below.

1.PNG2.PNG3.PNG


Thanks,
Lydia Zhang

View solution in original post


All Replies
Frequent Visitor
Posts: 10
Registered: ‎08-15-2015

Re: Parameters for SON API data - Maintain Query daily

If its not possible do you know if this is something on the radar?
Moderator
Posts: 3,119
Registered: ‎03-10-2016

Re: Parameters for JSON API data - Maintain Query daily

[ Edited ]

Hi @Sayth,

Add a new blank query in Power BI Desktop, then paste  the following code in Advanced Editor.

let sqltext = (Date as text) =>

let
    Source = Json.Document((Web.Contents("https://api.tatts.com/sales/vmax/web/data/racing/" & Date & "/sr/full")))
in
    Source  
in
    sqltext


Then you can input date in the sqltext function to return result of that date. See my example below.

1.PNG2.PNG3.PNG


Thanks,
Lydia Zhang