Forum Discussion
Understanding Expression.Evaluate - isn't working
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?
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"])
6 Replies
- lbendlinSuper User
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"- artemusMicrosoft Employee
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"])- edhansCommunity Champion
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