Rest API Dynamic Date Query

[Anonymous]

Hi Everyone.

I am having a problem with the Ariba API dynamic time filter. Code is listed below.

Source = Json.Document(Web.Contents("https://openapi.ariba.com/api/analytics-reporting-details/v1/prod/views/templatename?realm=myrealm&filters={""createdDateFrom"":""2020-02-01T00:00:00Z"",""createdDateTo"":""2020-02-06T00:00:00Z""}")),

Basically I want to make date dynamic ( from and to ) . I want to detect 2 months time period dynamically selected.

{""createdDateFrom"":""2020-01-01T00:00:00Z"",""createdDateTo"":""2020-02-29T00:00:00Z""}

Many Thanks in advance.

[Mariusz]

Hi @Anonymous 

Try something like this.

Edited 2.0:

let
    endDate = Date.From( DateTime.LocalNow() ),
    startDate = Date.AddMonths( endDate , -2 ),
    endDateText = Date.ToText( endDate, "yyyy-MM-ddT00:00:00Z" ),
    startDateText = Date.ToText( startDate, "yyyy-MM-ddT00:00:00Z" ),
    Custom1 = "https://openapi.ariba.com/api/analytics-reporting-details/v1/prod/views/templatename?realm=myrealm&filters={""createdDateFrom"":"& startDateText &",""createdDateTo"":"&endDateText&"}"
in
    Custom1

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mariusz · Accepted solution]

Hi @Anonymous 

You can adjust your code to:

let
    startMonthDate = Date.StartOfMonth( Date.From( DateTime.LocalNow() ) ) ,
    endDate = Date.AddDays(  startMonthDate , -1 ),
    startDate = Date.AddMonths( startMonthDate , -2 ),
    endDateText = Date.ToText( endDate, "yyyy-MM-ddT00:00:00Z" ),
    startDateText = Date.ToText( startDate, "yyyy-MM-ddT00:00:00Z" ),
    Custom1 = "https://openapi.ariba.com/api/analytics-reporting-details/v1/prod/views/templatename?realm=myrealm&filters={""createdDateFrom"":"& startDateText &",""createdDateTo"":"&endDateText&"}"
in
    Custom1

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi @Mariusz .

Thank you. Really appreciated. The Code is working for me.

Just a last Question. What if i want to skip the current month and filter for last two months. 

For Example: i want to skip the current march month 3 days etc, and filter the last whole february and january Months.

Many Thanks.

[Mariusz]

Hi @Anonymous 

Are you looking for the current month and previous or last two months?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Pipepiper]

Hi Mariusz - Please help. I have a similar requirement and struggling as I dont understand programming that well. I am trying to simply get the end date to dynamically pick the previous day or yesterdays date (i.e today -1). Below is my code that is picking a particular date range at the moment I need help with the end date to change dynamically. 

url = "https://abc.abcedfoperations.com/abcccc-adminisabc/c3/GetDataBCDEFG",
body = "{""filters"":{""fromDate"":""2020-01-01"",""endDate"":""2021-03-25""}}",
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json",#"Authorization" = "Bearer "&APILoopBackToken(),#"Ocp-Apim-Subscription-Key" = "abcdefxxxxxxxabcdefxxxxxabc"],
Content = Text.ToBinary(body) ] )),
Response = Source[Response],
#"Converted to Table" = Table.FromList(Response, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Entity",.........

[Anonymous]

Hi @Mariusz .

Thanks for reaching out.

I am looking for Current Month and last month.  Like today its 3 march 2020, so i want it automatically select from 3/01/2020 to 3/03/2020. The format must be json "YYYY,MM,DDTime" .

Thank you.