Dynamically filtering a structured column (Table) using a condition stored in another column

[brassy27]

I've been busting my head for a few days and cannot seem to accomplish what I feel should be a trivial task.

I have a table with one of the columns being a structured column (Table) looking like this:

Fig. 1

The same table has another basic text column containing:

 

Fig. 2

I'd like to create a new column with the Custom2 (Fig. 1) content filtered by the PerformanceIndicatorsParsed (Fig. 2).

Expected result for 1st row:

For 4th row:

Here is the code:

let
   filter_indicator = () =>
   let
      output = [PerformanceIndicatorParsed]
   in
      output,

   Filtered = Table.SelectRows([Custom2], filter_indicator()),
   Result = Table.FirstN(Filtered,10)
in
   Result

Unfortunately, i receive the below error:

Expression.Error: We cannot convert the value "each [PI_Id] = 2 or ..." to type Function.
Details:
Value=each [PI_Id] = 2 or [PI_Id] = 3 or [PI_Id] = 1
Type=[Type]

If i hardcode the condition like below, it works just fine.

let
   filter_indicator = () =>
   let
      output = each [PI_Id] = 2 or [PI_Id] = 3
   in
      output,

      Filtered = Table.SelectRows([Custom2], filter_indicator()),
      Result = Table.FirstN(Filtered,10)
in
    Result

Any help is greatly appreciated.

Regards

[jbwtp · Accepted solution]

Hi @brassy27,

you were almost there. All you needed was Expression.Evaluate overlay:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpNTM5QiPZMibU1VMgvArMUbBWMlGJ1opWMkOVjSg0MjJON4IrA/FQFU7BKY6BKMMMMxjAHM2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Filter = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Filter", type text}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"Changed Type", Expression.Evaluate([Filter])))
in
    #"Added Custom"

Cheers,

John

[brassy27]

Oh yeah!!!  Thanks a million John, you nailed it!