Remove Duplicate Rows Based on Value

[Anonymous]

In my data there are several duplicates of Record ID and Brand. The same combination fo Record ID and Brand can refer to different Inv IDs. I want to remove those cases Record ID / Brand combination where Inv ID is empty when for the same Record ID / Brand combination a row with an Inv ID exists. For cases where there is only a single row for a Record ID / Brand combination where Inv ID is empty, I want to keep that. Does anyone know how to do that? I'm stuck...

See the below example:

The table I have:

Record ID	Brand	Inv ID
R1	Brand1	I1
R1	Brand1	I2
R1	Brand1	null
R2	Brand2	null
R3	Brand3	I3
R4	Brand4	null
R4	Brand4	I4

The table I'd like to return:

Record ID	Brand	Inv ID
R1	Brand1	I1
R1	Brand1	I2
R2	Brand2	null
R3	Brand3	I3
R4	Brand4	I4

[Anonymous]

Thank you so much, it works perfectly!

[ronrsnfld · Accepted solution]

Group by Record ID, then process each subtable:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjJU0lFyKkrMSwExPA2VYnUwBI2wCEKEjGBCRnAhY5gQiOFpDBE0gQmawNWhCHmaKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record ID" = _t, Brand = _t, #"Inv ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Record ID", type text}, {"Brand", type text}, {"Inv ID", type text}}),
    
    //Group by ID, then process each subtable
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Record ID"}, {
        {"Rem Dups", (t)=>
            let 
                unique = Table.Distinct(t),
                #"Rem Nulls" = if Table.RowCount(unique) > 1
                    then Table.SelectRows(unique, each [Inv ID] <> null and [Inv ID] <> "") else unique
            in #"Rem Nulls",
                type table [Record ID=nullable text, Brand=nullable text, Inv ID=nullable text]}}),
    
    #"Expanded Rem Dups" = Table.ExpandTableColumn(#"Grouped Rows", "Rem Dups", {"Brand", "Inv ID"}, {"Brand", "Inv ID"})
in
    #"Expanded Rem Dups"

Data

Results