Selecting one row based on criteria if another field is duplicated

[MichaelF1]

I have a table of products (SKUs) showing whether they are live or discontinued.

Unfortunately there are some products that are showing as Live and Discontinued on two different rows. If a product appears twice, I want to delete the Discontinued row.

What I have: 

Product	Status
SKU1	live
SKU1	discontinued
SKU2	live
SKU3	discontinued

What I need:

Product	Status
SKU1	live
SKU2	live
SKU3	discontinued

Thanks in advance!

[mussaenda]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvYONVTSUcrJLEtVitWB81Myi5Pz80oy80pTU2DiRmjqQPxEhZLU4hKYiDGGzlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Status = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Product"}, {{"Count", each List.Max([Status]), type nullable text}})
in
    #"Grouped Rows"

[BA_Pete]

Hi @MichaelF1 ,

If the relevant words are always "Live" and "Discontinued", you can group your table by [Product] and create an aggregated column that is MAX of [Status].

Pete

[MichaelF1]

Hello Pete and thanks for the reply!

It isn't always Live and Discontinued, but you have given me an idea: I can create a new column and substitute a number for the status (there are only about 5 differnet ones so possible to hard code), then just group and aggregate on that.

Does that sound like it would work?

[BA_Pete]

Yes, the principle seems sound.

Difficult to say whether it would stand up to changing values, but it's a simple enough solution to amend/update.

Pete