Forum Discussion

ChemEnger's avatar
ChemEnger
Icon for Advocate V rankAdvocate V
4 years ago
Solved

Add Prefix from one column to another in place

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]

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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's avatar
    CNENFRNL
    4 years ago

    Simple enough

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

9 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      ChemEnger
      Icon for Advocate V rankAdvocate V
      4 years ago

      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's avatar
        BA_Pete
        Icon for Super User rankSuper User
        4 years ago

        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

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Simple enough

    = Table.ReplaceValue(Source, each [Heading], each [Prefix] & [Heading], Replacer.ReplaceText, {"Heading"})
    • ChemEnger's avatar
      ChemEnger
      Icon for Advocate V rankAdvocate V
      4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Have you tried:

     

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

     

    --Nate 

    • ChemEnger's avatar
      ChemEnger
      Icon for Advocate V rankAdvocate V
      4 years ago

      Hi Anonymous,

       

      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.