ImkeF
Community Champion
6 years agoStatus:
New
Exception error through environment in Expression.Evaluate
Expression.Evaluate throws an exception error when the reference to the current row is used together with native function, like [Text.Contains = Text.Contains, _=_]
let
Conditions = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxT0lGKDkvNS8kvilWwVYhRCk7MLchJNYxRAkqUZKTmASmIkFKsDky9f1Fmun9BalFiSX6RZwpEX1JReoySQmJeikJ0cGVxSWouRNgxANmkoNTi0pwSQySj0NUq5BehGhDgj2yAY0CAP0J3SGpFiZ5zfl5JYmZesQbMGzoKcG8YxShpYvojFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hypothesis = _t, Condition = _t, Conclusion = _t, Result = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Hypothesis", type text}, {"Condition", type text}, {"Conclusion", type text}, {"Result", type text}})
in
#"Changed Type",
ListOfConditions = Table.ToRows(Conditions),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzC3ISTUCsYyBhG9inlKsTrSSEVwKpCg9H0i4FwWBpYzhUiBWUlE6kHQMAEuZwKVArKTUVKhULAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Vendor = _t, OrigOperatorId = _t, System = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {"OrigOperatorId", type text}, {"System", type text}}),
Evaluate = Table.AddColumn(
#"Changed Type",
"Custom",
each List.First(
List.Transform(
ListOfConditions,
(l) => Expression.Evaluate(
" if " & l{1} & " then """ & l{3} & """ else null",
[Text.Contains = Text.Contains, _=_]
)
)
)
)
in
Evaluate
For more details, please check this thread: https://community.powerbi.com/t5/Desktop/Power-Query-Configurable-Dynamic-If-Statement/td-p/1288482
Current workaround is OK so far.
2 Comments
- v-chuncz-msft
Community Support
You may change the custom column formula as follows.
let r = _ in List.First( List.Transform( ListOfConditions, (l) => Expression.Evaluate( " if " & l{1} & " then """ & l{3} & """ else null", [Text.Contains = Text.Contains, _=r] ) ) ) - ImkeF
Community Champion
Thanks v-chuncz-msft ,
that's an interesting solution.
If you have any explanation for this behaviour to share, I'd be interested to hear.Thanks,
Imke