Forum Discussion

kb177's avatar
kb177
Helper II
3 years ago
Solved

remove all rows with same id and one specific value

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

 

 

Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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.

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.