Return a Value based on other rows

[kimbtoth]

I am trying to combine data from our old system with our new one. 
The commonality is the account number which will stay the same.  I want to update my spreadsheet so that if there was a date recorded at all for that account number, it will show for all rows for that same account number.  Also, I want it to show the most current date if there are multiple rows for the same account number with dates.  
For example, from the screenshot below, I want all dates for Account 1234 to show with most recent date of 4/5/2022.

Can someone help me figure out if this is possible?  Thanks so much!

[v-yalanwu-msft · Accepted solution]

Hi, @kimbtoth ;

You could try it.

Then expand it and delete it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYxBDgAhCAP/wlkSQJT4FuP/v+GJpdl4a6bT7k1q3amRiSkHK52GzNh5IMs8YfMJAUKksOp0Ve+SvUo9uIHR0Ug4nrP5o+cC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Number" = _t, #"Date ACE Completed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Number", Int64.Type}, {"Date ACE Completed", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account Number"}, {{"maxdate", each List.Max([Date ACE Completed]), type nullable date}, {"a", each _, type table [Account Number=nullable number, Date ACE Completed=nullable date]}}),
    #"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"Date ACE Completed"}, {"a.Date ACE Completed"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded a",{"a.Date ACE Completed"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kimbtoth]

Hi @mussaenda ,
Thanks for getting back to me!  I will google to figure that out since I don't know how to extract the max date.  If I group, don't I lose all the other columns except that column that I am grouped on? 

[mussaenda]

Hi @kimbtoth ,

Exactly what @v-yalanwu-msft showed you.

Let us know if you need more help about this topic.

[mussaenda]

Hi @kimbtoth ,

you can group them by account number then extract the max date ace completed.