Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Parameters for JSON API data - Maintain Query daily

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 =...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

    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.


    Thanks,
    Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Anonymous,

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.


Thanks,
Lydia Zhang

shoeshirt's avatar
shoeshirt
Frequent Visitor
3 years ago

I think I follow your solution but what if the API for the URL uses relative time units for example: Now - 1Month + 3Days.

We are loading time series data and it could potentailly be millions of rows. I want to use the Json.Document to initially load 2 days worth of data but then allow the user to select a date range. But I need the API to use relative time units. Is there a way to allow the user to select dates that then are transformed to relative time units to use as the parameters?