none
Power Query: Concatenate cells of columns that contains a string value RRS feed

  • Question

  • I have a condition where I want to concatenate columns that match a string but don't know how many of those columns exist.  For example if I have a table with column titles of "Name", "Label", "Label_1", "Label_2", "Label_3".  I am wanted to add a new custom column titled "Merge_Label".  If I have the following table:

    Name Label Label_1 Label_2 Label_3
    Add JIRA Plug-in development pipeline jira pi-2
    Tool Chain Fix defect pi-3 pipeline  

    I use the following code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Added Custom" = Table.AddColumn(Source, "Merge_Label", each List.Transform(Table.ToRows(Table.SelectColumns(Source, List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Labels")))), each Text.Combine(_, "#(cr)#(lf)")))
    in
        #"Added Custom"

    It outputs the following:

    Name Label Label_1 Label_2 Label_3 Merge_Label
    Add JIRA Plug-in development pipeline jira pi-2 List
    Tool Chain Fix defect pi-3 pipeline   List

    But I want the following:

    Name Label Label_1 Label_2 Label_3 Merge_Label
    Add JIRA Plug-in development pipeline jira pi-2 development
    pipeline
    jira
    pi-2
    Tool Chain Fix defect pi-3 pipeline   defect
    pi-3
    pipeline

    Note at any time I could get a "Label_N" column add/deleted from the table so I need a solution that can locate all the columns with values of a specific string concatenate the values in the row and place the value in the new column cell of that row.


    • Edited by Babalu' Friday, July 5, 2019 4:02 AM
    Thursday, July 4, 2019 10:29 PM

Answers

  • Try this one

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Fields = List.Buffer(List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Label"))),
        #"Added Custom" = Table.AddColumn(Source, "Merge_Label", each Text.Combine(Record.ToList(Record.SelectFields(_, Fields)), "#(cr)#(lf)"))
    in
        #"Added Custom"

    • Marked as answer by Babalu' Friday, July 5, 2019 12:52 PM
    Friday, July 5, 2019 8:23 AM

All replies

  • Try this one

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Fields = List.Buffer(List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Label"))),
        #"Added Custom" = Table.AddColumn(Source, "Merge_Label", each Text.Combine(Record.ToList(Record.SelectFields(_, Fields)), "#(cr)#(lf)"))
    in
        #"Added Custom"

    • Marked as answer by Babalu' Friday, July 5, 2019 12:52 PM
    Friday, July 5, 2019 8:23 AM
  • That worked.  Thanks!!!
    Friday, July 5, 2019 12:52 PM