remove all rows with same id and one specific value

[kb177]

Hi, I'm having multiple line with one order id and i want to remove all line for that order id if any one line contains word cancel 

as shown below order id 1 and all related lines should be removed. can there be any measure to filter such data or dax

order id  type

1            sell booked

1            payment received 

1            delivered

1            order cancel

2            sell booked

2            payment received

2            delivered

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([type],"cancel",Comparer.OrdinalIgnoreCase) then 1 else 0),
    #"Grouped Rows" = Table.SelectRows(Table.ExpandTableColumn(Table.Group(#"Added Custom", {"order id"}, {{"Count", each List.Sum([Custom]), type number}, {"All", each _, type table}}), "All", {"type"}, {"type"}), each [Count] = 0)[[order id],[type]]
in
    #"Grouped Rows"

 Hope this helps.

[Ahmedx]

pls see my video

https://1drv.ms/v/s!AiUZ0Ws7G26RiRJExYscE_ld_8CS?e=ffGJHq

(x)=> Table.SelectRows(x, (y)=> not  Text.Contains( y[type], "cancel",Comparer.OrdinalIgnoreCase))