Table.FuzzyNestedJoin

Table

Joins two tables using fuzzy string matching and produces a nested table column of matches.

Examples on this page use shared sample tables. View them to understand the input data before reading the examples below.

Syntax

Table.FuzzyNestedJoin(table1 as table, key1 as any, table2 as table, key2 as any, newColumnName as text, optional joinKind as nullable number, optional joinOptions as nullable record) as table

Parameters

NameTypeRequiredDescription
table1tableYesThe left table.
key1anyYesThe key column name or list of column names from table1.
table2tableYesThe right table.
key2anyYesThe key column name or list of column names from table2.
newColumnNametextYesThe name for the new nested table column added to table1.
joinKindnumberNoOptional. The join type (JoinKind.Inner, JoinKind.LeftOuter, etc.). Defaults to JoinKind.LeftOuter.
joinOptionsrecordNoOptional record with fuzzy matching options: Threshold, IgnoreCase, IgnoreSpace, NumberOfMatches, TransformationTable.

Return Value

table — The left table with a new column containing nested tables of fuzzy-matched rows from the right table.

Remarks

Table.FuzzyNestedJoin is the fuzzy-matching counterpart of Table.NestedJoin. Instead of requiring exact key equality, it matches keys using approximate string similarity and stores all matching right-table rows in a nested table column. This is useful when you need to inspect all fuzzy matches per left-table row before deciding which to keep, or when you want to count matches rather than flatten them immediately.

The joinOptions record accepts the same fields as Table.FuzzyJoin: Threshold, IgnoreCase, IgnoreSpace, NumberOfMatches, and TransformationTable. The default joinKind is JoinKind.LeftOuter. Use Table.ExpandTableColumn to flatten the nested matches into additional columns.

Prefer Table.FuzzyNestedJoin over Table.FuzzyJoin when you want to handle one-to-many matches explicitly — for example, counting the number of fuzzy matches per row or selecting the best match based on a custom criterion. For a direct flat join without the nested intermediate step, use Table.FuzzyJoin.

Examples

Example 1: Fuzzy nested join — Sales with misspelled names against Customers

let
    Orders = #table(
        {"OrderID", "CustomerName", "Amount"},
        {{1,"Alise",250},{2,"Bobb",180},{3,"Charlei",300},{4,"Diana",150}}
    ),
    Customers = #table(
        {"Name", "City"},
        {{"Alice","New York"},{"Bob","Chicago"},{"Charlie","Houston"},{"Diana","Seattle"}}
    ),
    Joined = Table.FuzzyNestedJoin(
        Orders, "CustomerName",
        Customers, "Name",
        "CustomerMatches",
        JoinKind.LeftOuter,
        [Threshold = 0.75]
    )
in
    Joined
Result
OrderID
CustomerName
Amount
CustomerMatches
11Alise250[Table]
22Bobb180[Table]
33Charlei300[Table]
44Diana150[Table]

Example 2: Expand the nested column to bring in City data

let
    Orders = #table({"OrderID","CustomerName","Amount"},{{1,"Alise",250},{2,"Bobb",180}}),
    Customers = #table({"Name","City","State"},{{"Alice","New York","NY"},{"Bob","Chicago","IL"}}),
    Joined  = Table.FuzzyNestedJoin(Orders,"CustomerName",Customers,"Name","CustomerMatches",JoinKind.LeftOuter,[Threshold=0.75]),
    Expanded = Table.ExpandTableColumn(Joined, "CustomerMatches", {"Name","City","State"}, {"CanonicalName","City","State"})
in
    Expanded
Result
OrderID
CustomerName
Amount
CanonicalName
City
State
11Alise250AliceNew YorkNY
22Bobb180BobChicagoIL

Example 3: Count fuzzy matches per order row

let
    Orders    = #table({"OrderID","Product"},{{1,"Widge A"},{2,"Gadgit B"},{3,"Xyz"}}),
    Products  = #table({"CanonicalName"},{{"Widget A"},{"Widget C"},{"Gadget B"},{"Gadget D"}}),
    Joined    = Table.FuzzyNestedJoin(Orders,"Product",Products,"CanonicalName","Matches",JoinKind.LeftOuter,[Threshold=0.6]),
    WithCount = Table.AddColumn(Joined, "MatchCount", each Table.RowCount([Matches]), type number),
    Result    = Table.RemoveColumns(WithCount, {"Matches"})
in
    Result
Result
OrderID
Product
MatchCount
11Widge A2
22Gadgit B2
33Xyz0

Related Functions

Compatibility

✓ Power BI Desktop✓ Power BI Service✗ Excel Desktop✗ Excel Online✓ Dataflows✓ Fabric Notebooks