Global and Local environment effect on Expression.Evaluate.

[scottdk]

I am was completely confused about the inconsistent results I was getting from Expression.Evaluate while taking a deeper dive into learning about global and local environments in preparation for moving a lot of my code into external function libraries. (VSCode has search function! Who'd have though that would come in handy in an Advanced Editor!)

As is very often the case, by preparing an example to present here in order to ask for help, I think I have started to understand what is going on.

I am utterly less confused now. Can somebody look over my findings here and provide some feedback?

My original confusion was over why the results in Example 1 and Example 2 are different when I switch the order of the environment record. And moreso when I changed the variable names from MyTextValue to Val. It seems that when building the Enviroment record, if an expression exists more than once (in #shared, and passed with MyTextValue = MyTextValue, then Expression.Evaluate will use the value defined last in the record, not first. So it's not using the first one it finds? It's more like the second one supercedes the first? I'd like to fully understand what is going on here, as it may help down the line when I start building my function library.

I have created a simple query MyTextValue:

let
    Source = "EXTERNAL"
in
    Source

I then have a Second Query that has a local environment definition of MyTextValue, as well as referencing the value in the global environment from the first query.

let
    MyTextValue = "internal",

    Expression1 = """This is "" & MyTextValue",
    Expression2 = """This is "" & Val",

    Environment1a = Record.Combine({ [MyTextValue = MyTextValue], #shared }),
    Environment1b = Record.Combine({ #shared, [MyTextValue = MyTextValue] }),
    Environment1c = Record.Combine({ #shared }),
    Environment1d = Record.Combine({ [MyTextValue = MyTextValue] }),

    Environment2a = Record.Combine({ [Val = MyTextValue], #shared }),
    Environment2b = Record.Combine({ #shared, [Val = MyTextValue] }),
    Environment2c = Record.Combine({ [Val = MyTextValue] }),
    Environment2d = Record.Combine({ #shared }),


    Result1 = Expression.Evaluate( Expression1, Environment1a),
    Result2 = Expression.Evaluate( Expression1, Environment1b),

    Result3 = Expression.Evaluate( Expression1, Environment1c),
    Result4 = Expression.Evaluate( Expression1, Environment1d),

    Result5 = Expression.Evaluate( Expression2, Environment2a),
    Result6 = Expression.Evaluate( Expression2, Environment2b),

    Result7 = Expression.Evaluate( Expression2, Environment2c),
    Result8 = Expression.Evaluate( Expression2, Environment2d),

    FinalResult = Table.FromRecords (
    {   
        [ Example = "Example 1", Expression = Expression1, Environment = "Record.Combine({ [MyTextValue = MyTextValue],   #shared })", Result = Result1, Comment = "#shared defined last overwrites the internal MyTextValue???" ],
        [ Example = "Example 2", Expression = Expression1, Environment = "Record.Combine({ #shared,   [MyTextValue = MyTextValue] })", Result = Result2, Comment = "MyTextValue in #shared is overwritten by the internal definition???" ],
        [ Example = "Example 3", Expression = Expression1, Environment = "Record.Combine({ [ #shared })",                              Result = Result3, Comment = "As expected. Only external defined." ],
        [ Example = "Example 4", Expression = Expression1, Environment = "Record.Combine({ [MyTextValue = MyTextValue] })",            Result = Result4, Comment = "As expected. Only internal defined." ],

        [ Example = "Example 5", Expression = Expression2, Environment = "Record.Combine({ [Val = MyTextValue],   #shared })",         Result = Result5, Comment = "Why does this differ from Example 1??? Because Val is not in #shared?" ],
        [ Example = "Example 6", Expression = Expression2, Environment = "Record.Combine({ #shared,   [Val = MyTextValue] })",         Result = Result6, Comment = "Expected. Val isn't in #shared! I'm starting to get this." ],
        [ Example = "Example 7", Expression = Expression2, Environment = "Record.Combine({ [Val = MyTextValue] })",                    Result = Result7, Comment = "Expected" ],
        [ Example = "Example 8", Expression = Expression2, Environment = "Record.Combine({ #shared })",                                Result = Result8, Comment = "Error because #shared doesn't define 'Val'! This explains #5 and #6!" ]

    }, type table[Example, Expression, Environment, Result, Comment], MissingField.UseNull )

in
    FinalResult

Giving me these results:

Are my assumptions in the comments correct? I'd appreciate any comments and feedback.

So creating these examples in order to ask the question has shed a lot of light on the topic. I may have answered my own question in the process but I'm not 100% confident yet, so it's still a question, not a tutorial. 😅

[m_dekorte · Accepted solution]

Hi @scottdk,

As the example by @AlienSx clearly illustrates, a record merge (or combination operation), will overwrite like named fields from the left operant with that of the right operant. See the docs here: Values, Record  

[a = 1] & [a = 2] & [a = 3] & [a = 2]

//result: a = 2

[AlienSx]

So it's not using the first one it finds? It's more like the second one supercedes the first?

This is how records with the same field names are combined in M

    rec_1 = [a = 1],
    rec_2 = [a = 2],
    combine12 = Record.Combine({rec_1, rec_2}), // [a = 2]
    combine21 = Record.Combine({rec_2, rec_1}) // [a = 1]