Forum Discussion

PareshDalvi's avatar
PareshDalvi
Frequent Visitor
3 years ago

Apply filter on grouped table using each

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

 

General Comment

1 Reply

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    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.