Fuzzy grouping

[Anonymous]

To work on some tables, I found it useful to use the Table.FuzzyGroup function. Unfortunately the result of the tests carried out is different from what I would have expected.

In particular, the TransformationTable parameter seems to have a behavior that depends on the order of the rows of the table on which to make groupings.
It also seems to depend on the value assigned to the other parameter: Threshold.

Has anyone used this function with this parameter?
What do you think about  the way it works?

Source 1

 

res 1

sorce 2

res 2:

source 3:

res 3:

[ziying35]

Hi, @Anonymous 

Power Query functions that contain the word Fuzzy are not easy to use, their Threshold parameter is particularly difficult to adjust, and we often don't get the results we expect using such functions.

[Anonymous]

Hi @ziying35 ,

I normally use excel for work activities (sometimes VBA for excel), from now on, after having acquired a certain familiarity with the M language, I wanted to try to exploit the superior possibilities of power query compared to excel for manipulation of the data tables, for the presentation aspects I still rely on excel and power point.
In this case, the possibility of fuzzy grouping would have been useful, using a mapping table, to group a series of labels under a macro category.
From the description the table.FuzzyGroup function seemed to me to do just what I needed.
I would have expected that if in the From-To table I bind
l1, l2, ..., ln To L, then the label of the group would be "L" regardless if and where there is a line with the label "L". Instead the function seems to give the first lx it finds, as the label of the group.

PS
in this case a workaround of the problem is to insert at the beginning of the table some dummy rows that have in the column used to make group the desired value as representative of the group.

Below is some code examples to do this ...

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    cols=Table.ColumnNames(#"Changed Type"),
    n=List.Count(cols),
    head=Table.InsertRows(#"Changed Type",0,List.Transform(List.Distinct(mapTab[To]), each Record.FromList(List.Repeat({_},n),cols))),
    #"Grouped Rows" = Table.FuzzyGroup(head, {"Column1"}, {{"Count", each Table.RowCount(_)-1, Int64.Type}},[TransformationTable=mapTab])
in
    #"Grouped Rows"

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    cols=Table.ColumnNames(#"Changed Type"),
    n=List.Count(cols),
    head=Table.InsertRows(#"Changed Type",0,List.Transform(List.Distinct(mapTab[To]), each Record.TransformFields(Record.FromList(List.Repeat({null},n),cols),{cols{0},(x)=>_}))),
    #"Grouped Rows" = Table.FuzzyGroup(head, cols{0}, {{"Count", each Table.Skip(_), Int64.Type}},[TransformationTable=mapTab])
in
    #"Grouped Rows"

[Greg_Deckler]

@Anonymous Have used it, has some limitations around just how much control you have over it. Yes, the order matters I believe as well as the threshold as I think the way the algorithm works is that it finds the first match that falls within the threshold essentially. Hard to know for certain as it is kind of a black box.

If you want more control, I did create this DAX Fuzzy matching Quick Measure Gallery submission here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Fuzzy/m-p/1352914#M608