How to pass a dynamic date parameter in the json api in power bi

[Anonymous]

Hi,
Somehow I managed to run the below web api with a static start date and end date and i am getting the result in Power Bi. But I need to pass the date values dynamically and I know as per what I have googled that it is possible to do so via creating
Parameters. 
I am not able to do so and whatever I try I get error as There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression.

I  would like some help in formatting the correct syntax correctly. I have created two parameters StartDate and EndDate with Date Type and have provided a default date value.

API I am running currently successfully in Blank Query is : 

= Json.Document(Web.Contents("https://www.xyz.com/api/rest/v1/entries?startdate=2018-01-01&enddate=2019-12-12",[Headers=[Accept="application/json", Authorization="Bearer eyJraWQixyz"]]))

API I tried with parameters but with errors:

= Json.Document(Web.Contents("https://www.xyz.com/api/rest/v1/entries?startdate="&Date.ToText([StartDate], "yyyy-MM-dd")&"&enddate="&Date.ToText([StartDate], "yyyy-MM-dd")&"", [Headers=[Accept="application/json", Authorization="Bearer eyJraWQixyz"]]))

Kindly guide what is wrong in M query here

Thanks
Ankku

[Ali5457]

Hi,

I am getting data from an API that has two parameters like below in the URL:

Param1930=2021-01-01&Param1931=2030-12-31&format=JSON

These are the start and end dates of the report that I have manully set up in the API URL. But, how do I make it dynamic so that eveyday that I refresh the data in Power BI connect to the API and pull the latest data? My current M code is something like below:

let
Source = Json.Document(File.Contents("C:\Users\Ali\AppData\Local\Microsoft\Windows\INetCache\IE\EHD7MHFN\1120")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"txtProvider",
"txtIncidentNo",
"FltDate",
"LogTime")

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Based on my test, use [] around parameter may be the reason of error. We can modify the formula to following.

= Json.Document(Web.Contents("https://www.xyz.com/api/rest/v1/entries?startdate="&Date.ToText(StartDate, "yyyy-MM-dd")&"&enddate="&Date.ToText(StartDate, "yyyy-MM-dd")&"", [Headers=[Accept="application/json", Authorization="Bearer eyJraWQixyz"]]))

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more