Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MojoGene
Post Patron
Post Patron

Filtering 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.

2 ACCEPTED SOLUTIONS
MojoGene
Post Patron
Post Patron

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")

View solution in original post

edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
MojoGene
Post Patron
Post Patron

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")

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors