remove all rows with same id when a condition applies.

[NadaFathy193]

Hi, i have multiple lines with same 'Problem Id' and i need to remove all lines related to this 'Problem Id' when this condition applies ('Request Type' = 'Infrastructure Chang').

[MOVC · Accepted solution]

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.

[v-xinruzhu-msft · Accepted solution]

Hi ,

Thanks for the solution @MOVC  and @bchager6  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.

[NadaFathy193]

thank you so much 😊

[MOVC · Accepted solution]

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.

[NadaFathy193]

thank you so much 😊

[bchager6]

@NadaFathy193  Do you want to just remove all rows with the Request Type = Infrastructure Chang? If not, how can you identify the dynamic Problem IDs? Do these IDs share anything in common?

[NadaFathy193]

i need to filter out all Problem Ids which have (Request type = Infrastructure change).
for example if i have the below Problem id, i need to remove all lines not just the line have 'Infrastructure change'

[bchager6]

@NadaFathy193  You could create a conditional column in the query editor; When Problem ID = ...7140 and request Type = Infrastructure Chang, 0, else 1, then filter the table on 1s.

[NadaFathy193]

But the Problem Id is not constant value, it can be changed so i need to make it dynamic.