Relative Date Parameter in Power Query

[ToddChitt]

Is there a way to configure a Parameter in Power Query so that it is RELATIVE (to the current date or date of the refresh)?

I have some tables with several million rows and would like to limit it to just the last few months. Sure, I can set up a Query Parameter and reference it in all the queries, but it's a hard coded date. Left un-touched, a year from now it will be back up to several million rows. 

Currently, the only option is to open the model, get into Power Query, adjust the date, Close and Apply, and Deploy.

It would be great if I could set up a Query Parameter that was constantly rolling forward, say three months ago today.

I see in the Query Parameter setup that the Suggested Values can come from a Query, but nothing seems to work there. 

I was thinking of writing a custom M query that always returned the date three months ago today, then use that in the Suggested Values Query, but can't seem to get it to work. How do you set up a Query in Parameters? Is it broken?

I guess the only options are:

* If using TSQL, write the custome query with a WHERE clause and DATEADD(MONTH, -3, GETDATE() ) expresison.

* In each query, put in a line similar to the following: #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Date] > Date.AddDays ( DateTime.LocalNow(), -90 ) )

Any suggestions on doing this in a Query Paramter that can then be referenced in multiple places (queries) ?

Thanks

[jvaught]

The trick is to create a text parameter then convert it to a date query and then reference the query in your other queries.

Here are the detailed steps:

You have this same filter in multiple queries and you'd like to parameterize it.

#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Date] > Date.AddDays ( DateTime.LocalNow(), -90 ) )

But if we create a parameter for the relative date like this:

  

and use that parameter in the query like this:

  #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Date] > StartDate )

it generates the following error:

  

Expression.Error: We cannot apply operator < to types Text and DateTime. Details: Operator=< Left=Date.AddDays ( DateTime.LocalNow(), -90 ) Right=1/1/1984 12:00:00 AM

This can be solved by converting the parameter to a query. Right-click the parameter and select "Convert to Query"

   

There is one more thing we need to do. Just after the conversion the query is a text type and looks like this

   

   

So we need to make sure it evaluates as a date (or datetime in this case). All we need to do is add an "=" to the step editor, or remove the quotes in the advanced editor.

   

   

Now the query results in a datetime value and evaluates correctly when referenced in other queries.

 #"Filtered Jan 1 2018 - 6 weeks future" = Table.SelectRows(#"Renamed Columns", each [Date] > StartDate )

   

[v-yuezhe-msft]

@ToddChitt,

You can check my reply in this similar thread about how to set up a Query in Parameters. However, if you need to use the parameter to filter rows in tables in query editor, you would need to manually enter current value each day.



In your scenario, I would recommend you add the line  #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Date] > Date.AddDays ( DateTime.LocalNow(), -90 ) ) in each query.


Regards,
Lydia