With Power Query,
1. Load your data table to the PQ Editor and employ the following Mcode to get the max sale date
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Lot", Int64.Type}, {"unit", type text}, {"sale date", type date}, {"agreement sales price", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"unit"}, {{"Last Sale Date", each List.Max([sale date]), type nullable date}})
in
#"Grouped Rows"
2. Load the data table to the PQ Editor again but with the query name of Table1 (2) and employ the following Mcode
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Lot", Int64.Type}, {"unit", type text}, {"sale date", type date}, {"agreement sales price", Int64.Type}})
in
#"Changed Type"
3. Join the two queries with the following Mcode
let
Source = Table.NestedJoin(#"Table1 (2)", {"unit", "sale date"}, Table1, {"unit", "Last Sale Date"}, "Table1", JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Last Sale Date"}, {"Last Sale Date"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table1", each ([Last Sale Date] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Last Sale Date"})
in
#"Removed Columns"
4. Close and Load your final result to Excel
Excel 2016 (Windows) 64 bit
|
G |
H |
I |
J |
8 |
Lot |
unit |
sale date |
agreement sales price |
9 |
5 |
a |
2/15/2024 |
235000 |
10 |
10 |
b |
10/15/2023 |
267000 |
11 |
11 |
c |
1/15/2024 |
234999 |
12 |
12 |
d |
2/1/2024 |
185000 |
Bookmarks