Forum Discussion
Passing dynamic date parameters to Web query
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.
= 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
9 Replies
Hi gingercat123 ,
sure - you have to use the "-" as a delimiter in the 2nd function argument of Text.Combine:
TodaysDateText.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")),"-")
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
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.
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?
= 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
Great solution how to get date in YYYY-MM-DD format instead of YYYYMMDD, i Need to have "-" in the date
