Comparing two tables on multiple column resulting full match and partial match

[mahenkj2]

Hi,

I am trying to find a solution for below scenario:

There are two table. 

Table-1
ID	Name	Invoice Number	Invoice amount	Tax rate	Tax amount
AA0	John	Aax/2359	10400	5%	520
AB0	Albert	Bbc/23	15200	12%	1824
AA0	John	CCd/23	15000	18%	2700
BB0	Stephy	BFG/542	28000	18%	5040

Table-2
ID	Name	Invoice Number	Invoice amount	Tax rate	Tax amount
AA0	John	2359	10400	5%	520
AB0	Albert	Bbc/23	15200	12%	1824
AA0	John	CCd/23	15000	5%	750
CDF	Kelley	ASG/5423	1200	5%	60

Aim is to find the match in both the tables by comparing ID+Name+Invoice Number +Invoice Amount+Tax rate. If such combinations are matched in both the tables, it will be like 'full match'.

But if say for one entry, invoice number is wrong due to some reason, but still ID+Name+Invoice Amount+Tax rate are matching and invoice number is partially matching, we will term as 'partial match'.

It is also possible that, one table have some entry which is not present in another, then we term it as 'Not in Table-1' or 'Not in Table-2'.

I reproduce the expected output as well below:

Intended result

Table-1 data

Table-2 data

Difference

ID

Name

Table-1-Invoice number

Table-1-Invoice amount

Table-1-Tax rate

Table-1-Tax amount

Table-2-Invoice Number

Table-2-Invoice amount

Table-2-Tax rate

Table-2-Tax amount

Result

Invoice number

Invoice amount

Tax rate

Tax amount

AA0

John

Aax/2359

10400

5%

520

2359

10400

0.05

520

Partial match

Mismatch

AB0

Albert

Bbc/23

15200

12%

1824

Bbc/23

15200

12%

1824

Match

AA0

John

CCd/23

15000

18%

2700

CCd/23

15000

5%

750

Partial match

Mismatch

Mismatch

BB0

Stephy

BFG/542

28000

18%

5040

Not in Table-1

CDF

Kelley

ASG/5423

1200

5%

60

Not in Table-2

Similary it is possible that ID+Name+Invoice Number +Invoice Amount are matching for an entry in both the tables but tax rate is wrong/mismatch and we mark it as partial match for Tax rate mismatch.

So, it is kind of comparing multiple columns.

I have done much work on this problem in power query but multiple column comparison is a challenge for me so far.

Any help is much appreciated.

[jpessoa8]

Hi @mahenkj2 ,

I think I'll be able to create a solution in PowerQuery based on GroupBy's. The issue is, when you have partial matches, since you will only display 1 row, which criteria should be use to identify?

Table1

AA0

John

Aax/2359

10400

5%

520

Table2

AA0

John

2359

10400

5%

520

The difference is on Invoice Number. Which one is going to be displayed?

[mahenkj2]

Thanks.

Since there is a partial match in invoice number, but other things are matching, so I would like the result as shown in intended result:

Table-1 Invoice number is Aax/2359, whereas table-2 shows 2359, and since other items ID+Name+Tax rate is matching in both table. The result will be a partial match.

[jpessoa8 · Accepted solution]

@mahenkj2 ,

The key to this issue is the usage of the Fuzzy Merge. This allow the Merge of the two tables by the fields ID, Name and Invoice Number but allowing a match of at least 80% (value by default but can be edited) and ignoring case and spaces.

With this Fuzzy Merge with a Full Outer kind you can afterwards compare each combinations and also find if a record is only on one the tables:

Final result:

Here is a link with the PowerBI file used : https://we.tl/t-DDvMm0z6Wb

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa

[mahenkj2]

Hi @jpessoa8 ,

I am giving it a try on real dataset, but after looking at your example, I feel thats good way to do it.

Thanks again.