Forum Discussion

kellyylx's avatar
kellyylx
Icon for Helper I rankHelper I
2 years ago
Solved

Fuzzy duplicates in one column

Hi I have a column called names with fuzzy duplicates and I am hoping to be able to group them together. I am aware of fuzzy matching in power bi but i do not have a list of correct names to merge th...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi kellyylx ,

     

    Is the question solved? Try the following steps:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1QITyxJLUpLzMlRUIrVQRfOycEiCFRbDBb2SM3JyVdwTs0rKUoFCzjqKQTpKUBVQ0SCYFyg8bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t]),
    #"Added Custom1" = Table.AddColumn(Source, "name2", each Text.Replace(Text.Replace([name],".","")," ",""),type text),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Added Custom1", {"name2"}, #"Added Custom1", {"name2"}, "Source", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.5]),
    #"Removed Other Columns" = Table.Distinct(Table.ReplaceValue(Table.SelectColumns(#"Merged Queries",{"Source"}),each [Source], each Table.Sort([Source],{{"name", Order.Ascending}}),Replacer.ReplaceValue,{"Source"})),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "count", each Table.RowCount([Source])),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"count", Order.Ascending}}),
    #"Added Custom2" = Table.AddColumn(#"Sorted Rows", "number", each if [count] = 1 then 0 else 1),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom2", "Index", 1, 1, Int64.Type),
    #"Added Custom3" = Table.AddColumn(#"Added Index", "group number", each List.Sum(List.FirstN(#"Added Custom2"[number],[Index]))),
    #"Expanded Source" = Table.ExpandTableColumn(#"Added Custom3", "Source", {"name"}, {"name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Source",{"number", "Index"})
in
    #"Removed Columns"

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

kellyylx's avatar
kellyylx
Icon for Helper I rankHelper I
2 years ago

hi thanks so much for your help but is it possible to do something like if the row has no other duplicates, the row is in group 0. so group 0 are the unique rows and group 1 onwards are rows with duplicates?

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

You can check the count of items in each group and then bunch all the single items in a "Others"  group but that would somehow defeat the purpose of the fuzzy match, no?

  • kellyylx's avatar
    kellyylx
    Icon for Helper I rankHelper I
    2 years ago

    i have to filter out the potential duplicates thats why i wanted to have all the unique values in the same group. 

  • kellyylx's avatar
    kellyylx
    Icon for Helper I rankHelper I
    2 years ago

    Hi, how can i go about counting the items in each group? I used group by index to count the number of rows for each index in another table but is unable to merge the 2 tables together. 

     

    this would be my ideal output:

     

    namegroup number count
    Company Waterfall 13
    Company Waterfll13
    Company Waterfalls13
    Hello Centre01
    A. R. Company22
    AR Company 22
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi kellyylx ,

       

      Is the question solved? Try the following steps:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1QITyxJLUpLzMlRUIrVQRfOycEiCFRbDBb2SM3JyVdwTs0rKUoFCzjqKQTpKUBVQ0SCYFyg8bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t]),
    #"Added Custom1" = Table.AddColumn(Source, "name2", each Text.Replace(Text.Replace([name],".","")," ",""),type text),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Added Custom1", {"name2"}, #"Added Custom1", {"name2"}, "Source", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.5]),
    #"Removed Other Columns" = Table.Distinct(Table.ReplaceValue(Table.SelectColumns(#"Merged Queries",{"Source"}),each [Source], each Table.Sort([Source],{{"name", Order.Ascending}}),Replacer.ReplaceValue,{"Source"})),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "count", each Table.RowCount([Source])),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"count", Order.Ascending}}),
    #"Added Custom2" = Table.AddColumn(#"Sorted Rows", "number", each if [count] = 1 then 0 else 1),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom2", "Index", 1, 1, Int64.Type),
    #"Added Custom3" = Table.AddColumn(#"Added Index", "group number", each List.Sum(List.FirstN(#"Added Custom2"[number],[Index]))),
    #"Expanded Source" = Table.ExpandTableColumn(#"Added Custom3", "Source", {"name"}, {"name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Source",{"number", "Index"})
in
    #"Removed Columns"

      Best Regards,
      Gao
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
      If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly --  How to provide sample data in the Power BI Forum