Forum Discussion

torsteinheggen's avatar
torsteinheggen
Frequent Visitor
3 years ago
Solved

Removing "duplicates"

I have a data set with a lot of duplicates. Many of them, I can remove in Power Query with the remove duplicate function. But I am having great difficulties with names. The names are identical, but in some rows the family name comes first, and in other rows it comes last. Example:

Row 1: Torstein Heggen

Row 2: Heggen Torstein

 

I need a way to identify these two as the same. In Excel, I've worked this out by splitting the name in separate characters sorted alphabetically (combining concatenate, sort, mid and sequence functions). My name returns as " eeeggHinnorsTt" when using this formula, regardles of the name is written as in row 1 or row 2. This makes it perfect to remove the duplicates.

 

I've tried to make a new column to return the same in Power BI. Both by adding a new custom column in Power Query, and writing it in dax. I've tried different ways of writing them as in Excel (with M or Dax languange, of course), but I just get errors. Anyone got an idea of how to make a column in Power BI equivalent to what I've done in Excel? Preferably in Power Query, but Dax will get me a lot further. Or is there another way of matching these without a workaround as this?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi torsteinheggen - You can use the following approach, but I am not sure if this works if you want to keep the First Name, Surname result.  The Remove Duplicates will keep the first available.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvKi5JzcxT8EhNT0/NU4rViVaCMBVgUmAxl8SiyhwFn8q85AwwH8xSAIsqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = 
        Table.AddColumn(
            Source, 
            "Custom", 
            each 
                Text.Combine( 
                    List.Sort( 
                        Text.ToList( 
                            Text.Lower( [Column1] ) 
                        ) 
                    )
                )
            ),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Custom"})
in
    #"Removed Columns"

     

    Many thanks

    Daryl

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi torsteinheggen - You can use the following approach, but I am not sure if this works if you want to keep the First Name, Surname result.  The Remove Duplicates will keep the first available.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvKi5JzcxT8EhNT0/NU4rViVaCMBVgUmAxl8SiyhwFn8q85AwwH8xSAIsqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = 
        Table.AddColumn(
            Source, 
            "Custom", 
            each 
                Text.Combine( 
                    List.Sort( 
                        Text.ToList( 
                            Text.Lower( [Column1] ) 
                        ) 
                    )
                )
            ),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Custom"})
in
    #"Removed Columns"

     

    Many thanks

    Daryl

  • torsteinheggen's avatar
    torsteinheggen
    Frequent Visitor
    3 years ago

    Think I missed something with the Text.ToList part. I'll investigate how to actually remove the duplicates, so I keep the rows I want. My main problem was the Table.AddColumn-part, which you solved.

    Thank you so much Daryl.