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
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi DryMouse555 ,

To dynamically set the 'DateStart' to pull data from the last 3 months and 'DateEnd' to the current day in your API call, please modify the codes as below and check if it can work:

Date.AddMonths - PowerQuery M | Microsoft Learn

 

let
    EndDate= DateTime.LocalNow(),
    StartDate = Date.AddMonths(DateTime.Date(EndDate), -3),
    DateStart = DateTime.ToText(StartDate, "yyyy-MM-dd"),
    DateEnd = DateTime.ToText(Today, "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

  • DryMouse555's avatar
    DryMouse555
    Frequent Visitor
    2 years ago

    Hi, I am still getting these errors. I have added the code above.


     

     

     

     

    Also, before I put static date values, I used this code. I was told to use this date format to get the output

    = Json.Document(Web.Contents("https://xyz.com.au/api/",
    [Query=[
    dbtable="statementitems",
    fundingtype= "2",
    DateStart = "01/Jun/2024",
    Date.End = "29/Jun/2024",
    Authkey = ApiKey,
    includedeleted = "yes",
    toprestrict = "1000000",
    source= "powerBI"
    ]]
    ))