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

JosephYalla's avatar
JosephYalla
Regular Visitor
5 years ago

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.