Reconcile customer, company, or supplier lists when names are similar but not identical. Use tested prompts to configure Power Query fuzzy matching, review ambiguous candidates, and handle systematic abbreviations without changing the source tables.
I have two Excel tables containing customer names from different systems. The companies should mostly correspond, but the names are not written exactly the same way. ### Table 1 — SalesCustomers | Customer ID | Customer Name | | C001 | Acme Corporation | | C002 | Northstar Technologies | | C003 | Green Valley Foods | | C004 | Baker & Sons Limited | | C005 | Horizon Consulting Group | | C006 | Metro Office Supplies | ### Table 2 — AccountingCustomers | Account ID | Customer Name | | A101 | Acme Corp | | A102 | Northstar Technology | | A103 | Green Valley Food | | A104 | Baker and Sons Ltd | | A105 | Horizon Consulting | | A106 | Metro Supplies | The tables are already formatted as Excel Tables and are in the same workbook. I need to match each row in SalesCustomers to the most likely customer in AccountingCustomers, even when the names are similar but not identical. Please use Excel Power Query fuzzy matching, not VBA and not the old Microsoft Fuzzy Lookup Add-In. Please answer in exactly these 6 sections: 1. Power Query setup — Give the exact steps to load both Excel Tables into Power Query and create the fuzzy merge. 2. Fuzzy matching settings — Explain which columns to select, which join type to use, whether Use fuzzy matching to perform the merge should be enabled, and suggest a reasonable starting similarity threshold. Do not imply that one threshold is universally correct. 3. Expected matches — Show the most reasonable expected match for each of the six rows in SalesCustomers. 4. How to inspect match quality — Explain how I can view or return the similarity score, if supported by the Power Query fuzzy merge options, and how that score should be interpreted. 5. False positives and manual review — Explain why fuzzy matching should not automatically be treated as proof that two companies are the same. Tell me what should happen with low-confidence or ambiguous matches. 6. Original data and limitations — Confirm whether this approach can leave both original Excel tables unchanged. Briefly explain important limitations of Power Query fuzzy matching, including whether availability can depend on the Excel version/platform. Do not invent Excel functions or Power Query features. If a requested option is not available in all supported Excel versions, say so clearly.
Use this when two lists contain the same customers or companies but the names differ through suffixes, plural forms, punctuation, or missing words.
I have two Excel tables containing supplier names from two different systems. Several names are similar, but some matches are ambiguous because multiple suppliers have nearly the same name. ### Table 1 — PurchaseSuppliers | Supplier ID | Supplier Name | | S001 | Horizon Consulting | | S002 | Greenfield Logistics | | S003 | Metro Office Solutions | | S004 | North Star Technology | | S005 | Apex Industrial Group | ### Table 2 — MasterSuppliers | Vendor ID | Supplier Name | | V101 | Horizon Consulting LLC | | V102 | Horizon Consulting Europe | | V103 | Greenfield Logistics Ltd | | V104 | Metro Office Solution | | V105 | Northstar Technologies | | V106 | Apex Industrial | | V107 | Apex Industrial Services | Both datasets are Excel Tables in the same workbook. I want to use Power Query fuzzy matching, but I do NOT want Excel to silently choose a supplier when more than one candidate is plausible. Please answer in exactly these 6 sections: 1. Fuzzy merge configuration — Give the Power Query merge setup, including the join type, columns to match, and a reasonable starting similarity threshold. 2. Return multiple candidates — Explain how to configure Power Query so that it can return more than one potential match for each supplier instead of keeping only the single strongest match. Use a maximum of 3 candidates per input row. 3. Expected candidates — For each row in PurchaseSuppliers, identify the logically plausible candidate or candidates from MasterSuppliers. Clearly distinguish unambiguous cases from ambiguous cases. 4. Ambiguous matches — Specifically evaluate Horizon Consulting and Apex Industrial Group. Explain why automatically accepting the highest fuzzy-match score could be unsafe for these names. 5. Similarity threshold and false positives — Explain what can happen if the similarity threshold is set too low or too high. Do not claim that a specific similarity score proves that two supplier records are the same company. 6. Safe reconciliation workflow — Recommend a practical workflow for separating strong matches from records requiring manual review while leaving both source tables unchanged. Do not invent similarity scores. I want the expected candidates based on the supplied data, but actual scores must be treated as values calculated by Power Query. Do not use VBA or the old Microsoft Fuzzy Lookup Add-In.
Use this when several master records share the same core company name and you need to inspect competing candidates instead of auto-accepting the top result.
I have two Excel tables containing company names from different systems. Some differences are not simple typos. One system regularly uses abbreviations such as Intl, Co, and Svc, while the other system uses the full words. ### Table 1 — ImportedCustomers | Customer ID | Customer Name | | C001 | Global Intl Trading | | C002 | West Coast Svc Co | | C003 | Summit Mfg Group | | C004 | Atlantic Tech Solutions | | C005 | Central Distribution Co | ### Table 2 — CustomerMaster | Account ID | Customer Name | | A101 | Global International Trading | | A102 | West Coast Service Company | | A103 | Summit Manufacturing Group | | A104 | Atlantic Technology Solutions | | A105 | Central Distribution Company | Both datasets are Excel Tables in the same workbook. I want to reconcile them using Power Query fuzzy matching. Because the abbreviations are systematic, I also want to know whether a Power Query transformation table can be used to help the fuzzy matcher understand known equivalents such as: | From | To | | Intl | International | | Svc | Service | | Co | Company | | Mfg | Manufacturing | | Tech | Technology | Please answer in exactly these 6 sections: 1. Why ordinary fuzzy matching may be insufficient — Explain why systematic abbreviations can be different from ordinary spelling mistakes and why relying only on a lower similarity threshold may be risky. 2. Transformation table setup — Explain how to create the transformation table in Excel and load it into Power Query. State the required column names if Power Query requires specific names. 3. Fuzzy merge configuration — Give the exact merge setup for ImportedCustomers and CustomerMaster, including join type, matching columns, fuzzy matching, a reasonable starting threshold, and how to select the transformation table. 4. Expected matches — Show the expected match for C001 through C005. Do not invent similarity scores. 5. What the transformation table actually does — Explain how mappings such as Intl → International and Co → Company affect fuzzy matching. Clarify whether the source Excel Tables themselves are modified. 6. Limitations and safe use — Explain important limitations of transformation tables, including what can happen if an abbreviation has more than one possible meaning. Explain when manual review is still necessary. Do not use VBA or the Microsoft Fuzzy Lookup Add-In. Do not replace the source values permanently. The solution should remain non-destructive. Do not claim that transformation tables guarantee a correct business-entity match.
Use this when abbreviations are systematic and known in advance, such as Intl vs International or Co vs Company.
| Sales customer | Accounting value | Exact match |
|---|---|---|
| Acme Corporation | Acme Corp | No |
| Northstar Technologies | Northstar Technology | No |
| Baker & Sons Limited | Baker and Sons Ltd | No |
| Metro Office Supplies | Metro Supplies | No |
| Sales ID | Expected candidate | Review |
|---|---|---|
| C001 | Acme Corp | Plausible ✓ |
| C002 | Northstar Technology | Plausible ✓ |
| C003 | Green Valley Food | Plausible ✓ |
| C004 | Baker and Sons Ltd | Plausible ✓ |
| C005 | Horizon Consulting | Plausible ✓ |
| C006 | Metro Supplies | Plausible ✓ |
Use a Left Outer fuzzy merge on Customer Name. Start around the documented 0.80 threshold, then inspect the actual similarity scores and business context. A fuzzy score is a review signal — not proof of identity.
| Purchase supplier | Possible master records | Risk |
|---|---|---|
| Horizon Consulting | Horizon Consulting LLC Horizon Consulting Europe | Ambiguous |
| Greenfield Logistics | Greenfield Logistics Ltd | Clearer |
| Apex Industrial Group | Apex Industrial Apex Industrial Services | Ambiguous |
| Supplier | Candidate handling | Status |
|---|---|---|
| Horizon Consulting | Review LLC vs Europe using another identifier | Manual review |
| Greenfield Logistics | One logical candidate: Greenfield Logistics Ltd | Strong candidate |
| Metro Office Solutions | Metro Office Solution | Strong candidate |
| North Star Technology | Northstar Technologies | Strong candidate |
| Apex Industrial Group | Review Industrial vs Industrial Services | Manual review |
Set Number of matches to 3 during review. Use similarity scores to rank candidates, then verify ambiguous records with tax ID, address, country, vendor number, or another reliable identifier.
| Imported name | Master name | Known difference |
|---|---|---|
| Global Intl Trading | Global International Trading | Intl |
| West Coast Svc Co | West Coast Service Company | Svc / Co |
| Summit Mfg Group | Summit Manufacturing Group | Mfg |
| Atlantic Tech Solutions | Atlantic Technology Solutions | Tech |
| From | To | Expected result |
|---|---|---|
| Intl | International | Global International Trading ✓ |
| Svc | Service | West Coast Service Company ✓ |
| Co | Company | Central Distribution Company ✓ |
| Mfg | Manufacturing | Summit Manufacturing Group ✓ |
| Tech | Technology | Atlantic Technology Solutions ✓ |
Create a Power Query transformation table with columns named From and To. Select it in the fuzzy merge options. The source Excel tables remain unchanged; mappings affect the fuzzy comparison only.
Load each Excel Table into Power Query and create the reconciliation as a separate query. This preserves the original customer or supplier values while you test different matching settings.
Select the company-name column in both queries, enable fuzzy matching, and begin around the documented default threshold of 0.80. Treat that value as a starting point, not a universal rule.
Show similarity scores when available and, for reconciliation work, consider returning more than one candidate. A high text-similarity score does not prove two records belong to the same legal entity.
If one system consistently stores terms such as Intl, Co, or Mfg, use a Power Query transformation table with From and To columns instead of simply lowering the threshold.
Send uncertain results to manual review and compare tax ID, address, country, vendor number, email domain, or another reliable key before approving the mapping.
0.80 as its documented default, but there is no universal best threshold. Lower values return more candidates and can increase false positives; higher values are stricter and can miss legitimate name variations.Horizon Consulting could correspond to more than one master record.From and To fields that supplies known equivalents such as Intl → International. It helps with controlled abbreviations before fuzzy comparison, but it does not prove that two records represent the same real-world company.