Concatenate (not merge) columns from a table of header names in Power Query

Question

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 |

I have no idea yet what you want (Yet) but why not duplicate the columns and merge the duplicates, so they originals are left behind when the duplicates disappear? — horseyride
– horseyride, Commented Aug 29, 2022 at 20:32
yeah, that seems like it would work, but it leaves me in more-or-less the same position. How to duplicate columns based on my mergedHeaders table — xyz333
– xyz333, Commented Aug 29, 2022 at 22:29

Ron Rosenfeld · Accepted Answer · 2022-08-30 02:45:42Z

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

Collectives™ on Stack Overflow

Concatenate (not merge) columns from a table of header names in Power Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related