Forum Discussion
Solution SageComparing two tables on multiple column resulting full match and partial match
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.
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
4 Replies
- jpessoa8
Continued Contributor
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?
mahenkj2Thanks.
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
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