Forum Discussion
Find closest date before transaction date based on unique ID
Hi asimpson22
You may create a new query with below code.
let Source = Table.NestedJoin(CancelTable, {"SalesOffice", "Lot", "UniqueID"}, SalesTable, {"SalesOffice", "Lot", "UniqueID"}, "SalesTable", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(Source, "Custom", each let __CancelDate = [CancelDate] in Table.FirstN(Table.Sort(Table.SelectRows([SalesTable], each [Sale Date] <= __CancelDate), {{"Sale Date", Order.Descending}}),1)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"SalesOffice", "Lot", "UniqueID", "Transaction Type", "CancelDate", "Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Sale Date"}, {"Orig. Sale Date "}) in #"Expanded Custom"Result:
The problem is that from the current sample data, we cannot know which one is a record of a lot cancelling and then reselling on the same day, and which one is a record of a lot resold and then cancelled on the same day. There is no difference in the sample data. So you will find that for the example of Sales Office D, the output is not meeting your expected result.
I have attached a sample file at bottom.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!
Thanks for your reply! Here are some sample tables and descriptions below. The first 2 tables are what I'm working with and the third table is the result I'm looking for.
| SalesOffice | Lot | UniqueID | Transaction Type | CancelDate |
| A | 1 | A1 | Cancel | 12/2/2022 |
| B | 2 | B2 | Cancel | 12/2/2022 |
| C | 3 | C3 | Cancel | 12/2/2022 |
| A | 1 | A1 | Cancel | 11/1/2022 |
| D | 4 | D4 | Cancel | 10/1/2022 |
| E | 5 | E5 | Cancel | 9/1/2022 |
| E | 5 | E5 | Cancel | 8/15/2022 |
| SalesOffice | Lot | UniqueID | Transaction Type | Sale Date |
| A | 1 | A1 | Gross Order | 11/15/2022 |
| B | 2 | B2 | Gross Order | 2/1/2022 |
| C | 3 | C3 | Gross Order | 3/1/2022 |
| A | 1 | A1 | Gross Order | 10/1/2022 |
| D | 4 | D4 | Gross Order | 10/1/2022 |
| D | 4 | D4 | Gross Order | 9/1/2022 |
| E | 5 | E5 | Gross Order | 9/1/2022 |
| E | 5 | E5 | Gross Order | 8/1/2022 |
| SalesOffice | Lot | UniqueID | Transaction Type | CancelDate | Orig. Sale Date |
| A | 1 | A1 | Cancel | 12/2/2022 | 5/25/2022 |
| B | 2 | B2 | Cancel | 12/2/2022 | 2/17/2022 |
| C | 3 | C3 | Cancel | 12/2/2022 | 1/17/2022 |
| A | 1 | A1 | Cancel | 11/1/2022 | 10/1/2022 |
| D | 4 | D4 | Cancel | 10/1/2022 | 9/1/2022 |
| E | 5 | E5 | Cancel | 9/1/2022 | 9/1/2022 |
| E | 5 | E5 | Cancel | 8/15/2022 | 8/1/2022 |
Sales Offices A - C are the typical transaction examples with 1 sale and 1 cancel.
Sales Office D is an example of a lot cancelling and then reselling on the same day.
Sales Office E is an example where there were multiple cancellations. This is also an example where a lot resold and then cancelled on the same day.
Please let me know if this helps or if any additional detail would be helpful!
Community SupportHi asimpson22
You may create a new query with below code.
let
Source = Table.NestedJoin(CancelTable, {"SalesOffice", "Lot", "UniqueID"}, SalesTable, {"SalesOffice", "Lot", "UniqueID"}, "SalesTable", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let __CancelDate = [CancelDate] in Table.FirstN(Table.Sort(Table.SelectRows([SalesTable], each [Sale Date] <= __CancelDate), {{"Sale Date", Order.Descending}}),1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"SalesOffice", "Lot", "UniqueID", "Transaction Type", "CancelDate", "Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Sale Date"}, {"Orig. Sale Date
"})
in
#"Expanded Custom"Result:
The problem is that from the current sample data, we cannot know which one is a record of a lot cancelling and then reselling on the same day, and which one is a record of a lot resold and then cancelled on the same day. There is no difference in the sample data. So you will find that for the example of Sales Office D, the output is not meeting your expected result.
I have attached a sample file at bottom.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!