Forum Discussion
Filter a table
Hi I have a table. It has a date column. Now I want to filter the rows in such a way that I am left with only past 5 dates present in the table.
Suppose:
Date
12 Feb, 2017
11 June, 2017
9 May, 2017
31 May, 2017
24 March, 2017
19 Jan, 2017
26 April, 2017
Now my filtered table should display only past 5 dates and rest rows should be either hidden or removed from the table.
Like I want dates from 9 May, 2017 to 11 June, 2017 to be present and 19 Jan, 2017 and 12 Feb, 2017 to be removed.
Thanks
You can edit your query, add a custom column on it and filter the new column.
Add a custom column
= Table.AddColumn(#"Changed Type", "Custom.2", each Date.DayOfYear(DateTime.LocalNow())-Date.DayOfYear([Date]))
Filter this column= Table.SelectRows(#"Added Custom2", each [Custom.2] >= 0 and [Custom.2] <= 4)
Regards,
Charlie Liao
4 Replies
You can edit your query, add a custom column on it and filter the new column.
Add a custom column
= Table.AddColumn(#"Changed Type", "Custom.2", each Date.DayOfYear(DateTime.LocalNow())-Date.DayOfYear([Date]))
Filter this column= Table.SelectRows(#"Added Custom2", each [Custom.2] >= 0 and [Custom.2] <= 4)
Regards,
Charlie Liao
