cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mork
Helper V
Helper V

Filter rows in query editor up to current date

I have a table that includes a column with the date of the first day of each week for the whole 2016. I want to filter the rows of that table inside query editor in a way that it will keep only the rows up to the current date.

How can I do that inside the query editor?

1 ACCEPTED SOLUTION
KHorseman
Community Champion
Community Champion

Short of typing in all the code in the advanced editor yourself...

 

Hit the filter dropdown on the column and go to Date Filters, pick "Before...", then in the input box that pops up change that to "is before or equal to..." Type in today's date or whatever arbitrary date (this is just to get the editor to give you the code. You'll change this in a moment so the date doesn't really matter) and hit enter.

 

Now go to the formula bar. You should see something that looks like:

= Table.SelectRows(#"Replaced Value", each [startdate] <= #date(2016, 5, 9))

Replace the date part of that with Date.From(DateTime.LocalNow())

 

= Table.SelectRows(#"Replaced Value", each [startdate] <= Date.From(DateTime.LocalNow()))

Now your query will always be filtered to the current day or earlier.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
KHorseman
Community Champion
Community Champion

Short of typing in all the code in the advanced editor yourself...

 

Hit the filter dropdown on the column and go to Date Filters, pick "Before...", then in the input box that pops up change that to "is before or equal to..." Type in today's date or whatever arbitrary date (this is just to get the editor to give you the code. You'll change this in a moment so the date doesn't really matter) and hit enter.

 

Now go to the formula bar. You should see something that looks like:

= Table.SelectRows(#"Replaced Value", each [startdate] <= #date(2016, 5, 9))

Replace the date part of that with Date.From(DateTime.LocalNow())

 

= Table.SelectRows(#"Replaced Value", each [startdate] <= Date.From(DateTime.LocalNow()))

Now your query will always be filtered to the current day or earlier.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

You Sir are a hero among men. This worked for me I was doing a filter in the Query to get data for each date >= TodaysDate

Replace the date part of that with Date.From(DateTime.LocalNow())

 

= Table.SelectRows(#"Replaced Value", each [startdate] <= Date.From(DateTime.LocalNow()))

Now your query will always be filtered to the current day or earlier.


 

To the current day and time, to be precise.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!