Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
scottdk
Frequent Visitor

Global and Local environment effect on Expression.Evaluate.

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:

scottdk_0-1719635488742.png

 

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. 😅

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

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

 

View solution in original post

2 REPLIES 2
m_dekorte
Super User
Super User

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
Super User
Super User

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]

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors