Table.FuzzyGroup

Table

Groups rows using fuzzy matching on key column(s), tolerating typos and spelling variations.

Examples on this page use shared sample tables. View them to understand the input data before reading the examples below.

Syntax

Table.FuzzyGroup(table as table, key as any, aggregatedColumns as list, optional options as nullable record) as table

Parameters

NameTypeRequiredDescription
tabletableYesThe table to group.
keyanyYesA column name or list of column names to group by using fuzzy matching.
aggregatedColumnslistYesA list of {newColumnName, aggregationFunction} or {newColumnName, aggregationFunction, newColumnType} triples defining aggregated output columns.
optionsrecordNoOptional record with fuzzy options: Threshold, IgnoreCase, IgnoreSpace, NumberOfMatches, TransformationTable.

Return Value

table — A grouped table where similar key values are clustered together.

Remarks

Table.FuzzyGroup is a fuzzy-matching counterpart to Table.Group. Instead of requiring exact equality on key columns, it clusters rows with similar key values together — handling typos, case differences, extra spaces, and minor spelling variations. For example, "Micosoft", "Microsoft", and "MICROSOFT" would all be grouped under a single canonical representative.

The representative value chosen for each cluster is typically the first or most frequent form encountered. The options record supports the following fields:

- Threshold (number, 0.0–1.0): the minimum similarity score required for a match. Default is 0.8. Lower values match more loosely; 1.0 requires exact equality. - IgnoreCase (logical): ignore case differences. Default true. - IgnoreSpace (logical): ignore whitespace differences. Default true. - NumberOfMatches (number): maximum right-side matches per cluster group. - TransformationTable (table): a custom mapping table with From and To columns to explicitly map known variants before fuzzy matching.

For inspecting cluster assignments without aggregating, use Table.AddFuzzyClusterColumn instead. Table.FuzzyGroup does not fold to data sources. On large datasets, consider pre-filtering to reduce the candidate space before applying fuzzy operations.

Examples

Example 1: Consolidate misspelled customer names and sum their orders

let
    Orders = #table(
        {"CustomerName", "Amount"},
        {{"Alice", 250}, {"Alise", 180}, {"Bob", 400}, {"Bobb", 120}, {"Charlie", 300}}
    )
in
    Table.FuzzyGroup(
        Orders,
        "CustomerName",
        {{"TotalAmount", List.Sum, type number}, {"OrderCount", List.Count, type number}},
        [Threshold = 0.8, IgnoreCase = true]
    )
Result
CustomerName
TotalAmount
OrderCount
1Alice4302
2Bob5202
3Charlie3001

Example 2: Use a TransformationTable to handle known brand variants

let
    Orders = #table(
        {"Vendor", "Spend"},
        {{"Microsoft", 500}, {"MS Corp", 200}, {"Google", 300}, {"Alphabet", 150}}
    ),
    Mapping = #table({"From","To"},{{"MS Corp","Microsoft"},{"Alphabet","Google"}})
in
    Table.FuzzyGroup(
        Orders,
        "Vendor",
        {{"TotalSpend", List.Sum, type number}},
        [Threshold = 0.6, TransformationTable = Mapping]
    )
Result
Vendor
TotalSpend
1Microsoft700
2Google450

Example 3: Lower the threshold to catch more aggressive variations

let
    Cities = #table(
        {"City", "Count"},
        {{"New York", 10}, {"New-York", 5}, {"Newyork", 3}, {"NY City", 2}, {"London", 8}}
    )
in
    Table.FuzzyGroup(
        Cities,
        "City",
        {{"Total", List.Sum, type number}},
        [Threshold = 0.6]
    )
Result
City
Total
1New York20
2London8

Related Functions

Compatibility

✓ Power BI Desktop✓ Power BI Service✗ Excel Desktop✗ Excel Online✓ Dataflows✓ Fabric Notebooks