Understanding Expression.Evaluate - isn't working

[edhans]

I have the following M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8krMUzAyMDJQitUB88G0Z0lqLpAdnJiTWgwWSATyDMGsJCDLCMxKBrKMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Long Step Name" = Source,
    #"Need Help" = Expression.Identifier("Long Step Name") & "[" & Table.ColumnNames(#"Long Step Name"){0} & "]"
in
    #"Need Help"

In this example, I am trying to dynamically get the name of the first column and return that column as a list. 

Spoiler Alert: The following code does this so I am no longer stuck on my problem, just stuck on why my overly complex first approach isn't working. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8krMUzAyMDJQitUB88G0Z0lqLpAdnJiTWgwWSATyDMGsJCDLCMxKBrKMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Long Step Name" = Source,
    Works = 
        Table.Column(
            #"Long Step Name",
            Table.ColumnNames(#"Long Step Name"){0}
        )
in
    Works

Here is how I was first approaching it and hitting walls. In the first code, the #"Long Step Name" returns this:

I want to use List.PositionOf(listname, "Text to Find") to find the first valid row (to be used in Table.Skip())

So,

=List.PositionOf(#"Long Step Name"[Column1],"Item")

is what I need, which returns 3.

The #"Need Help" line returns the text value correctly:

Typing this into a new line works beatifully:

= #"Long Step Name"[Column1]

Now, I need to use Expression.Evaluate to make that string work. But it won't.

Fine. So I google and find @cwebb 's article on this here, which unfortunately links back to a dead-redirected link on Microsoft's site that is useless to me. (I am going to pretend I would have understood the detailed article on Environment Variables.) I had a similar issue with another use case a year or so ago that @ImkeF solved on her site, and it changed the statement to read this way:

= Expression.Evaluate(#"Need Help", each [_ = _])

That didnt' work either.

So, how can I get my text command to work?

[lbendlin]

I think you're just holding it wrong.  Here's how far I got for now.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8krMUzAyMDJQitUB88G0Z0lqLpAdnJiTWgwWSATyDMGsJCDLCMxKBrKMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Long Step Name" = Source,
    #"Added Custom" = Table.AddColumn(#"Long Step Name", "Custom", each List.PositionOf(Table.Column(#"Long Step Name","Column1"),"Item"))
in
    #"Added Custom"

please ignore the custom column, that's just for debugging.

The next step would be 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8krMUzAyMDJQitUB88G0Z0lqLpAdnJiTWgwWSATyDMGsJCDLCMxKBrKMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Long Step Name" = Source,
    ToSkip = List.PositionOf(Table.Column(#"Long Step Name","Column1"),"Item"),
    #"Removed Top Rows" = Table.Skip(#"Long Step Name",ToSkip),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sales", Currency.Type}})
in
    #"Changed Type"

[artemus · Accepted solution]

When you use Expression.Evaulate you need to include every variable and library function you want to make avialable in a record. In your case:

Expression.Evaulte(#"Need Help", [Long Step Name = #"Long Step Name"])

[edhans]

Thanks @artemus 
So in the Final step below, Expression.Evalute doesn't know what to evaluate, and I have to tell it where Long Step Name is defined? 

Why am I saying:
Long Step Name = #"Long Step Name"
Is "Long Step Name" how Evaluate.Expression sees it, and #"Long Step Name" is the reference to the step?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8krMUzAyMDJQitUB88G0Z0lqLpAdnJiTWgwWSATyDMGsJCDLCMxKBrKMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Long Step Name" = Source,
    #"Need Help" = Expression.Identifier("Long Step Name") & "[" & Table.ColumnNames(#"Long Step Name"){0} & "]",
    Final = Expression.Evaluate(#"Need Help", [Long Step Name = #"Long Step Name"])
in
    Final

[ImkeF]

Hi @edhans ,

My friend @LarsSchreiber  and I have written an article about the evaluation context in M that might explain a bit of this here (hopefully 😉  ) https://ssbi-blog.de/blog/technical-topics-english/the-environment-concept-in-m-for-power-query-and-...

You can find the official language specification here now: https://docs.microsoft.com/en-us/powerquery-m/power-query-m-language-specification

BTW: The "equivalent" of Table.Column is Record.Field (if you want to do some dynamic field referencing on row-level) 

[edhans]

Thanks @ImkeF for the reminder on Record.Field. I've used it before but not enough I could just code with it, so need to go back and refresh my memory with it.

So many ways to do the same or similar things in Power BI, but performance considerations, or just code readability, can be very different. 👍

[artemus]

Yes, you are binding the object to the name it currently is known as.

The second parameter to Evaluate is the environment (which is optional). If you don't include the parameter then your statement cannot refer to any outside variables, or any standard functions like List.Max, the only calls you can make are the constructor for table, date, time, datetime, datetimezone, duration.

If you wanted to be able to reference all previous steps you would have to enumerate all of them in the record.  If you wanted to include all top level objects you could use #shared, but if you use this, Power Bi will refuse to load it into the model due to security reasons.

Ex:

Expression.Evaulate("foo + List.Max([bar])", [foo = 9, _ = [bar = {1, 2, 7, 5}]], List.Max = each _{List.Count(_) - 1})

would evaulte to:

14, since foo = 9, and List.Max is bound to a function which returns the last element in a list (5), instead of the max of the list.