Forum Discussion
Filtering column based on another table | power query
Hi All,
I have the following tables:
Employee
| Name | Date of Birth |
| Andrew | 5/13/1998 |
| Michael | 9/3/1990 |
SalesByCity
| City | Sales | Date |
| Orlando | 2,000,000 | 5/14/2001 |
| Scottsdale | 6,000,000 | 6/15/1980 |
| Tampa | 1,000,000 | 6/19/2005 |
And would love to have this table:
RESULT
| City | Sales | Date |
| Orlando | 2,000,000 | 5/14/2001 |
| Tampa | 1,000,000 | 6/19/2005 |
The "Result" table was generated based on the max date from table "Employee".
I can come up with the same result using this function:
= Table.SelectRows(#"previous step", each [ValueDate] < #date(1998, 5, 13))
Is there a way to have this formula but not using date numbers; meaning, can I change this part "#date(1998, 5, 13))" to a formula that gives my result in the following way:
1) Check max date in Table "Employee"
2) Filter Table 2 to only show anything after the Max Date?
I hope this was clear enough. Thank you in advance.
=let a=List.Max(Employee[Date of Birth]) in Table.SelectRows(PreviousStepName,each [ValueDate]>a)
2 Replies
=let a=List.Max(Employee[Date of Birth]) in Table.SelectRows(PreviousStepName,each [ValueDate]>a)
thank you so much!
