Add Prefix from one column to another in place

[ChemEnger]

I have two columns in a table, named Heading and Prefix.  I am trying to add the Prefix in a row to the Heading column in the same row, in place (rather than add a merged column with Table.CombineColumns, delete & rename etc.)

I have tried using the TransformColumns command:

Table.TransformColumns(#"Expanded Albumin Prefixes", {{"Heading", each [Prefix] & _, type text}})

 but get a 'We cannot apply field access to the type Text' error:

(Start date is the row value in the Heading column)

I have also tried

Text.From([Prefix])

and

Record.Field(_, "Prefix")

but get the same error.

This feels like it should be easier!  What's most annoying is that I can very easily add a column (Table.AddColumn) with exactly what I need 😠

Edit:  The reason I am trying to achieve this is I am bringing in data from an Excel workbook with multiple worksheets and I can have duplicate [Heading]s - I need to differentiate them by adding the [Prefix]

[BA_Pete · Accepted solution]

Hi @ChemEnger ,

Select you prefix field, then ctrl+click your heading field.

Go to Transform tab > Merge Columns.

For reference, this should generate the following code if no delimiter is specified:

Table.CombineColumns(
  previousStep,
  {"Prefix", "Heading"},
  Combiner.CombineTextByDelimiter("", QuoteStyle.None),
  "newColumnName"
)

Pete

[CNENFRNL · Accepted solution]

Simple enough

= Table.ReplaceValue(Source, each [Heading], each [Prefix] & [Heading], Replacer.ReplaceText, {"Heading"})

[ChemEnger]

@CNENFRNL I was definitely over-thinking it!  This is indeed 'Simple enough' and worked well, thank you.

[BA_Pete · Accepted solution]

Hi @ChemEnger ,

Select you prefix field, then ctrl+click your heading field.

Go to Transform tab > Merge Columns.

For reference, this should generate the following code if no delimiter is specified:

Table.CombineColumns(
  previousStep,
  {"Prefix", "Heading"},
  Combiner.CombineTextByDelimiter("", QuoteStyle.None),
  "newColumnName"
)

Pete

[ChemEnger]

Thanks @BA_Pete.  Unfortunately, this will create a new combined column (newColumnName in your example) and I will then need to delete the two original columns & rename the new column, but I think this is the only way to get what I need.

I was trying (hoping!) to do this in one step (TransformColumns) rather than three (CombineColumns- RemoveColumns-RenameColumns)

Simon

[BA_Pete]

Hi @ChemEnger ,

No, this will not add any new columns. It will turn your two original columns into one single column with a new name that you choose for it to have.

If you're doing it via the GUI rather than manually coding, thn make sure you go to Merge Columns on the Transform tab, not the Add Column tab.

Pete

[ChemEnger]

Hi @BA_Pete,  Apologies for my misunderstanding - changing newColumnName for the original Heading did the trick, basically replacing the old column with the updated version.

Simon

[BA_Pete]

Yes, and removing your [prefix] column at the same time as you implied you didn't want to have to delete it afterwards.

Pete

[watkinnc]

Have you tried:

 each [Prefix] & [Heading], type text}})

--Nate 

[ChemEnger]

Hi @watkinnc,

I have now 😉  Same error I'm afraid.  If I hard-type the [Prefix] part as say "123" then the error moves on to the [Heading] part of the command.