Filter a table

[mshashmi]

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

[v-caliao-msft · Accepted solution]

@mshashmi,

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

[mshashmi]

@v-caliao-msft

I am getting error that my Date.DayofYear is not recognized.

[v-caliao-msft]

@mshashmi,

Power query is case sensitive, you need to use Date.DayOfYear

Regards,

Charlie Liao

[mshashmi]

@v-caliao-msft

Thanks, it worked.