Forum Discussion

mshashmi's avatar
mshashmi
Frequent Visitor
9 years ago
Solved

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 ...
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    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

     

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

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's avatar
    mshashmi
    Frequent Visitor
    9 years ago

    v-caliao-msft

     

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

    • v-caliao-msft's avatar
      v-caliao-msft
      Microsoft Employee
      9 years ago

      mshashmi,

       

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

       

      Regards,

      Charlie Liao