Table.FuzzyJoin
TableJoins two tables using fuzzy (approximate) string matching on key columns.
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 tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
table1 | table | Yes | The left table. |
key1 | any | Yes | The key column name or list of column names from table1. |
table2 | table | Yes | The right table. |
key2 | any | Yes | The key column name or list of column names from table2. |
joinKind | number | No | Optional. The join type: JoinKind.Inner (0), JoinKind.LeftOuter (1), JoinKind.RightOuter (2), JoinKind.FullOuter (3), JoinKind.LeftAnti (4), JoinKind.RightAnti (5). Defaults to JoinKind.Inner. |
joinOptions | record | No | Optional record supporting fields: Threshold (0.0–1.0 similarity), IgnoreCase, IgnoreSpace, NumberOfMatches, TransformationTable. |
Return Value
table — A table resulting from joining the two tables using fuzzy key matching.
Remarks
Table.FuzzyJoin joins two tables using approximate string matching on key columns instead of exact equality. It tolerates typos, spacing differences, and case variations, making it useful when joining datasets from different sources where key values may not be standardized — such as customer names entered by hand, product descriptions from different vendors, or city names with varied spellings.
The joinOptions record supports these fields:
- Threshold (number, 0.0–1.0): minimum similarity for a match. Default 0.8. Lower values match more loosely; 1.0 is exact matching only.
- IgnoreCase (logical): ignore case. Default true.
- IgnoreSpace (logical): ignore whitespace. Default true.
- NumberOfMatches (number): limit matches per left-table row. Default is all matches.
- TransformationTable (table with From and To columns): pre-map known variants before scoring.
Table.FuzzyJoin returns a flat joined table (unlike Table.FuzzyNestedJoin, which returns nested tables). It does not fold to data sources. The default join kind is JoinKind.Inner — use JoinKind.LeftOuter to retain unmatched left rows. Be cautious with low Threshold values on large tables as performance can degrade significantly.
Examples
Example 1: Match Sales customer names against a canonical Customers table
let
SalesWithTypos = #table(
{"OrderID", "CustomerName", "Amount"},
{{1,"Alise",250},{2,"Bobb",180},{3,"Charlie",300},{4,"Dianah",150}}
),
Customers = #table(
{"Name", "City", "State"},
{{"Alice","New York","NY"},{"Bob","Chicago","IL"},{"Charlie","Houston","TX"},{"Diana","Seattle","WA"}}
)
in
Table.FuzzyJoin(
SalesWithTypos, "CustomerName",
Customers, "Name",
JoinKind.LeftOuter,
[Threshold = 0.75]
)OrderID | CustomerName | Amount | Name | City | State | |
|---|---|---|---|---|---|---|
| 1 | 1 | Alise | 250 | Alice | New York | NY |
| 2 | 2 | Bobb | 180 | Bob | Chicago | IL |
| 3 | 3 | Charlie | 300 | Charlie | Houston | TX |
| 4 | 4 | Dianah | 150 | Diana | Seattle | WA |
Example 2: Use a TransformationTable to handle known aliases
let
Orders = #table({"CustomerRef","Total"},{{"Al","500"},{"Robbie","200"},{"Diane","350"}}),
Customers = #table({"Name"},{{"Alice"},{"Bob"},{"Diana"}}),
Mapping = #table({"From","To"},{{"Al","Alice"},{"Robbie","Bob"},{"Diane","Diana"}})
in
Table.FuzzyJoin(
Orders, "CustomerRef",
Customers, "Name",
JoinKind.LeftOuter,
[Threshold = 0.5, TransformationTable = Mapping]
)CustomerRef | Total | Name | |
|---|---|---|---|
| 1 | Al | 500 | Alice |
| 2 | Robbie | 200 | Bob |
| 3 | Diane | 350 | Diana |
Example 3: Limit to one match per left row with NumberOfMatches
Table.FuzzyJoin(
#table({"Product"},{{"Widge A"},{"Gadgit B"}}),
"Product",
#table({"CanonicalName","Category"},{{"Widget A","Widgets"},{"Gadget B","Gadgets"}}),
"CanonicalName",
JoinKind.LeftOuter,
[Threshold = 0.7, NumberOfMatches = 1]
)Product | CanonicalName | Category | |
|---|---|---|---|
| 1 | Widge A | Widget A | Widgets |
| 2 | Gadgit B | Gadget B | Gadgets |