API as data source with dynamic date

[LiliLPo]

Hi, 

I want to get data from an API. 

I need the data to be pulled each month for the previous month only, I dont need to append the data only monthly data. 

how do i write it so it will be equivalent 

to the following query in sql:

where datepart(month,UpdatedAt) = datepart(month,dateadd(month, -1,getdate()))

Here is my API call:

= (PageNumber) =>

let
token_body = [
client_id = tokenClientId,
client_secret = tokenClientSecret,
grant_type = "client_credentials",
scope = tokenClientScope
],
tokenResponse = Json.Document(
Web.Contents(
tokenAddress,
[
Headers = [
ContentType ="application/x-www-form-urlencoded",
Accept = "application/json"
],
Content = Text.ToBinary(Uri.BuildQueryString(token_body))
]
)
),
Source = Json.Document(
Web.Contents("https://XXXXXXXXXXX.net/api/XXXXX/Get?receivedFromDateTime=2023-02-01&receivedToDateTime=2024-02-28&..."&Number.ToText(PageNumber),
[
Headers = [
Accept="application/json",
Authorization = "Bearer " & tokenResponse[access_token]
]
]
)
)
in
Source

thank you 

[LiliLPo]

THANK YOU SO MUCH 😀 it workedI

[PhilipTreacy · Accepted solution]

Hi @LiliLPo 

You can use this code to create the Start and End dates for the previous month

    Now = Date.AddMonths(Date.From(DateTime.LocalNow()), -1)
    SOM = Date.ToText(Date.StartOfMonth(Now), [Format="yyyy-MM-dd"]),
    EOM = Date.ToText(Date.EndOfMonth(Now), [Format="yyyy-MM-dd"])

Then use these in your URL like this

Web.Contents("https://XXXXXXXXXXX.net/api/XXXXX/Get?receivedFromDateTime="&SOM&"&receivedToDateTime="&EOM&"&......."

Regards

Phil