Forum Discussion
Parameters for JSON API data - Maintain Query daily
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
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
6 Replies
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 ZhangHi Lydia,
I have a web API as below:
https://churchmetrics.com/api/v1/campuses/12345/weekly_totals.json?category_id=234567
Numbers in the API are parametrized, i.e., 12345 (represents location ID) and 234567 (specific data for the location, i.e., attendance, new comers, etc.)
There are 10 different locations and each location has 4 different types of data that I want to extract.
I have created two lists in power query editor, i.e., one for locations and the other list for types of data. I want to use parameters to be able to fetch all the values (i.e., 40 values) in single query using parameters. Is it possible to do in Power Query?
Please advise.
This is resolved using inputs from https://msdynamicsworld.com/story/use-parameters-and-custom-functions-call-apis-power-bi althoug I am unable to automate data refresh in Power BI service since I am using custom functions.
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?
