none
Passing a dynamic field/column name to the field identifier in Table.AddColumn RRS feed

  • Question

  • Hi

    If I have a add column but I need to pick up the column name from a user input on the spreadsheet how do I pass the user name to the field identifier?

    so instead of:  Table.AddColumn(DataTable, "NewColName", each [GL_ACCT_CODE])

    To something like:
    UserInputFieldName = #"Replaced Value"{0}[newAccount]
     NewTable =Table.AddColumn(DataTable, "NewColName", each UserInputFieldName) .

    Many thanks

    Wednesday, August 26, 2015 5:21 PM

Answers

  • Try this:

    UserInputFieldName = #"Replaced Value"{0}[newAccount]
    NewTable =Table.AddColumn(DataTable, "NewColName", each Record.Field(_, UserInputFieldName))

    One hint to understanding this is that "each" is just shorthand for "(_) =>" (i.e. a function with one argument called "_"), and [Foo] is just shorthand for _[Foo].

    Ehren


    Monday, August 31, 2015 7:08 PM
    Owner

All replies

  • Hey, you seem to be in to it now :-)

    Interesting request - no idea how to do it directly. But you can create a "RolePlayingColumn" instead that can be controlled by your named range:

    let
        Source = ...,
        #"Duplicated Column" = Table.DuplicateColumn(Source, UserInputFieldName, "RolePlayingCol"),
        #"Added Custom" = Table.AddColumn(#"Duplicated Column", "Custom", each [RolePlayingCol])
    in
        #"Added Custom"

    So you have a "role playing duplicate column" - will always show what has been selected by UserInputFieldName.


    Imke Feldmann TheBIccountant.com


    Wednesday, August 26, 2015 7:08 PM
    Moderator
  • Here is a similar question and answer:

    https://social.technet.microsoft.com/Forums/en-US/95dd4361-cffb-4b12-820a-85c3d041db3e/power-query-query-with-parameter-from-a-cell?forum=powerquery

    Wednesday, August 26, 2015 9:36 PM
  • Hi Greg,

    thanks, but that has been done already:

    "UserInputFieldName = #"Replaced Value"{0}[newAccount]" is basically the short version of it:

    {} before [] means that a value from a named range has been retrieved.

    Problem in this case is how to use this value in square brackets [...]: Just the name as it is doesn't work, neither does framed in "". Do you have an idea here?


    Imke Feldmann TheBIccountant.com

    Thursday, August 27, 2015 3:38 AM
    Moderator
  • Try this:

    UserInputFieldName = #"Replaced Value"{0}[newAccount]
    NewTable =Table.AddColumn(DataTable, "NewColName", each Record.Field(_, UserInputFieldName))

    One hint to understanding this is that "each" is just shorthand for "(_) =>" (i.e. a function with one argument called "_"), and [Foo] is just shorthand for _[Foo].

    Ehren


    Monday, August 31, 2015 7:08 PM
    Owner
  • Thanks this really works well, and it opens up a new dimension enabling users to control the output by populating an input table.

    Having solved this challenge what if I wanted to loop through all the #"Replaced Value"{xx}[newAccount] records to add multiple columns, would it be best to set up a recursive function or is there another "each _" trick I should know about.

    Thanks

    Monday, August 31, 2015 8:52 PM
  • To add multiple columns, you could either use a recursive function, or use List.Accumulate over a list of N items to add N columns.

    Ehren

    Monday, August 31, 2015 9:19 PM
    Owner
  • I think it would also work if you unpivot the columns to be duplicated (actually unpivot others on the ones that should remain) then add you column (you only need one at that time) and then pivot back on these 2 cols: http://www.thebiccountant.com/2015/08/12/how-to-pivot-on-multiple-measurescolumns-in-power-query/

    Might not be as fast as Ehrens approaches, more of a "simple stupid" method :-)


    Imke Feldmann TheBIccountant.com


    Tuesday, September 1, 2015 5:34 AM
    Moderator
  • Hi Greg,

    thanks, but that has been done already:

    "UserInputFieldName = #"Replaced Value"{0}[newAccount]" is basically the short version of it:

    {} before [] means that a value from a named range has been retrieved.

    Problem in this case is how to use this value in square brackets [...]: Just the name as it is doesn't work, neither does framed in "". Do you have an idea here?


    Imke Feldmann TheBIccountant.com

    Hi Imke,

    Did you ever get an answer to this question?  I don't think it has been answered here, but perhaps I just can't see it.

    thanks Andrew


    Friday, February 16, 2018 8:18 AM
  • Yes, to my understanding Ehren has answered it:

    NewTable =Table.AddColumn(DataTable, "NewColName", each Record.Field(_, UserInputFieldName))

    Record.Field(_, UserInputFieldName) references the current row in a table where the column names is "UserInputFieldName" (which can be a variable).

    Is that what you needed?


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, February 16, 2018 10:12 AM
    Moderator