Excel
All Top 50 Excel & Business Modeling Questions#36
Joining Dirty Datasets with Power Query Fuzzy Matching
MediumKPMGInterview Question #36
Asked at KPMGYou 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.
Input Table: TablesToMerge
3 rows preview| Invoice Table Vendor | Master List Approved Vendor |
|---|---|
| Microsoft Corp | Microsoft Corporation |
| General Electric Inc. | General Electric Co. |
| Amazon.com LLC | Amazon |
Expected Output Structure2 rows
| Matched Invoice Vendor | Resolved Master Vendor | Similarity Score |
|---|---|---|
| Microsoft Corp | Microsoft Corporation | 0.92 |
| General Electric Inc. | General Electric Co. | 0.88 |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 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 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
This question demonstrates a technique that can't be graded as a single cell formula (it needs another sheet, a real PivotTable, or compares two methods side-by-side). Read the explanation below instead.