Forum Discussion

Stoner's avatar
Stoner
New Member
3 years ago
Solved

Filter row in table cell with dynamic list

I have grouped table cells in [Custom] and a list of row numbers in [Row].

[Custom] includes an index column that can match to the list in [Row]. I would like to filter the table contents in [Custom] without expanding it.

 

 

 

= Table.AddColumn(Source_Sheet, "Data", each
            Table.SelectRows(
            Table.AddColumn([Custom], "Row", each List.ContainsAny(Source_Sheet[Row]{0}, {[Index]})),
            each ([Row]=true)))

 

 

I currently use this formula which obtains the list from the first cell in column [Row] and applies it to the table (Row 0) but i would like it to dynamically reference the row which the table is in.

 

is there are way to do that?

Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hi, Stoner are you trying to filter [Custom][Index] column by [Row] list? Then try this

    step = 
    Table.AddColumn(
        Source_Sheet, "Data", 
        (x) => Table.SelectRows(x[Custom], (w) => List.Contains(x[Rows], w[Index]))
    )

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hi, Stoner are you trying to filter [Custom][Index] column by [Row] list? Then try this

    step = 
    Table.AddColumn(
        Source_Sheet, "Data", 
        (x) => Table.SelectRows(x[Custom], (w) => List.Contains(x[Rows], w[Index]))
    )
    • Stoner's avatar
      Stoner
      New Member
      3 years ago

      Hi AlienSx 

       

      I'm getting the following error.

       

       

      For your reference, the list content in [Row] is not a column inside the [Custom] table. The two have been matched by merging queries.

       

      ---

      Edit: Hi AlienSx, just noticed the column was Rows instead of Row. It works now, thank you very much!