For Loop To Get API Data

[numersoz]

Hi,

I have use an API where I send a parameter to get the results, but I have three different tables where has all the values needed to get the whole data.

I have a query that is pulling all possible names, another one for all possible values to get, another one for all possible dates.

How I can create a loop where when I filter these, the API parameters will change accordingly. 

Here is the API code that I use

let
    //Fil your login info
    username = "email@email.com",
    password = "PASSWORD",
    
       
    //These are the available access types
    access = [#"list_names" = "names/list", #"list_variables" = "variables/list?id=", #"list_data" = "data/list?"],

    //This information needs to be filled to get the desired data
    access_type = "list_data",
    name_id = "2332", //id variable
    start_date = "2018-02-27", //start variable
    end_date = "2018-03-01", //end variable
    time_format ="time", //time format expression
    aggregation = "integrate", //Options: mean, median, integrate, min, max, first, last, firstnm, lastnm
    variable= "automation:temperature@oven1", //expression variable
    period = "1+h", //Use this format: native, singleton, 1+s, 1+min, 1+h, 1+day

//Get the data from API
    //Define the query string to be send
    qry_str = if access_type = "list_data" then     
        access[list_data] & "id="&name_id & "&start="&start_date & "&end="&end_date & "&expression="&time_format & "&expression=" & aggregation&"(" & variable & ")&" & "&period="&period
    else if access_type = "list_names" then
        access[list_names]
    else
        access[list_variables]&name_id,
        
    //URLs
    token_url = "myurl.com",    
    api_base_url = "myapiurl.com", 
    //Uses
    body="grant_type=password&client_id=data_api&client_secret=" & password & "&username=" & username & "&password=" & password,
    token_response  = Json.Document(Web.Contents(token_url,
    [ 
      Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
      Content=Text.ToBinary(body)
    ]
    )
    ),
     token = token_response[access_token],

     response= Json.Document(Web.Contents(api_base_url&qry_str,
    [ 
     Headers = [
                #"Authorization"="Bearer "&token,#"Content-Type"="application/json"]
     
    ]
    )
    )
in
    response

I have these below API options where for every possible option I can create a separate query or table. 

name_id = "2332", //id variable
    start_date = "2018-02-27", //start variable
    end_date = "2018-03-01", //end variable
    time_format ="time", //time format expression
    aggregation = "integrate", //Options: mean, median, integrate, min, max, first, last, firstnm, lastnm
    variable= "automation:temperature@oven1", //expression variable
    period = "1+h", //Use this format: native, singleton, 1+s, 1+min, 1+h, 1+day

[Anonymous]

@numersoz - Try the following.

1. Create a Parameter for each of the parameters you want to send to the website. Give each parameter a sample value.

2. Import Data from your website with your parameters as URL parts. Initially, this will use your sample values.

3. Create a Function from the api data source in step 2.

4. Add a New Column to your table that lists parameters, with Invoke Custom Function. You'll need to map the parameters from columns in your table.

You could do the above for each of your parameters, if you want to do them separately. Then, you can Append the queries together.

Hope this helps,

Nathan

[numersoz]

@Anonymous One of the parameters available is to list all available parameters. So now I have a table with the name and ID. I have to use the ID inside the parameter, not the name. 

When I create a parameter, there is an option to get them from a query. But it has to be a list. If I leave the ID's alone as a list, then I can use this but it's hard its easier to use the names instead. How can I match these?

[Anonymous]

Your parameter would be ID, and you can invoke the function from a new column in your table. That way, each ID in your table will be used to call the URL, and the results will be returned to the new column. You can then expand the column. 

Hope this helps,

Nathan