Filtering Two Columns in Power Query

[MojoGene]

I have a table of "Matters" with a Status column designating whether the Matter is "Open" or "Closed". There is also a StatusDate column showing the date the Status was set.

I want to filter out Matters where the status=Closed and the StatusDate<1/1/2020.

I have tried this, but get the error: "Expression.Error: We cannot apply operator < to types Number and Date."

= Table.SelectRows(#"Filtered Rows3",each ([Status]="Closed" and [StatusDate]<1/1/2020))

  Thanks for any help.

[MojoGene · Accepted solution]

Looks like my brute force approach (try everything until something works) has worked again:

= Table.SelectRows(#"Filtered Rows3", each ([StatusDate]>#date(2020,1,1) and [Status]="Closed") or [Status]="Open" or [Status]="Active")

[edhans · Accepted solution]

Change your original formula to :

Table.SelectRows(#"Filtered Rows3",each ([Status]="Closed" and [StatusDate]<#date(2020,1,1))

Power Query doesn't understand 1/1/2020 in a filter. It has to use the #date() function, or refer to a column or variable that has the data type date set. 

The error is your StatusDate is a date, and 1/1/2020 is a formula dividing 1 by 1 by 2020, and you cannot compare dates to numbers.

[MojoGene · Accepted solution]

Looks like my brute force approach (try everything until something works) has worked again:

= Table.SelectRows(#"Filtered Rows3", each ([StatusDate]>#date(2020,1,1) and [Status]="Closed") or [Status]="Open" or [Status]="Active")