Find [Field2] value associated with Max[Date]

[horseyride]

I want to find Text1 associated with Max[Date] (here "cat") and use that to create column Text2 for any matching Text1's

<table>
<tr><td>Date</td><td>Text1</td></tr>
<tr><td>11/1/2017</td><td>dog</td></tr>
<tr><td>11/2/2017</td><td>cat</td></tr>
<tr><td>11/3/2017</td><td>rat</td></tr>
<tr><td>11/7/2017</td><td>dog</td></tr>
<tr><td>11/8/2017</td><td>cat</td></tr>
</table>

Results in:

<table>
<tr><td>Date</td><td>Text1</td><td>Text2</td></tr>
<tr><td>11/1/2017</td><td>dog</td><td>No</tr>
<tr><td>11/2/2017</td><td>cat</td><td>Yes</tr>
<tr><td>11/3/2017</td><td>rat</td><td>No</tr>
<tr><td>11/7/2017</td><td>dog</td><td>No</tr>
<tr><td>11/8/2017</td><td>cat</td><td>Yes</tr>
</table>

I can find the max date List.Max(#"Expanded"[Date]) but can't figure out how to grab the Text1 field
#"Added Custom" = Table.AddColumn(#"Expanded", "Text2", each if [Date]<= (List.Max(#"Expanded"[Date]) then "YES" else "No"),

Thanks

 

[MarcelBeug]

You can use Table.Max, which will give you a record with all fields from the table of the row with the maximum date.
One remark: if you have multiple max dates, you will only get 1 result.

let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "Text2", each if [Text1] = Table.Max(Source, "Date")[Text1] then "Yes" else "Mo")
in
    #"Added Custom"