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...
Need Help
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"