query to validate rows if dups

[Anonymous]

hi dears

i am facing an problem and i could not solve it in power query 

my issue is:  

i have a table which contain transaction ID and status. as you see the transaction id is duplicated ( which is fine for me ) but the status should be for each transaction id different.

so i need to keep the blue circles and remove the red circles.  do have any idea how to keep the blue cirles ( the different status ) ?

notice:  for status i only have 2 values ( G, F )

[AmiraBedh · Accepted solution]

You select both columns > Remove Rows > Remove Duplicates :

Which is in Power Query : 

= Table.Distinct(#"yourPreviousStep")

[Anonymous]

god bless you it works

[AmiraBedh]

Glad to help 🙂

[jennratten]

Hello  - Selecting both columns and selecting remove duplicates should produce the expected result.  It does look like your data needs to be cleaned up first though - you can see that some rows have an extra line and some do not.  Power Query is a case sensitive language that does not automatically omit whitespace and non-printable characters.  Without cleaning it first, you may end up with some records that appear to be the same but are not duplicates because they include some other differences that you cannot see. 

In this example below, I have cells in which the first two appear to be the same; the third appears to have the same value with an extra blank line. However, you can see that I have removed duplicates and still see three. This is because the first cell has a space at the end and the third has a new line character so the cells actually are different.

Here, I have added another column to convert the characters to their character numbers.  Now you can see that the first and last cells have characters that the second does not.

In your data the Transaction Id column appears to be formatted as a number so there shouldn't be any additional characters hiding in it.  The Status 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"

@Anonymous

[Anonymous]

thank you very much for your support