PQ question - remove row

[scott_86_]

Hi,

Below is a sample data set (apologizes, I am unable to load a sample mini-sheet) of a much large dataset (with more coloumns) I am using.

My goal: For a specific 'training' value ('Heights' in the example), if a 'name' ('Scott' in the example) has both a 'Completed' and 'Approved' row, then remove the 'Approved' row.

I have a hunch this may be able to be achieved through the 'group by' and 'merge query' functions; however, I've had no luck so far trying this.

Is anyone able to help with some steps to achieve the above goal? I'm trying to learn steps and M code amending rather that asking for the code so I can trouble shoot queries in future. Thanks!

 

[arthurbr]

Have a look at Remove rows based on multiple criteria

 

[alex78]

Hi,
for example

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cols = {Table.ColumnNames(Source)} & {{"Approved", "Completed"}},
    Grouped = Table.Combine(Table.Group(Source, List.FirstN(cols{0},2), {{"x", 
              each 
              if List.ContainsAll(Table.Column(_,cols{0}{2}), cols{1}) 
              then Table.SelectRows(_, each Record.Field(_, cols{0}{2}) = cols{1}{1} ) 
              else _ }})[x])
in
    Grouped

Regards