Table.FuzzyJoin
Joins the rows from the two tables that fuzzy match based on the given keys.
function (table1
as table, optionalkey1
as nullable any,table2
as table, optionalkey2
as nullable any, optionaljoinKind
as nullable any, optionaljoinOptions
as nullable record) as table
Joins the rows of
table1
with the rows of table2
based on a fuzzy matching of the values of the key columns selected by key1
(for table1
) and key2
(for table2
).Fuzzy matching is a comparison based on similarity of text rather than equality of text.
By default, an inner join is performed, however an optional
joinKind
may be included to specify the type of join. Options include: JoinKind.Inner
JoinKind.LeftOuter
JoinKind.RightOuter
JoinKind.FullOuter
JoinKind.LeftAnti
JoinKind.RightAnti
</p>
An optional set of
joinOptions
may be included to specify how to compare the key columns. Options include: ConcurrentRequests
Culture
IgnoreCase
IgnoreSpace
NumberOfMatches
Threshold
TransformationTable
</p>
The following table provides more details about the advanced options.
Advanced Option | Default | Allowed | Description |
ConcurrentRequests | 1 | Between 1 and 8 | The ConcurrentRequests option supports parallelizing the join operation by specifying the number of parallel threads to to use. |
Culture | Culture neutral | A valid culture name | The Culture option allows matching records based on culture-specific rules.
For example a Culture option of 'ja-JP' matches records based on the Japanese language. |
IgnoreCase | true | true or false | The IgnoreCase option allows matching records irrespective of the case of the text.
For example, 'Grapes' (sentence case) is matched with 'grapes' (lower case) if the IgnoreCase option is set to true. |
IgnoreSpace | true | true or false | The IgnoreSpace option allows matching records ignoring the spaces in the string.
For example, 'Grapes' is matched with 'Grape s' (space before 's') if the IgnoreSpace option is set to true. |
NumberOfMatches | 2147483647 | Between 0 and 2147483647 | The NumberOfMatches option specifies the maximum number of matching rows that can be returned. |
Threshold | 0.80 | Between 0.00 and 1.00 | The similarity Threshold option provides the ability to match records above a given similarity score. A threshold of 1.00 is the same as specifying an exact match criteria.
For example, 'Grapes' matches with 'Graes' (missing 'p') only if the thresold is set to less than 0.90. |
TransformationTable | | A valid table with at least 2 columns named 'From' and 'To'. | The TransformationTable option allows matching records based on custom value mappings.
For example, 'Grapes' are matched with 'Raisins' if a transformation table is provided with the 'From' column containing 'Grapes' and the 'To' column containing 'Raisins'. |
Table.Transformation
Left inner fuzzy join of two tables based on [FirstName]
Table.FuzzyJoin(Table.FromRecords({ [CustomerID = 1, FirstName1 = "Bob", Phone = "555-1234"], [CustomerID = 2, FirstName1 = "Jim", Phone = "555-2345"], [CustomerID = 3, FirstName1 = "Paul", Phone = "555-3456"], [CustomerID = 4, FirstName1 = "Zoe", Phone = "555-4567"], [CustomerID = 5, FirstName1 = "Robert", Phone = "555-4567"] }), "FirstName1", Table.FromRecords({ [CustomerStateID = 1, FirstName2 = "Bob", State = "TX"], [CustomerStateID = 2, FirstName2 = "bob", State = "WA"], [CustomerStateID = 3, FirstName2 = "BOb", State = "PA"], [CustomerStateID = 4, FirstName2 = "bOB", State = "CA"], [CustomerStateID = 5, FirstName2 = "Pul", State = "TX"], [CustomerStateID = 6, FirstName2 = "Paul", State = "WA"], [CustomerStateID = 7, FirstName2 = "paul", State = "PA"], [CustomerStateID = 8, FirstName2 = "Peter", State = "CA"], [CustomerStateID = 9, FirstName2 = "Adam", State = "TX"], [CustomerStateID = 10, FirstName2 = "Zoe", State = "WA"], [CustomerStateID = 11, FirstName2 = "Zo", State = "PA"], [CustomerStateID = 12, FirstName2 = "Zaza", State = "CA"] }), "FirstName2", JoinKind.LeftOuter, [IgnoreCase = true, IgnoreSpace = false] )
Table.FromRecords({ [ CustomerID = 1, FirstName1 = "Bob", Phone = "555-1234", CustomerStateID = 1, FirstName2 = "Bob", State = "TX" ], [ CustomerID = 1, FirstName1 = "Bob", Phone = "555-1234", CustomerStateID = 2, FirstName2 = "bob", State = "WA" ], [ CustomerID = 1, FirstName1 = "Bob", Phone = "555-1234", CustomerStateID = 3, FirstName2 = "BOb", State = "PA" ], [ CustomerID = 1, FirstName1 = "Bob", Phone = "555-1234", CustomerStateID = 4, FirstName2 = "bOB", State = "CA" ], [ CustomerID = 2, FirstName1 = "Jim", Phone = "555-2345", CustomerStateID = null, FirstName2 = null, State = null ], [ CustomerID = 3, FirstName1 = "Paul", Phone = "555-3456", CustomerStateID = 6, FirstName2 = "Paul", State = "WA" ], [ CustomerID = 3, FirstName1 = "Paul", Phone = "555-3456", CustomerStateID = 7, FirstName2 = "paul", State = "PA" ], [ CustomerID = 3, FirstName1 = "Paul", Phone = "555-3456", CustomerStateID = 5, FirstName2 = "Pul", State = "TX" ], [ CustomerID = 4, FirstName1 = "Zoe", Phone = "555-4567", CustomerStateID = 10, FirstName2 = "Zoe", State = "WA" ], [ CustomerID = 4, FirstName1 = "Zoe", Phone = "555-4567", CustomerStateID = 11, FirstName2 = "Zo", State = "PA" ], [ CustomerID = 5, FirstName1 = "Robert", Phone = "555-4567", CustomerStateID = null, FirstName2 = null, State = null ] },{ "CustomerID", "FirstName1", "Phone", "CustomerStateID", "FirstName2", "State" })
Last modified 4yr ago