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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

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"]]))

11.PNG12.PNG

 

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

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

View solution in original post

2 REPLIES 2
Ali5457
Frequent Visitor

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
Community Support
Community Support

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"]]))

11.PNG12.PNG

 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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