Forum Discussion
Filtering Dates help in Power Query please!
Hi there,
I would like to set a filter on a date field in Power Query. The filter I would like to set is following:
Include all dates on or before 30 days from now.
For example if the date today is 02/10/2024. I would like to include all data with dates on or before 01/11/2024.
Thanks!
Hello threw001 ,
Use the following M code to create the column for filtering, This code calculates the date 30 days before today
DateTime.LocalNow() - #duration(30, 0, 0, 0)
After creating this custom column, filter the date field by choosing dates that are less than or equal to the result of the formula
Table.SelectRows(#"Previous Step", each [Date] <= Date.From(DateTime.LocalNow() - #duration(30, 0, 0, 0)))
by this your data will dynamically filter to include all records with a date on or before 30 days from the current date, regardless of when the report is run
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!
Thank You
Dharmendar S
4 Replies
Hello threw001 ,
You can create the filter slicer using dates and set Relative Date style in the slicer settings, this should resolve your issue.
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!
Thank You
Dharmendar S
Hello,
Thanks a lot for your help and apologies I had forgotten (but have now edited the original post) where I need to do this in Power Query as I have table mergers etc.
Hello threw001 ,
Use the following M code to create the column for filtering, This code calculates the date 30 days before today
DateTime.LocalNow() - #duration(30, 0, 0, 0)
After creating this custom column, filter the date field by choosing dates that are less than or equal to the result of the formula
Table.SelectRows(#"Previous Step", each [Date] <= Date.From(DateTime.LocalNow() - #duration(30, 0, 0, 0)))
by this your data will dynamically filter to include all records with a date on or before 30 days from the current date, regardless of when the report is run
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!
Thank You
Dharmendar S
