Create a dynamic value for Json.Document function

[Anonymous]

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.

[Anonymous]

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]

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

[Anonymous]

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 ?

[lbendlin · Accepted solution]

Welcome to the wonderful world of the formula firewall.  Happy reading.

Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Docs

Inline your queries if possible.

[Anonymous]

@lbendlin many thanks for your help 

[lbendlin]

read about the Query option for Web.Contents()