Table.FuzzyJoin

Table

Joins 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 table

Parameters

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]
    )

Applied Steps

The final output — Table.FuzzyJoin matches each misspelled CustomerName to the closest canonical Name using a 0.75 similarity threshold, producing a flat joined table with city and state resolved.

	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]
    )

Applied Steps

The final output — Table.FuzzyJoin applies the TransformationTable mappings first, then resolves each CustomerRef to a canonical Name with a left outer join.

	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]
)

Output

	Product	CanonicalName	Category
1	Widge A	Widget A	Widgets
2	Gadgit B	Gadget B	Gadgets

Compatibility

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

Contributors