Add Text to an Existing Column in Power Query

Add some text or value to an existing column of values. Example like adding "ABC" to the start of all values in a [Name] column

me@jaykilleen.com wrote this over 1 year ago and it was last updated over 1 year ago.


← Back to the Posts

This snippet below will add the text "ABC" to all the values in the [name] column in an existing table.

StepName = Table.TransformColumns(
  Source,
  {
    {
      "name", 
      each 
        Text.Combine({"ABC",(_)}),
      type text
    }
  }
)

This works by using the Table.TransformColumns method and the _ underscore function that returns the value of the existing row. Match this with the each function to apply a function to each record of a list of records (ie a column) and you can do many things to that existing column of values.

This is really handy so that you don't have to add a column, rename the column and then remove the old column.

Before hand, if I wanted to simply add some text to the start of an existing column of text, I would do something like:

  AddColumn = Table.AddColumn(Source, "new_name", each "ABC" & [name]),
  RenameColumn = Table.RenameColumns(AddColumn,{{"name", "old_name"}, {"new_name", "name"}}),
  RemoveColumn = Table.RemoveColumns(RenameColumn,{"old_name"}),

Similar