Table.AddFuzzyClusterColumn
Adds a column that gives each spelling of a text the spelling its group of look-alikes is known by.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.AddFuzzyClusterColumn(table as table, columnName as text, newColumnName as text, optional options as nullable record) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table with the texts. |
columnName | text | The column of texts to cluster. |
newColumnName | text | The name of the new column. |
options | nullable record optional | IgnoreCase (true unless false), IgnoreSpace (true unless false), Threshold (0.80 unless you give another, from 0 to 1) and TransformationTable (a table with From and To columns: texts to read as the same). |
Returns
table. The table with the new column last: for each row, its cluster's text.
Remarks
- The column must be of type text.
- A cluster is known by its most frequent spelling; with a tie, by the one that comes first.
- Set
SimilarityColumnNameto add a column with each row's similarity to its cluster's text. - 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
One spelling per shop
let
Sales = #table(type table [Shop = text, Units = number], {
{"Oslo Sentrum", 4}, {"oslo sentrum", 2}, {"Oslo Sentrm", 1}, {"Oslo Sentrum", 1},
{"Bergen Torget", 3}, {"BERGEN TORGET", 5}})
in
Table.AddFuzzyClusterColumn(Sales, "Shop", "Shop cluster", [SimilarityColumnName = "Similarity"])| Shop | Units | Shop cluster | Similarity |
|---|---|---|---|
| Oslo Sentrum | 4 | Oslo Sentrum | 1 |
| oslo sentrum | 2 | Oslo Sentrum | 1 |
| Oslo Sentrm | 1 | Oslo Sentrum | 0.96 |
| Oslo Sentrum | 1 | Oslo Sentrum | 1 |
| Bergen Torget | 3 | Bergen Torget | 1 |
| BERGEN TORGET | 5 | Bergen Torget | 1 |
Oslo Sentrum is written that way twice, more than any other Oslo spelling; the Bergen spellings tie, so the first is used. Case is ignored: "oslo sentrum" is as similar as can be.