Help, deleting repeated rows based on status of another column

[franorio]

Hi guys, 

just a quick question, I got a table, some of the principal columns I'am using are User ID, Date, Grade and Status.

The table collects information about determined users compleating courses. Users need more than an 80% to pass and they can be repeated in the column due they can try doing the course more than once. 

Tried out with the "delete duplicates" option in Query Editor, but sometimes it keeps line with Status NO OK and sometimes with OK. 

So what I need is to delete repeated user rows, if user is repeated with OK and NO OK keep only those that have status OK. 

User ID	Date	Grade	Status
654321	8/8/17	10	NO OK
654321	8/8/17	20	NO OK
123456	8/8/17	24	NO OK
123456	8/8/17	100	OK
654321	8/8/17	80	OK
123456	8/8/17	30	NO OK
123456	8/8/17	43	NO OK

Thanks!!

[Greg_Deckler · Accepted solution]

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM1MTYyVNJRstC30DcyMDQHMg0NgISfv4K/t1KsDnYlRqhKDI2MTUzN0JSYEFRiaAAyBo81Fkjy2Awwhjvj0AKcikyMEQ6JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Date = _t, Grade = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Date", type date}, {"Grade", Int64.Type}, {"Status", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Status", Order.Descending}}),
    #"Table Buffer" = Table.Buffer(#"Sorted Rows"),
    #"Removed Duplicates" = Table.Distinct(#"Table Buffer", {"User ID"})
in
    #"Removed Duplicates"

The trick is the #"Table Buffer" line, which you have to hand-type.