Sue Bayes
January 13, 2022
Sometimes you might want to apply a specific logic to filtering a date column either by using another column or another relative date. For example, only show me items that have happened within the last 45 days. If you want to do this, then Date.AddDays is your friend.
Example Query = Table.AddColumn(PreviousStepName, “NewColumnName”, each if [Column you want to filter] = null then “logic for null columns” else if [Column you want to filter] > Date.AddDays([Column you want to use to define filter], -45) then “logic for recent rows” else “logic for past data”, type text).
You need to build in logic first for those rows that may contain null values
If you want to use Today() or any date Relative to today, then you need to create a new query in Power Query. You can then reference that query in your existing query. To use today as a new query, the code is Date.From(DateTime.LocalNow()). If you want to create a relative date, for example, 45 days from Today put Date.AddDays(Date.From(DateTime.LocalNow()), -45). The sign before the number indicates whether it goes forward or back.
You can then use the query as a logic within your new column to filter, for example, = Table.SelectRows(#”Filtered Rows1″, each [Implemented] > #”Today – 45″).
This would only show me rows that have been Implemented within the last 45 days from today.
Any questions, happy to help,
Sue
14/01/2022
