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, Culture, SimilarityColumnName, 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.
  • Culture (text): culture-specific matching rules, such as "ja-JP". Default "" (invariant).
  • SimilarityColumnName (text): adds a column showing each row's similarity to its group's representative value. Default null (no column).
  • TransformationTable (table): a custom mapping table with From and To columns to explicitly map known variants before fuzzy matching.

As with Table.Group, each aggregation function receives the rows of its group as a table, so write aggregations like each List.Sum([Amount]) or each Table.RowCount(_) rather than passing List.Sum directly.

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", each List.Sum([Amount]), type number}, {"OrderCount", each Table.RowCount(_), type number}},
        [Threshold = 0.8, IgnoreCase = true]
    )
Applied Steps

The final output — Table.FuzzyGroup merges similar names at Threshold 0.8 (ignoring case) into Alice (250 + 180 = 430, 2 orders), Bob (400 + 120 = 520, 2 orders), and Charlie (300, 1 order).

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", each List.Sum([Spend]), type number}},
        [Threshold = 0.6, TransformationTable = Mapping]
    )
Applied Steps

The final output — Table.FuzzyGroup applies the TransformationTable mappings first, then groups remaining similar vendors together, producing two canonical groups with summed spend.

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}, {"New Yrok", 2}, {"London", 8}}
    )
in
    Table.FuzzyGroup(
        Cities,
        "City",
        {{"Total", each List.Sum([Count]), type number}},
        [Threshold = 0.6]
    )
Applied Steps

The final output — Table.FuzzyGroup at Threshold 0.6 groups the New York variants under "New York" with a Total of 20 (10 + 5 + 3 + 2), while London stays on its own with 8.

City
Total
1New York20
2London8

Related Functions

Compatibility

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