cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sayth
Advocate I
Advocate I

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 = 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
1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft
Microsoft

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

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

View solution in original post

5 REPLIES 5
FernSant
Helper I
Helper I

in Octber Power BI Server & Power Desktop The Solution works in Power BI Desktop only

Cannot save to Power BI Server after upgrading to Power BI Server October 2020 (Build 15.0.1104.264) not in (Build 15.0.1104.300), Released: January 8, 2021.

Could someone share any workaround to this issue ?

 

v-yuezhe-msft
Microsoft
Microsoft

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

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

View solution in original post

Hi 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.

Sayth
Advocate I
Advocate I

If its not possible do you know if this is something on the radar?

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors