Remove duplicates in Column A when Column B = "Value" in power BI or DAX

Question

I am having difficulties with PowerBI in removing duplicates from one column based on a condition on another column. My data is something like this Sample Data As you can see in the sample data, there are duplicates in ID column that I want to remove when Status = 'False' The Desired output to be like below Expected Output

Any help is highly appreciated

Answer 1

• In PQ (Transform)
  • Group by ID
  • For each subtable, if there is more than one row, select only the rows where Status=true

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZC7DoAgDEX/hdmAt1KE0UEnJx+T8f9/Q5Iq1ESnkpyT21uOw8TOs2nMtuxjHuTgHVF+AZbN2RRhGub13wg1Ag64Bba4BYYIEpAKjw8n3YFIhGSD5rXCh9DrgCDY1/z4PqEVQRVMr09g4X25MLQqoHN4eF5wXg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, Date = _t, Value = _t]),
    #"Correct Error in Source" = Table.TransformColumns(Source,{"Status", each if Text.StartsWith(_,"T") then true else false }),

    #"Changed Type" = Table.TransformColumnTypes(#"Correct Error in Source",{{"ID", Int64.Type}, {"Status", type logical}, {"Date", type date}, {"Value", type number}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
        {"deDupFalse", (t)=> if Table.RowCount(t) > 1 then Table.SelectRows(t, each [Status]=true) else t,
            type table[Status=logical, Date=date, Value=number]}        
        }),

    #"Expanded deDupFalse" = Table.ExpandTableColumn(#"Grouped Rows", "deDupFalse",{"Status","Date","Value"})
in
    #"Expanded deDupFalse"

Data

Filtered