Problem Defining a Variable in the Expression.Evaluate Environment

[sammym]

Hello all,

I have been playing around with pseudo switch functions to try and avoid using heavily nested if statements. The basic approach is to generate a list of conditions, check if the values in a column meet any of the conditions and then use the result to index a list of actions.

I was interested in using Expression.Evaluate to check each value in a column against a list of conditions stored as text. A simple query is shown below.

let
conditionlist = {"[Value]<100000 and [Value]>1000"},

Source = Table.TransformColumnTypes(Table.ExpandRecordColumn(Table.FromList(Json.Document(Binary.FromText("WwogewogICAiVmFsdWUiOiA3NTAwMAogfSwKIHsKICAgIlZhbHVlIjogNzUwCiB9LAogewogICAiVmFsdWUiOiA3NQogfSwKIHsKICAgIlZhbHVlIjogNzUwMDAwMDAwCiB9LAogewogICAiVmFsdWUiOiAxMjAwMAogfSwKIHsKICAgIlZhbHVlIjogNQogfSwKIHsKICAgIlZhbHVlIjogMTAwMDAwMDAwMDAwCiB9LAogewogICAiVmFsdWUiOiA0NTAwMDAwMDAwCiB9LAogewogICAiVmFsdWUiOiAxMjM1Njk4NwogfSwKIHsKICAgIlZhbHVlIjogMjM1NTU2ODgKIH0sCiB7CiAgICJWYWx1ZSI6IDg5OTk5OTk5OQogfSwKIHsKICAgIlZhbHVlIjogMjM2NTk4ODg3CiB9LAogewogICAiVmFsdWUiOiA1Njg4OQogfQpd")), Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"Value"}, {"Value"}),{{"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(Source, "Condition List T/F", each List.Transform(conditionlist, (v) => Expression.Evaluate(v, [v=v])))
in
#"Added Custom"

The list generated for each row contains the error "There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?".

I am certain the error results from the [Value] variable not being defined within the Expression.Evaluate environment. I have been unable to find a way to do so after searching this forum, looking extensively online and using AI bots. For example, Gemini was convinced that adding [Source=Source] to the environment was the way to go - it wasn't.
So, how do you define a dynamic variable in the Expression.Evaluate environment under these conditions?

Any suggestions would be appreciated.

[AlienSx]

lets use a list of functions

let
    conditionlist = {"(z) => z[Value] < 100000 and z[Value] > 1000"},
    Source = Table.FromColumns({{75000, 750, 75, 99000}}, type table [Value = number]),
    #"Added Custom" = Table.AddColumn(
        Source, 
        "Condition List T/F", 
        (x) => List.Transform(conditionlist, (v) => Expression.Evaluate(v)(x))
    )
in
    #"Added Custom"

or 

let
    conditionlist = {"[Value] < 100000 and [Value] > 1000"},
    Source = Table.FromColumns({{75000, 750, 75, 99000}}, type table [Value = number]),
    #"Added Custom" = Table.AddColumn(
        Source, 
        "Condition List T/F", 
        (x) => List.Transform(conditionlist, (v) => Expression.Evaluate("each " & v)(x))
    )
in
    #"Added Custom"

or w/o Expression.Evaluate

let
    conditionlist = {each [Value] < 100000 and [Value] > 1000},
    Source = Table.FromColumns({{75000, 750, 75, 99000}}, type table [Value = number]),
    #"Added Custom" = Table.AddColumn(
        Source, 
        "Condition List T/F", 
        (x) => List.Transform(conditionlist, (v) => v(x))
    )
in
    #"Added Custom"

[lbendlin]

I am sure this can be done in the elegant way you started with - assuming you know how to transfer variable objects across sections.  But here's a more pedestrian approach:

let
conditionlist = {"[Value]<100000 and [Value]>1000"},

Source = Table.TransformColumnTypes(Table.ExpandRecordColumn(Table.FromList(Json.Document(Binary.FromText("WwogewogICAiVmFsdWUiOiA3NTAwMAogfSwKIHsKICAgIlZhbHVlIjogNzUwCiB9LAogewogICAiVmFsdWUiOiA3NQogfSwKIHsKICAgIlZhbHVlIjogNzUwMDAwMDAwCiB9LAogewogICAiVmFsdWUiOiAxMjAwMAogfSwKIHsKICAgIlZhbHVlIjogNQogfSwKIHsKICAgIlZhbHVlIjogMTAwMDAwMDAwMDAwCiB9LAogewogICAiVmFsdWUiOiA0NTAwMDAwMDAwCiB9LAogewogICAiVmFsdWUiOiAxMjM1Njk4NwogfSwKIHsKICAgIlZhbHVlIjogMjM1NTU2ODgKIH0sCiB7CiAgICJWYWx1ZSI6IDg5OTk5OTk5OQogfSwKIHsKICAgIlZhbHVlIjogMjM2NTk4ODg3CiB9LAogewogICAiVmFsdWUiOiA1Njg4OQogfQpd")), Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"Value"}, {"Value"}),{{"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(Source, "TF", each conditionlist),
    #"Expanded TF" = Table.ExpandListColumn(#"Added Custom", "TF"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded TF",each [TF],each Text.Replace([TF],"[Value]",Text.From([Value])),Replacer.ReplaceValue,{"TF"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [TF],each Expression.Evaluate([TF]),Replacer.ReplaceValue,{"TF"})
in
    #"Replaced Value1"

[lbendlin]

Here's a version that hews closer to your original idea.  But note that your condition list needs to be modified.

let
conditionlist = {"Value<100000 and Value>1000"},

Source = Table.TransformColumnTypes(Table.ExpandRecordColumn(Table.FromList(Json.Document(Binary.FromText("WwogewogICAiVmFsdWUiOiA3NTAwMAogfSwKIHsKICAgIlZhbHVlIjogNzUwCiB9LAogewogICAiVmFsdWUiOiA3NQogfSwKIHsKICAgIlZhbHVlIjogNzUwMDAwMDAwCiB9LAogewogICAiVmFsdWUiOiAxMjAwMAogfSwKIHsKICAgIlZhbHVlIjogNQogfSwKIHsKICAgIlZhbHVlIjogMTAwMDAwMDAwMDAwCiB9LAogewogICAiVmFsdWUiOiA0NTAwMDAwMDAwCiB9LAogewogICAiVmFsdWUiOiAxMjM1Njk4NwogfSwKIHsKICAgIlZhbHVlIjogMjM1NTU2ODgKIH0sCiB7CiAgICJWYWx1ZSI6IDg5OTk5OTk5OQogfSwKIHsKICAgIlZhbHVlIjogMjM2NTk4ODg3CiB9LAogewogICAiVmFsdWUiOiA1Njg4OQogfQpd")), Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"Value"}, {"Value"}),{{"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(Source, "Condition List T/F", (k)=> List.Transform(conditionlist, each Expression.Evaluate(_,[Value=k[Value]]))),
    #"Expanded Condition List T/F" = Table.ExpandListColumn(#"Added Custom", "Condition List T/F")
in
#"Expanded Condition List T/F"