Hi all,
I have the following problem. I have 2 tables, which have the following approximate format:
Table 1 (selection of columns)
Email Transaction ID Date Payment processor Status
john@gmail 0001 01/02/2018 PaymentYellow Paid
john@gmail cd_03A 01/03/2018 PaymentBlack Paid
john@gmail cd_03B 01/04/2018 PaymentBlack Paid
john@gmail cd_03B 01/04/2018 PaymentBlack Refund
Table 2 (all columns):
Email Transaction ID Date Payment processor Transaction number
john@gmail 0001 01/02/2018 PaymentYellow 0
john@gmail cd_03A 01/03/2018 PaymentBlack 1
john@gmail cd_03B 01/04/2018 PaymentBlack 2
The transaction number in table 2 is correctly calculated after the following indexing:
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Email"}, {{"Count", each Table.AddIndexColumn(_,"Index2",0,1), type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"PaymentProcessor", "Transaction Id", "Date", "Index2"}, {"PaymentProcessor", "Transaction ID", "Date", "Transaction number"}),
The problem happens after the merge. After merging based on any combination of keys, trimmed, cleaned, transformed all to text, the transaction number, in around 25% of cases is not correctly transfered from Table 2 to Table 1 (left outer merge, with table 1 being on the left - I've tried multiple join options). No duplicate rows, most numbers get transported, but a significant part of them get incorrectly transported, with no obvious (to me) pattern, until now.
Please help, I've been losing a lot of sleep over this.
The expected result would be:
Table 1 (after merge and expand)
Email Transaction ID Date Payment processor Status Transaction number
john@gmail 0001 01/02/2018 PaymentYellow Paid 0
john@gmail cd_03A 01/03/2018 PaymentBlack Paid 1
john@gmail cd_03B 01/04/2018 PaymentBlack Paid 2
john@gmail cd_03B 01/04/2018 PaymentBlack Refund 2
