none
Passing Column Name as Variable RRS feed

  • Question

  • Greeting all, 

      A basic evaluation behavior question here.  I noticed that tables will evaluate input as variables, but they won't evaluate a variable passed as Column name.  See the example below:

    fnTest2 = (lstA as list, strField as text) as list=>
        let        
        //match strField to name of correct list in lstKeys which containes the name off all the 1 column tables on the sheet             
             tblKeys = Excel.CurrentWorkbook(){[Name=lstKey]}[Content], //Returns Table conatining names of all tables on sheet
        // Convert tblKeys to a list
             lstKeys = tblKeys[Keys], //Returns a list     
        //get the name of the correct table to match to by matching item on lstKeys to passed in variable strField      
             strListPos = List.PositionOfAny(lstKeys, {strField}),            
             strListName = lstKeys{strListPos},  // Returns the name of the table containing the values I want to match against lstA.      
        //Get the table using the name returned in the previous statement.
             tblFieldKey= Excel.CurrentWorkbook(){[Name=strListName]}[Content], //Returnes Correct Table
             //Convert table to list
             lstFieldKey = tblFieldKey[strListName] //This is the problem line.  
    
         in        
           lstFieldKey ,
      It seems that the lstFieldKey = tblFieldKey[strListName] will not evaluate strListName as a variable and will actually evaluate strListName as a literal.  The error is "table doesnt contail a column "strListName".   Also that statement will not allow concatenation symbles without giving me a syntax error.  How do you pass a column name as a variable in M?

    Thursday, October 27, 2016 8:34 PM

Answers

  • As you observed, anything you put in square brackets is treated literally, so you can't put a variable name there. Given a table named tblFieldKey and a column named Keys, tblFieldKey[Keys] is just syntax sugar for Table.Column(tblFieldKey, "Keys"). They both do the same thing - return a list of keys.

    I'd strongly recommend that you don't use Hungarian notion when using a functional language, as it's pretty obvious what's being returned in any step, and you don't get to define a type for the variable anyway. However, it does make code harder to read. :)

    Thursday, October 27, 2016 11:26 PM
  • It adds up, but not the way you're written the first version of testThis. Should be:

    testThis = Table.AddColumn("myTable", "myNewColumnName", each "myTable"[FieldToCopy])

    Friday, October 28, 2016 1:16 AM
  • I don't understand what you're trying to do here.

    In your function-example given in your first post, the last step would return one list.

    The exercises on your last post would return multiple results, as they operate on each record of your table. Is this the step where you want to call your first function? (Then you should expect a list/column with lists returned in every row)

    Or are you still trying to get your last line of the function from above? Then you shouldn't use the Table.AddColumn, but this:

    lstFieldKey = Table.Column(tblFieldKey, ReferenceToWhereTheVariableComesFrom)


    Imke Feldmann TheBIccountant.com

    Saturday, October 29, 2016 6:20 AM
    Moderator

All replies

  • Without using Expression.Evaluate you're not able to pass a variable into square brackets.

    Try this instead: lstFieldKey = Table.SelectColumns(tblFieldKey, "strListName")


    Imke Feldmann TheBIccountant.com

    Thursday, October 27, 2016 9:03 PM
    Moderator
  • Thanks,  Actually Expression.Evaluate and Expression.Constant don't work between [] either and create a syntax error, (or so my testing shows).  Table.Column(tblFieldKey, strListName) does seem to return the value.  Can you explain the difference in the way M actually evaluates the statements? 
    Thursday, October 27, 2016 9:20 PM
  • As you observed, anything you put in square brackets is treated literally, so you can't put a variable name there. Given a table named tblFieldKey and a column named Keys, tblFieldKey[Keys] is just syntax sugar for Table.Column(tblFieldKey, "Keys"). They both do the same thing - return a list of keys.

    I'd strongly recommend that you don't use Hungarian notion when using a functional language, as it's pretty obvious what's being returned in any step, and you don't get to define a type for the variable anyway. However, it does make code harder to read. :)

    Thursday, October 27, 2016 11:26 PM
  • Thanks for the reply but this doesn't quite add up.  Try this:

    testThis = Table.AddColumn("myTable", "myNewColumnName", each [FieldToCopy])

    Then:

    testThis = Table.AddColumn("myTable", "myNewColumnName", each Table.Column("myTable", "FieldToCopy")

    The first one copies the FieldToCopy in a new column, the second creates a new column full of lists each containing a copy of FieldToCopy. 

    Friday, October 28, 2016 1:06 AM
  • It adds up, but not the way you're written the first version of testThis. Should be:

    testThis = Table.AddColumn("myTable", "myNewColumnName", each "myTable"[FieldToCopy])

    Friday, October 28, 2016 1:16 AM
  • I don't understand what you're trying to do here.

    In your function-example given in your first post, the last step would return one list.

    The exercises on your last post would return multiple results, as they operate on each record of your table. Is this the step where you want to call your first function? (Then you should expect a list/column with lists returned in every row)

    Or are you still trying to get your last line of the function from above? Then you shouldn't use the Table.AddColumn, but this:

    lstFieldKey = Table.Column(tblFieldKey, ReferenceToWhereTheVariableComesFrom)


    Imke Feldmann TheBIccountant.com

    Saturday, October 29, 2016 6:20 AM
    Moderator
  • Have you been able to solve this issue?

    Please mark the answer as answer that helped solving your issue.


    Imke Feldmann TheBIccountant.com

    Saturday, November 19, 2016 7:17 AM
    Moderator
  • I got it working by changing the name of the Column I am processing to a fixed literal like C1 and then using C1 in square brackets. At last step change the name of the column back from C1 to original Col name.

     #"Renamed Columns" = Table.RenameColumns(PrevStep,{{HdrColName, "C1"}}),

    #"Process Step" = Table.ReplaceValue(#"Renamed Columns", each [Field2], each [C1],Replacer.ReplaceValue, {"Field2"}),

    #"Renamed Columns2" = Table.RenameColumns(#"Process Step",{{"C1", HdrColName}})

    Thursday, October 3, 2019 10:03 PM