Merge tables based on Category & closest prior Date/Time

[zoberender]

Hi all - 

I am working on merging 2 tables based on a Category and a Date/Time.  We send out funds to Banks frequently throughout the day.  This data has a Date/Time stamp and the name of the Bank.  It is queried through a DB2 table.  We then receive an acknowledgement email back from the bank confirming the transaction has been processed.  I have no issue consuming the DB2 data and the email data and getting them into tables.  My issue is that I need to join these pieces up based on the Bank Name and the Date/Time.  There can be quite  a time difference between the time the funds are sent and when the acknowledgement is received.  

Assumptions that can be made:

1. An acknowledgement will never be received before the funds are sent.  So the join should be based on the closest sent time that is prior to an acknowledgement.  
2. There will be multiple dates contained in the tables.
3. There may not be an acknowlegement for a specific time.  Eg, a time could just be missing.  Expect a null value returned. 
4. Time difference between sent and ackowledged can be within minutes or take a couple hours, but it would be unexpected for an acknowledgement to be received after the next time funds are sent.
5. Cannot join based on amount as the bank may not acknowledge everything that was sent.  

Tables below are sample data that shows the data on what we sent (TAS_Sent) and what was acknowledged by the bank (Acks_Received).  Final table is what the expected output should look like. 

FILE: Data File (let me know if there are issues accessing)

Can anyone help me with this?  I'd love to share some PowerQuery code of a solution I think is close, but I haven't been able to get anywhere with this 😞

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZI9bgNBCIWvErm2MP8MdEmRNoWLFJbvf40wu4ml2LPSNIM+wXsPbrfT9+fpfCK8CF4YWd6iCIusi0mICk6n+/kVG2Vc4l104kRwXWJZoqUxuw0ZCMOWGHkJFWVXBzoGeGzcx9f7cz+KXZ0guQAfcCTFtoOmqgxCB6A//AaqBERu4PX6EgvvmGcSgY4lNkrHHkvmQAK2Jdax5K86Q1VAXGJzZJZtdsOIwdZTyYq5eMbMnZ6B+f+Y6aGuQ5GtGNZLyyU2A+lHcxtD5thYczLlbTaYhRzI1lyfgJTN7ZKOFEB5Wgb9xYLRb/YLwrZxwE11XjiNDFJzED8A+wxapHZVaUhC8AFohbiDnpoK8rS4h0LGsolZf1uhLjGiktjv3hw7acs1p4V9L2M6Tg0CpDXX8qRwJsh9LwjYh3D/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Bank = _t, #"DateTime Sent" = _t, #"Amount Sent" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Bank", type text}, {"DateTime Sent", type datetime}, {"Amount Sent", Currency.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", (k)=> Table.SelectRows(#"Acks_Received",each [Bank]=k[Bank] and [DateTime Acknowledged]>k[DateTime Sent]){0}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"DateTime Acknowledged", "Acknowledged Amount"}, {"DateTime Acknowledged", "Acknowledged Amount"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "No Answer", each try if #"Expanded Custom"[DateTime Sent]{[Index]+1}<[DateTime Acknowledged] and #"Expanded Custom"[Bank]{[Index]+1}=[Bank] then 1 else 0 otherwise 0),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",each [DateTime Acknowledged],each if [No Answer]=1 then null else [DateTime Acknowledged],Replacer.ReplaceValue,{"DateTime Acknowledged"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Acknowledged Amount],each if [No Answer]=1 then null else [Acknowledged Amount],Replacer.ReplaceValue,{"Acknowledged Amount"}),
    #"Added Custom2" = Table.AddColumn(#"Replaced Value1", "Difference", each [Amount Sent]-[Acknowledged Amount]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"DateTime Acknowledged", type datetime}, {"Acknowledged Amount", Currency.Type}, {"Difference", Currency.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Bank", "DateTime Sent", "Amount Sent", "DateTime Acknowledged", "Acknowledged Amount", "Difference"})
in
    #"Removed Other Columns"

[zoberender]

@lbendlin - Well done.  And I can actually understand what you did there.  Nice straighforward solution.  I just couldn't see it.  Thanks for the help!  

[lbendlin]

Well, I wouldn't call it straightforward exactly, as the rows without ack are only corrected after the assignment. Would be nice if they could be flagged in one go but I haven't figured out yet how to do that.