ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago
Status:
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's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support

    ImkeF 

     

    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's avatar
    ImkeF
    Icon for Community Champion rankCommunity 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