Forum Discussion

torsteinheggen's avatar
torsteinheggen
Frequent Visitor
4 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 i...
Need Help
  • Anonymous's avatar
    Anonymous
    4 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

Anonymous's avatar
Anonymous
Not applicable
4 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