Filtering Dates on Power Query

[numersoz]

Hi,

On power query I have timestamp values (Datetime). First of all I want to dynamically filter this and remove any value before today's date. What would be the way to do this?

The second part that I want to do is, find the maximum value of these time stamps, convert this datetime to date and later use this to filter my callender table on Power Query so that the maximum value in the callender table is the maximum value in my timestamps.

[edhans · Accepted solution]

• Create a blank query and type = DateTime.LocalDateNow() in the formula bar.
• Call it varToday or whatever you want.
• Then go your table and filter by any date. It will have something like < #date(2020,1,1). Replace that #date(2020,1,1) with varToday and it will always be less than today.

To get the max time stamp into a variable like this do the following:

• Create a reference to your query. Right-click and make sure it is not set to load
• Click on teh date field you are interested in and on the Transform menu, select the Date and Latest and it will convert it to a scalar value with List.Max([datevalue])
• Name this query varWhateverYouWant
• Use it as a filter the same way.

I always use dynamic date tables this way. See this article - seems to be exactly what you are doing.

Creating a Dynamic Date Table in Power Query