Forum Discussion
Post PatronFiltering Two Columns in Power Query
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.
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")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.
2 Replies
- edhans
Community Champion
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.
MojoGeneLooks 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")