Forum Discussion

DryMouse555's avatar
DryMouse555
Frequent Visitor
2 years ago

Add dynamic dates in API Source url

Hi,   I have connected to a online system using API url and Auth key. Below is my code. This I enter in the blank query as source step. = Json.Document(Web.Contents("https://xyz.com.au/api/", [...
Need Help
DryMouse555's avatar
DryMouse555
Frequent Visitor
2 years ago

Hi,

 

Sorry for the late reply.

My input: 

let
EndDate= DateTime.LocalNow(),
StartDate = Date.AddMonths(DateTime.Date(EndDate), -3),
DateStart = DateTime.ToText(StartDate, "dd/mm/yyyy"),
DateEnd = DateTime.ToText(Today, "dd/mm/yyyy"),
apiUrl = "https://xyz.com.au/api/",
Source = Json.Document(Web.Contents(apiUrl,
[
Query = [
dbtable = "statementitems",
fundingtype = "2",
DateStart = DateStart,
DateEnd = DateEnd,
Authkey = ApiKey,
includedeleted = "yes",
toprestrict = "1000000",
source = "powerBI"
]
]
))
in
Source

 

I am getting this message 

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi DryMouse555 ,

Base on the error message in the screenshot, it seems that there’s an issue with the date format or conversion. Please update the codes as below and check if it can return the expected data...

let
    EndDate= DateTime.LocalNow(),
    StartDate = Date.AddMonths(DateTime.Date(EndDate), -3),
    DateStart = Date.ToText(StartDate, "yyyy-MM-dd"),
    DateEnd = Date.ToText(DateTime.Date(EndDate), "yyyy-MM-dd"),
    apiUrl = "https://xyz.com.au/api/",
    Source = Json.Document(Web.Contents(apiUrl,
    [
        Query = [
            dbtable = "statementitems",
            fundingtype = "2",
            DateStart = DateStart,
            DateEnd = DateEnd,
            Authkey = ApiKey,
            includedeleted = "yes",
            toprestrict = "1000000",
            source = "powerBI"
        ]
    ]
    ))
in
    Source

Best Regards