Add dynamic dates in API Source url

[DryMouse555]

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.

[DryMouse555]

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]

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]

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

[Anonymous]

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]

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