Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create a dynamic value for Json.Document function

Hi All,

 

I'm using the function Json.Document to get data from an API. Below is the an example of the Json.Document value:

 

Json.Document(Web.Contents("https://url.com/site/222222/energyDetails?xxx&startTime=2022-07-21%2011:00:00&endTime=2022-08-26%2013:00:00&api_key=xxx"))

 

 

I would like to change the startTime and the endTime value dynamically with values coming from another table. Is it possible ?

Maybe something like this:

 

Json.Document(Web.Contents("https://url.com/site/22222/dateDetails?xxx&startTime=2022-07-21%2011:00:00&endTime="#Table1[endTime]"&api_key=xxxx"))

 

 

Many thanks in advance for your help.

Tips and Tricks

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi,

     

    thanks for your recommandation.

    After reading the Query option for the Web.Contents(), I used the web.contents like below:

    = Json.Document(
        Web.Contents(
            "https://url.com/site/222222",
            [
                RelativePath="energyDetails",
                Query=
                [
                    grid="XXX",
                    startTime=DateTime.ToText(#"Site Data - Start and End Date"[dataPeriod.startDate]),
                    endTime="2022-08-29 13:00:00",
                    api_key="xxx"
                ]
            ]
        )
    )

    the options "startTime" and "endTime" are from another table "Site Data - Start and End Date" which contains two columns:

    • dataPeriod.startDate
    • dataPeriod.endDate

     

    I got the the problem below when i'm executing the code above:

     

    What's wrong with the startTime value ? how can I fix that ?

     

    Thanks in advance for the help

    Regards,

     

     

     

    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago
      startTime=DateTime.ToText(#"Site Data - Start and End Date"[dataPeriod.startDate]){0}
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Thanks lbendlin for your feedback.

         

        I still have the same error message after adding "{0}":

         

        startTime=DateTime.ToText(#"Site Data - Start and End Date"[dataPeriod.startDate]){0}

         

         

        I tried to added inside the DateTime.ToText() function and I got the error message as below :

         

        startTime=DateTime.ToText(#"Site Data - Start and End Date"[dataPeriod.startDate]{0})

         

        Any help ?