Table.FuzzyJoin
Joins two tables on key texts that are spelled alike rather than the same, into one flat table.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.FuzzyJoin(table1 as table, key1 as any, table2 as table, key2 as any, optional joinKind as nullable number, optional joinOptions as nullable record) as table| Parameter | Type | Description |
|---|---|---|
table1 | table | The first (left) table. |
key1 | any | The key column of table1, or a list of them; they must hold texts. |
table2 | table | The second (right) table. |
key2 | any | The key column of table2, or a list: as many as key1. |
joinKind | nullable number optional | JoinKind.Inner when left out; or JoinKind.LeftOuter and the other join kinds. |
joinOptions | nullable record optional | IgnoreCase, IgnoreSpace, Threshold (0.80 unless you give another), NumberOfMatches (how many matches a row may have), SimilarityColumnName (adds the similarity as a column) and TransformationTable. |
Returns
table. table1's columns and then table2's, one row per matching pair.
Remarks
- The key columns must be of type text: set their type first, with
Table.TransformColumnTypesor a table type. - Unlike
Table.FuzzyNestedJoin, the default kind is an inner join: rows without a match are left out. - How two texts are compared, and the few pairs near a threshold that come out otherwise, are in the M functions page's Fuzzy matching section.
Examples
Customer names written differently
Table.FuzzyJoin(
#table(type table [Order = number, Customer = text],
{{1, "Nordvik AS"}, {2, "nordvik as"}, {3, "Fjelstad Bygg"}, {4, "Lunde Handel"}}),
{"Customer"},
#table(type table [Name = text, Segment = text],
{{"Nordvik AS", "Retail"}, {"Fjellstad Bygg", "Trade"}}),
{"Name"})| Order | Customer | Name | Segment |
|---|---|---|---|
| 1 | Nordvik AS | Nordvik AS | Retail |
| 2 | nordvik as | Nordvik AS | Retail |
| 3 | Fjelstad Bygg | Fjellstad Bygg | Trade |
Case is ignored, and one letter missing still matches; Lunde Handel has no match and an inner join leaves it out.
Every order, with the similarity
Table.FuzzyJoin(
#table(type table [Order = number, Customer = text],
{{1, "Nordvik AS"}, {2, "nordvik as"}, {3, "Fjelstad Bygg"}, {4, "Lunde Handel"}}),
{"Customer"},
#table(type table [Name = text, Segment = text],
{{"Nordvik AS", "Retail"}, {"Fjellstad Bygg", "Trade"}}),
{"Name"},
JoinKind.LeftOuter, [SimilarityColumnName = "Similarity"])| Order | Customer | Name | Segment | Similarity |
|---|---|---|---|---|
| 1 | Nordvik AS | Nordvik AS | Retail | 1 |
| 2 | nordvik as | Nordvik AS | Retail | 1 |
| 3 | Fjelstad Bygg | Fjellstad Bygg | Trade | 0.97 |
| 4 | Lunde Handel | null | null | null |