Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
K-LED
Helper I
Helper I

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.

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

 

View solution in original post

6 REPLIES 6
K-LED
Helper I
Helper I

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:

KLED_0-1661784440010.png

 

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

 

Thanks in advance for the help

Regards,

 

 

 

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

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})

 

KLED_0-1661840349481.png

Any help ?

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.

 

@lbendlin many thanks for your help 

lbendlin
Super User
Super User

read about the Query option for Web.Contents()

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.