Forum Discussion

Mikee5's avatar
Mikee5
Regular Visitor
3 years ago

Remove group of rows based on column data

How do I write a query to remove all of the rows of a "group" based on some column data.  For example in the below I want to remove all Owner rows where the driver doesn't own a Toyota.

Owner   Car
JonesHonda
JonesToyota
JonesHonda
SmithVW
SmithFord
TaylorHyundai
TaylorToyota
TaylorToyota

 

The result I am after would remove all Smith rows because they have never owned a Toyota

Owner  Car
JonesHonda
JonesToyota
JonesHonda
TaylorHyundai
TaylorToyota
TaylorToyota

 

Just learning so any help appreciated.

Need Help

1 Reply

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hello, Mikee5 Group by owner, check if [Car] column contains "Toyota" and then select TRUE only. Just an idea.

     

    let
    Source = your_table,
    group = Table.Group(Source, "Owner", {{"rows", each _}, {"Toyota_owner", each List.Contains(_[Car], "Toyota")}}),
    filter = Table.SelectRows(group, each ([Toyota_owner] = true)),
    result = Table.Combine(filter[rows])
in
    result

     

    another option is to filter table to get a list of Toyota owners only and then filter table again by Owners column

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    names = List.Buffer(List.Distinct(Table.SelectRows(Source, each [Car] = "Toyota")[Owner])),
    result = Table.SelectRows(Source, each List.Contains(names, [Owner]))
in
    result