Forum Discussion

edhans's avatar
edhans
Community Champion
6 years ago
Solved

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?

 

 

 

 

  • artemus's avatar
    artemus
    6 years ago

    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

  • 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's avatar
      artemus
      Microsoft 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"])

       

      • edhans's avatar
        edhans
        Community 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