Table.FuzzyGroup
Groups rows whose key texts are spelled alike, not only the same, and works out a column per group.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.FuzzyGroup(table as table, key as any, aggregatedColumns as list, optional options as nullable record) as table| Parameter | Type | Description |
|---|---|---|
table | table | The rows to group. |
key | any | The key column, or a list of key columns; they must hold texts. |
aggregatedColumns | list | One or more {name, function, type}, as in Table.Group: a function of the group's rows as a table. |
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. One row per group: the key as the group's most frequent spelling, then the new columns.
Remarks
- The key columns must be of type text.
- Each group is shown by its most frequent spelling; with a tie, by the one that comes first.
- 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
Units per shop, spellings merged
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.FuzzyGroup(Sales, {"Shop"}, {
{"Units", each List.Sum([Units]), type number},
{"Rows", each Table.RowCount(_), Int64.Type}})| Shop | Units | Rows |
|---|---|---|
| Oslo Sentrum | 8 | 4 |
| Bergen Torget | 8 | 2 |