Apply filter on grouped table using each

[PareshDalvi]

Trying to filter grouped data table using each parameter.

In following dataset, I want to find if any row has event=ticket in group by session_id

= Table.Group(#"Added Custom", {"session_Id", "user_Id"}, {"Count", each Table.RowCount(_), Int64.Type}, {Table.SelectColumns (each [event] = "ticket")})

It returns following error.

Expression.Error: We cannot convert a value of type List to type Number.
Details:
Value=[List]
Type=[Type]

This is what the column contains

[AlienSx]

Hello, @PareshDalvi Table.Group expects "nullable number" as 4th parameter (groupKind) while Table.SelectColumns (each [event] = "ticket") does not look like a number and does not return number. Moreover Table.SelectColumns is looking forward to get a table as it's 1st argument while you give it a function (_) => [event] = "ticket".

You have 3 options here:

1. Filter table before Table.Group call

filter_table = Table.SelectRows(#"Added Custom", each [event] = "ticket"),
g = Table.Group(filter_table, {"session_Id", "user_Id"}, {"Count", each Table.RowCount(_), Int64.Type})

2. Filter table inside Table.Group call

= Table.Group(
    Table.SelectRows(
       #"Added Custom", 
       each [event]= "ticket"
    ),
    {"session_Id", "user_Id"}, 
    {"Count", each Table.RowCount(_), Int64.Type}
)

3. Filter table inside Table.RowCount

= Table.Group(
    #"Added Custom", 
    {"session_Id", "user_Id"}, 
    {"Count", 
      each 
        Table.RowCount(
            Table.SelectRows(_, each [event] = "ticket")
        ), 
      Int64.Type}
)

Either should work. I don't know which one is better performance wise - I don't know.