Forum Discussion
Return a Value based on other rows
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!
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.
4 Replies
Hi kimbtoth ,
Exactly what v-yalanwu-msft showed you.
Let us know if you need more help about this topic.
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.
