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/",
[Query=[
dbtable="statementitems",
fundingtype= "2",
DateStart = "Date.IsInPreviousMonth",
DateEnd = "Date.IsInCurrentDay",
Authkey = ApiKey,
includedeleted = "yes",
toprestrict = "1000000",
source= "powerBI"
]]
))


I want to add in DateStart where whenever it refreshed, it pulls the last 3 months data and DateEnd to Current day.
Can you tell me how to do that? Thanks in advance.

 

Need Help

5 Replies

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

       

  • 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

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