remove all rows with same id when a condition applies.

You want to create a conditional column where your request type is Infrastructure then 1 else 0.

Afterwards, duplicate this whole table and then group by on Problem ID with a Max aggregation on your conditional column  to give a unique list of Problem IDs with 1 when they have infra and 0 if they never do.

You can then merge this back into the original table joining on Problem ID then filter out the 1's.

Hi ,

Thanks for the solution MOVC  and Anonymous  provided,your answers are greate and i want to offer some information for user to refer to.

hello NadaFathy193 , based on  your description, you can refer to the following sample.

1.Sample data 

You can create a calculated column 

Flag =
VAR A =
    CALCULATETABLE (
        VALUES ( 'Table'[Problem ID] ),
        'Table'[Request Type] = "Infrastructure change"
    )
RETURN
    IF ( NOT ( [Problem ID] IN A ), 1, 0 )

Then filter the flag column ,set it equal to 1

Output

2.You can also create a custom column in power query.

let a=Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Problem ID]=[Problem ID] and x[Request Type]="Infrastructure Change"))
in if a=0 then 1 else 0

Then filter column when it equal to 1

Output

Here is the whole M code. you can creat a blank query and put it to advanced editor in power query to refer to.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUXLOz0vLTC8tSizJzM9T8CxJzVWK1YFIeeYlZ6ak5pXgFsChOQm/FIoxOATSihKLS4pKk0tKi1IVnDMS89JTwbLJFMvCrIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Problem ID" = _t, #"Request Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Problem ID", type text}, {"Request Type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Problem ID]=[Problem ID] and x[Request Type]="Infrastructure Change"))
in if a=0 then 1 else 0),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each ([Custom] = 1))
in
    #"Filtered Rows"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.