Forum Discussion

mlsx4's avatar
mlsx4
Memorable Member
2 years ago
Solved

Help with Group By

Hello everyone.

 

Let's say I have a table like this, where I need to remove all rows related with workers which doesn't have any True value in status. How can I achieve this behaviour?

Worker ID Column1 Column2 Status
1 a e False
1 b r True
1 c f False
2 d g False
2 e y False
2 g u False
3 as o False
3 g p False
3 ba ñ False
4 n . False
4 n s False
4 ge d False
4 y z True

 

I have seen solutions about merging between the own table and so on, but it has a real bad performance due to the size of the table.

 

Thank you in advance!!

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    Hello, mlsx4 

    let
    Source = your_table_like_this,
    group = Table.Group(Source, "Worker ID", {{"wid", each _}}), 
    select = Table.SelectRows(group, (x) => List.AnyTrue(x[wid][Status])),
    expand = Table.ExpandTableColumn(select, "wid", {"Column1", "Column2", "Status"})
in
    expand

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    Hello, mlsx4 

    let
    Source = your_table_like_this,
    group = Table.Group(Source, "Worker ID", {{"wid", each _}}), 
    select = Table.SelectRows(group, (x) => List.AnyTrue(x[wid][Status])),
    expand = Table.ExpandTableColumn(select, "wid", {"Column1", "Column2", "Status"})
in
    expand