Table.FuzzyJoin
The operation joins the rows of table1 with the rows of table2 using a fuzzy matching algorithm to compare the values of the key columns specified by key1 (for table1) and key2 (for table2). Fuzzy mat...
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 tableThe operation joins the rows of table1 with the rows of table2 using a fuzzy matching algorithm to compare the values of the key columns specified by key1 (for table1) and key2 (for table2). Fuzzy matching involves comparing similarities in text rather than exact matches.
By default, an inner join is executed, but an optional joinKind can be included to specify a different type of join. Options include:
- InnerJoin
- LeftOuterJoin
- RightOuterJoin
- FullOuterJoin
- LeftAntiJoin
- RightAntiJoin
Another optional set of joinOptions can be included to define how the key columns are compared. Options include:
- ConcurrentRequests: specifies the number of parallel threads (ranging from 1 to 8) to use for fuzzy matching. Default is 1.
- Culture: allows matching based on culture-specific rules by specifying a valid culture name (e.g., "ja-JP" for Japanese). Default is "" for Invariant English culture.
- IgnoreCase: enables case-insensitive key matching. Default is true.
- IgnoreSpace: enables combining text parts f
Perform a left inner fuzzy join between two tables based on the [FirstName] column using the Power Query MTable.FuzzyJoin function. The function takes in two tables with specified columns, matches the [FirstName] values, and performs a left outer join with case-insensitive and space-sensitive comparisons.
The resulting output table includes records with matching [FirstName] values from both tables, along with corresponding columns such as [CustomerID], [Phone], [CustomerStateID], and [State]. Records that do not have a match in the other table are also included in the output with NULL values for non-matching columns.