Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Removing duplicates with two columns

Hey everyone

 

I have two columns with email lists 1 and 2, now what I want to do is check and remove duplicate values from both the columns irrespective of their orders. 

 

 

I have tried the following

 

 

If some one can please help with detailed solution would be appreciated, thanks in advance

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Count", each List.Count(
Table.SelectRows(
Data,
(MyVariable) => MyVariable[Email list 1]=[Email2]
)
[Email2]
))
in
    #"Added Custom"

    Hope this helps.

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous you want it to be removed from both the columns or any of the column values should contain and other one should be replace with null??

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I want it to be removed from only one coloumn

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Anonymous try this in power query.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxKdkjPTczM0UvOz1XSUQrPL8rOzEt3qEzMyM8Hi8XqRCslpaCpKk91qEotQijA1KajVJ6aA2SkImkEq0TWqqNUkpGYl12ZX4qmKiWxLBVqWGYeUBmqM2NjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email1 = _t, Email2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email1", type text}, {"Email2", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "C", each if List.Contains(Source[Email1], [Email2]) then null else [Email2])
in
    #"Added Custom"

         

         

        You can remove the email2 column after the last step.

         

        Accept the solution if that works.

         

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hey Ashish

       

      I am expecting the outcome to be somewhat like this.

       

      Email1 vs Email2 True if any of email matches in both the columns and false if the data is only in email2

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        Hi,

        This M code works

        let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Count", each List.Count(
Table.SelectRows(
Data,
(MyVariable) => MyVariable[Email list 1]=[Email2]
)
[Email2]
))
in
    #"Added Custom"

        Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Still doesn't work, should I update the text to something (marked in yellow)

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi Anonymous ,

     

    Whether the advice given by Ashish_Mathur @ has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out


    Looking forward to your feedback.


    Best Regards,
    Henry