Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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 😞
Solved! Go to 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"
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"
@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!
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.