horseyride
Board Regular
- Joined
- Nov 2, 2017
- Messages
- 82
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
<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