Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Fuzzy grouping

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:


General Comment
Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    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

  • ziying35's avatar
    ziying35
    Impactful Individual
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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"