0

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.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
DanAthens644
  • 1
  • Why isn't Jerez included in the output? – Alexis Olson Nov 05 '20 at 23:14
  • Hi Alexis, I’m only looking to check the difference in amount where the customer is in both tables. I have excluded from my query the cases, such as Jerez, where a customer is in Table 1, but not Table 2 (or vice-versa), as I have used the LeftAnti merge option for these subsets and as it has been relatively efficient. Thanks – DanAthens644 Nov 06 '20 at 09:38

0 Answers0