Joining Dirty Datasets with Power Query Fuzzy Matching

Question

You need to merge a Vendor Invoice table with an Approved Vendor Master list. Company names in the invoices have typos (e.g. 'Microsoft Corp', 'Microsoft Corporation', 'Micro soft'). VLOOKUP fails. Explain how Power Query's Merge Queries with Fuzzy Matching solves this.

Accepted Answer

Optimal Solution:
Power Query > Home > Merge Queries:
1. Select Invoice Table & Master Table
2. Select Vendor Columns
3. Check: 'Use fuzzy matching to perform the merge'
4. Fuzzy matching options: Similarity Threshold = 0.80

Technical Explanation:
Power Query utilizes Jaccard similarity algorithms to compute a similarity coefficient between 0.00 and 1.00. Setting the threshold to 0.80 matches minor variations without false-positive mismatches.

Invoice Table Vendor	Master List Approved Vendor
Microsoft Corp	Microsoft Corporation
General Electric Inc.	General Electric Co.
Amazon.com LLC	Amazon

Matched Invoice Vendor	Resolved Master Vendor	Similarity Score
Microsoft Corp	Microsoft Corporation	0.92
General Electric Inc.	General Electric Co.	0.88

	A	B	E
1	Invoice Table Vendor	Master List Approved Vendor
2	Microsoft Corp	Microsoft Corporation	Target [Enter Formula]
3	General Electric Inc.	General Electric Co.
4	Amazon.com LLC	Amazon
5
6
7