Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Problem Defining a Variable in the Expression.Evaluate Environment

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.

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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's avatar
      lbendlin
      Super User
      2 years ago

      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"
  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    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"