Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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"
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"
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"
Check out the July 2025 Power BI update to learn about new features.