Passing dynamic date parameters to Web query

[hlombard]

I am able to pull data for a date range from our web service. I use a web query with the following string:

https://reports.xxxxxxxx.net/generate.py/ProcessedTxnsRecon.csv?StartDate=20170701&EndDate=20170708

I have created two parameters; FromDate and ToDate and use it like so:

= Csv.Document(Web.Contents("https://reports.xxxxx.net/generate.py/ProcessedTxnsRecon.csv?StartDate="&FromDate&"&EndDate="&ToDate& ""),[Delimiter=",", Columns=43, Encoding=1252, QuoteStyle=QuoteStyle.None])

I would like to make this dynamic by setting the ToDate to "Today's date" and the FromDate to "Today's date less 3 months"

Even better: The FromDate should be a parameter with a default of 3 months!

So the end result should be: When I refresh the report, it pulls data starting 3 months ago up to today.

If I want more data, I change the FromDate parameter to say, 6 months and refresh again.

Your help is appreciated.

[ImkeF]

Hi @gingercat123 ,
sure - you have to use the "-" as a delimiter in the 2nd function argument of Text.Combine:
TodaysDate

Text.Combine(List.Transform(Record.FieldValues(Date.ToRecord(Date.From(DateTime.LocalNow()))), each Text.PadStart(Text.From(_), 2, "0")), "-")

FromDate:
 

= Text.Combine(List.Transform(Record.FieldValues(Date.ToRecord(Date.AddMonths(Date.From(DateTime.LocalNow()),-<<YourParameter>>))), each Text.PadStart(Text.From(_), 2, "0")),"-")

[gingercat123]

Thanks Imke, If i  would want to use this code dynamically , to fetch the data for past 12 months is that possible, api link will allow me to download data for only 30 days max, can i run in loop 

[gingercat123]

Great solution how to get date in YYYY-MM-DD format instead of YYYYMMDD, i Need to have "-" in the date

[ImkeF]

That's some really embarassing code here, hope that someone comes up with a shorter one:

Todays date:

Text.Combine(List.Transform(Record.FieldValues(Date.ToRecord(Date.From(DateTime.LocalNow()))), each Text.PadStart(Text.From(_), 2, "0")))

From date:

= Text.Combine(List.Transform(Record.FieldValues(Date.ToRecord(Date.AddMonths(Date.From(DateTime.LocalNow()),-<<YourParameter>>))), each Text.PadStart(Text.From(_), 2, "0")))

Make sure that your parameter is formatted as number.

[hlombard]

Thanks for your response.

Where do I put this code?

In the parameter - query field? That field does not allow me to click in it to edit?

[ImkeF · Accepted solution]

= Csv.Document(Web.Contents("https://reports.xxxxx.net/generate.py/ProcessedTxnsRecon.csv?StartDate="&Text.Combine(List.Transform(Record.FieldValues(Date.ToRecord(Date.From(DateTime.LocalNow()))), each Text.PadStart(Text.From(_), 2, "0")))&"&EndDate="&Text.Combine(List.Transform(Record.FieldValues(Date.ToRecord(Date.AddMonths(Date.From(DateTime.LocalNow()),-MonthParameter))), each Text.PadStart(Text.From(_), 2, "0")))& ""),[Delimiter=",", Columns=43, Encoding=1252, QuoteStyle=QuoteStyle.None])

with

[hlombard]

Genius !

You make me look good.

Thanks

[panipat1990]

Dear Team,

How to apply these codes. i am not understanding how to apply.Kindly help me for resolving.it's very urgent

[panipat1990]

Hi Imke,

Kindly tell me how to apply these code on table.

First of all how to make Max date of FACT_table

Then How to apply it Parameter and how to restrict in fact table through parameter.kindly help me