Fuzzy relationship between tables?

[General Ledger]

Is there a way to create a "fuzzy" relationship between tables?

For example, the key field in a table might be a person's full name (first and last names with optional middle name or initial). The key field in another table might be last name and first name. Is there a way to create a relationship on these fields and have Power Pivot correctly determine that "John Q. Public" matches to "Public, John"?

 

[Ozeroth]

Hi General Ledger,

You could achieve this by setting up a lookup table that contains two columns: Name Variant and Name Unique.
Name Variant contains all possible variants of each name that might appear in the fact table (constructed manually or according to some rules) and Name Unique contains the unique version of each name.

Then your fact table's Name column would be related to the lookup table's Name Variant column, and you could filter on Name Unique or Name Variant as desired.

For example (blue columns are related):
Fact Table:

Name	Sales
John Q. Public	1
John Public	2
Public, John	3
Abel Gideon	4
A Gideon	5

<colgroup><col><col></colgroup><tbody>
</tbody>

Lookup Table:

Name Variant	Name Unique
John Q. Public	Public, John
John Public	Public, John
Public, John	Public, John
Abel Gideon	Gideon, Abel
A Gideon	Gideon, Abel
Dr Abel Gideon	Gideon, Abel
Gideon, Abel	Gideon, Abel

<colgroup><col><col></colgroup><tbody>
</tbody>