As part of a migration project, I’m looking to reconcile two fact tables (high cardinality with approx 500k rows each- there are a lot of customer accounts and it has to be reconciled on a customer account basis ). There is a many-to-many relationship between customer columns in the two tables.
I am struggling to find an efficient way to output the customers that appear in both tables but have a difference in the value.
I’ve tried merge in Power Query but it is extremely slow- perhaps due to the volume and high cardinality factor.
I would welcome any advice on how to produce the desired output efficiently?
Input Table 1:
Customer | Type | Channel | Loan
Jones | A | Branch | 100
Taylor | B | Phone | 200
Taylor | B | Online | 60
Jerez | C | Online | 120
Murray | D | Phone | 90
Input Table 2:
Customer | Type | Loan
Jones | A | 81
Taylor | B | 285
Jerez | C | 80
Jerez | C | 40
Seinfeld | A | 140
Desired Output:
Customer is in both tables, but the difference is in loan:
Customer | Type1 | Loan1 | Loan2
Jones | A | 100 | 81
Taylor | B | 260 | 285
- where Loan 1 is the loan stated in Table 1; and Loan 2 is the loan stated in Table 2.
Thanks for taking the time to look at this question.