Table.FuzzyNestedJoin
Joins two tables on key texts that are spelled alike and puts each row's matches in a new column, as a nested table.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
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| Parameter | Type | Description |
|---|---|---|
table1 | table | The first (left) table: its rows are kept. |
key1 | any | The key column of table1, or a list of them; they must hold texts. |
table2 | table | The table to find matches in. |
key2 | any | The key column of table2, or a list: as many as key1. |
newColumnName | text | The name of the column that holds the matches. |
joinKind | nullable number optional | JoinKind.LeftOuter when left out; or JoinKind.Inner and the other join kinds. |
joinOptions | nullable record optional | IgnoreCase, IgnoreSpace, Threshold (0.80 unless you give another), NumberOfMatches (how many matches a row may have), SimilarityColumnName (adds the similarity as a column) and TransformationTable. |
Returns
table. table1's columns and the new column: in each row, a table of its matches in table2.
Remarks
- The key columns must be of type text. Expand the new column with
Table.ExpandTableColumn. - How two texts are compared, and the few pairs near a threshold that come out otherwise, are in the M functions page's Fuzzy matching section.
Examples
Matches nested, then the segment brought out
let
Merged = Table.FuzzyNestedJoin(
#table(type table [Order = number, Customer = text],
{{1, "Nordvik AS"}, {2, "nordvik as"}, {3, "Fjelstad Bygg"}, {4, "Lunde Handel"}}),
{"Customer"},
#table(type table [Name = text, Segment = text],
{{"Nordvik AS", "Retail"}, {"Fjellstad Bygg", "Trade"}}),
{"Name"},
"Match")
in
Table.ExpandTableColumn(Merged, "Match", {"Segment"})| Order | Customer | Segment |
|---|---|---|
| 1 | Nordvik AS | Retail |
| 2 | nordvik as | Retail |
| 3 | Fjelstad Bygg | Trade |
| 4 | Lunde Handel | null |
Lunde Handel is kept: the default kind is a left outer join, and its segment is null.