Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hlombard
Frequent Visitor

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.

1 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 PBI_MonthParameter.jpg

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

9 REPLIES 9
ImkeF
Super User
Super User

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Frequent Visitor

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

ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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?

 

Parameterquery.jpg

= 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 PBI_MonthParameter.jpg

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Genius !

 

You make me look good.

Thanks

Dear Team,

 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.