I need to concatenate columns on a pipe | from a table of column names in such a way that keeps the original columns

I can do this with merge, but I lose the original columns

How to do this without losing the columns being contaminated?

Thanks

#"Trimmed Text" = Table.TransformColumns(#"Sorted Rows",{{"Id", Text.Trim, type text}}),
    newMergedHeader = Table.ColumnNames(mergeHeaders){0},
    
    #"Merged Columns" = 
        if newMergedHeader <> null then 
          Table.RenameColumns(
          Table.CombineColumns(#"Trimmed Text",Table.Column(mergeHeaders,newMergedHeader),each Combiner.CombineTextByDelimiter("|", QuoteStyle.None)(List.Difference(List.RemoveNulls(_), {""})),"Merged"),{{"Merged",newMergedHeader}})
        else #"Trimmed Text

Input

| Other Colums | Label              | Segment              | Type   | Other Colums |
|--------------|--------------------|----------------------|--------|--------------|
| stuff        | Alexis Bhat        |                      |        | stuff        |
| stuff        | Alphonse Wilkinson |                      | Person | stuff        |
| stuff        | Bobbi Carter       | Network Blue         |        | stuff        |
| stuff        |                    | Community Healers    | Person | stuff        |
| stuff        | Dannie Cano        | Community Healers    | Person | stuff        |
| stuff        | Darius Neel        | Community Healers    | Person | stuff        |
| stuff        |                    |                      |        | stuff        |
| stuff        | Delores Rana       | Community Healers    | Person | stuff        |
| stuff        | Dionne Merino      | Education Commission | Person | stuff        |

Output

| Other Colums | Label              | Segment              | Type   | Other Colums | Blue                                      |
|--------------|--------------------|----------------------|--------|--------------|-------------------------------------------|
| stuff        | Alexis Bhat        |                      |        | stuff        | Alexis Bhat                               |
| stuff        | Alphonse Wilkinson |                      | Person | stuff        | Alphonse Wilkinson|Person                 |
| stuff        | Bobbi Carter       | Network Blue         |        | stuff        | Bobbi Carter|Network Blue                 |
| stuff        |                    | Community Healers    | Person | stuff        | Community Healers|Person                  |
| stuff        | Dannie Cano        | Community Healers    | Person | stuff        | Community Healers                         |
| stuff        | Darius Neel        | Community Healers    | Person | stuff        | Person                                    |
| stuff        |                    |                      |        | stuff        |                                           |
| stuff        | Delores Rana       | Community Healers    | Person | stuff        | Delores Rana|Community Healers|Person     |
| stuff        | Dionne Merino      | Education Commission | Person | stuff        | Dionne Merino|Education Commission|Person |

CodePudding user response：

If I understand you correctly, you can just add a column that concatenates the columns you wish to "merge" using the pipe | as the delimiter.

The columns to merge, and the merged column header will come from a different table in the workbook.

let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Other Colums", type text}, {"Label", type text}, {"Segment", type text}, {"Type", type text}, {"Other Colums 2", type text}}),

//Replace blank with nulls to more easily ignore when concatenating
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Other Colums", "Label", "Segment", "Type", "Other Colums 2"}),

//Get headers to merge and merged header name from table
    Source2 = Excel.CurrentWorkbook(){[Name="mergeHeaders"]}[Content],
    mergeHeaders = List.RemoveFirstN(Source2[New Concatenation Header Name],1),
    MergedHeaderName = Source2[New Concatenation Header Name]{0},

//add the merged column
    merge = Table.AddColumn(#"Replaced Value", MergedHeaderName, (r)=> Text.Combine(List.RemoveNulls(
            Record.ToList(Record.SelectFields(r,mergeHeaders))           
            ),"|"), type text)

in
    merge

Input

mergeHeaders

Results