How to remove duplicates from 2 columns using Power Query

[shaikhzdandg]

How do I remove duplicates from 2 separe columns named (Account No) and (CRM).

The requirement goes like this, I want to maintain only 1 single row when both columns have duplicates. and I do not want to remove the row when only one column has duplicate.

So the condition to remove duplicte should be both columns must have deuplicate value. What I want is shown below snip (The right hand side snip wis what I am after

 NOTE: I DID TRIED TO USE THE REMOVE DUPLICATE OPTION BY SELECTING BOTH COLUMNS IN POWER QUERY HOWEVER, THIS DOES NOT SEEM TO GIVE CORRECT RESULT (PLEASE NOTE I HAVE A LARGE DATASET, SO WHEN IF YOU TEST THIS ON SMALL NUMBER OF ROWS IT MAY GIVE CORRECT RESULT, HOWEVER ON LARGE DATASET THIS DOES NOT PROVIDE CORRECT RESULT

[jennratten]

@shaikhzdandg Have you had a chance to see if this will help you?

[ronrsnfld]

Your example should work using the Remove Duplicates method.

Is there some reason you are unable to supply an example of data which does not work? I'm afraid without that, we would just be guessing as to your problem.

It would be most helpful if you provide usable data (as text which can be copy/pasted, not a screenshot) which reproduces your problem. 

[jennratten]

Hello @shaikhzdandg  - selecting both columns and selecting remove duplicates will produce the expected result shown in your screenshots. 

I see your comment below the screenshots about it not working on a larger dataset.  Power Query is a case sensitive language that does not automatically omit whitespace and non-printable characters.  If you have some records that appear to be the same but were not eliminated as duplicates then it is likely that they truly are not duplicates because they include some other differences that you cannot see. 

In this example below, I have two cells that appear to be the same.  However, you can see that I have removed duplicates and still see both. This is because the first cell has a space at the end that you cannot see so the cells actually are different.

Here, I have added another column to convert the characters to their character numbers which will expose those you cannot see.  Now you can see that the first cell has an extra value at the end.

In your data the Account column appears to be formatted as a number so there shouldn't be any additional characters hiding in it.  The CRM column, however, is text and could have additional characters.  You can add the column to see if there are any extra characters that need to be removed.  You could go ahead and trim and clean the column and that may solve the problem, but it also might not because that doesn't always catch everything, like if there are repeating spaces at the end.  Adding the column to check which characters are actually present is usually a good idea.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcnd0cjY0MlZQitWBc5RiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each Text.Combine ( List.Transform ( Text.ToList ( [Column1] ), each Number.ToText ( Character.ToNumber (_) ) ), "," ) )
in
    #"Added Custom"