Merge data from multiple cells into a single cell

{ 10 Comments }

Assume a simple two column dataset with Name in column A and Department in column B.  There are repetitions in the Name column.  Here’s a snapshot of the base data:

One may want to merge data from multiple cells into a single cell – basically a view where all unique names are listed in a column and all departments for each name appear in a single cell (separated by commas).  The expected result should look like this:

You may view my solution in this workbook.

You may also watch a short video of my solution here

To accomplish the reverse of this i.e. start at Table 2, process the dataset and arrive at Table1, you may refer to my solution at this link.

Leave a Comment

Your email address will not be published.

*

  • Hi,
    Nice solution, but there is one drawback: if a new department comes, the refresh will create a new column instead of concatenating it with the other ones. Is there any way to make it dynamic with any number of (changing) departments?
    Thanks!

    • Hi,

      Thank you. It works fine for me. In row 14, of sheet1, I added T and Cash in cells columns A and B respectively. Thereafter, when I refreshed the Power Query output, the result in cell B7 was gbnm,Cash

  • Hi Ashish,
    I’m puzzled – how did you do that without re-doing the whole query/steps?
    When I get your book, the PQ formula is

    = Table.AddColumn(#”Pivoted Column”, “Merged”, each Text.Combine({[qw], [we], [er], [rt], [ty], [yu], [ui], [i], [p], [df], [gbnm]}, ” “), type text)

    there’s no way a new column [Cash] gets inserted in there dynamically – did you use any trick?
    just in case, I’m with Office 2010 + PowerQuery 2.21.3974.242
    Thanks!

    • Hi,

      No trick at all. I just refreshed. I am using Excel 2013 Professional Plus version. I think you have an outdated version of Power Query – the latest one is 2.22.4007.242. Download and install the latest version.

  • Hi,
    I just updated and did the same – doesn’t work, I get a new column [Cash]
    Could you please show us the Editor code that you get after adding the new row?
    Mine is

    let
    Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
    #”Duplicated Column” = Table.DuplicateColumn(Source, “Department”, “Copy of Department”),
    #”Pivoted Column” = Table.Pivot(#”Duplicated Column”, List.Distinct(#”Duplicated Column”[#”Copy of Department”]), “Copy of Department”, “Department”),
    #”Inserted Merged Column” = Table.AddColumn(#”Pivoted Column”, “Merged”, each Text.Combine({[qw], [we], [er], [rt], [ty], [yu], [ui], [i], [p], [df], [gbnm]}, ” “), type text),
    #”Trimmed Text” = Table.TransformColumns(#”Inserted Merged Column”,{{“Merged”, Text.Trim}}),
    #”Replaced Value” = Table.ReplaceValue(#”Trimmed Text”,” “,”,”,Replacer.ReplaceText,{“Merged”}),
    #”Removed Columns” = Table.RemoveColumns(#”Replaced Value”,{“qw”, “we”, “er”, “rt”, “ty”, “yu”, “ui”, “i”, “p”, “df”, “gbnm”})
    in
    #”Removed Columns”

    And does not change when adding new rows to the data, so when a new [Department] element appears, a new column is added to the output.

    Did you only refresh the output, or re-did all the steps? Could you show us all the code?

    Thanks a lot!

    • Hi,

      Yes, you are correct. It is creating another column. I can almost swear that last time, it did not add another column – I tried it. Out of ideas now.

      • Hi Ashish,
        I’ve done it! It took me a long read to the very extensive documentation from the PowerQuery language, but it has served me to learn a bit of the PQ (a.k.a. “M”) language, which is very, very powerful. Truth is, very little of the language is exposed through the PQ user interface, but after a bit of fiddling, I’ve found a GENERIC way to do it properly. Only thing to change from the code: the Source I’m quite happy with it!
        The full PQ documentation is at (check the linked PDFs there)

        https://support.office.com/en-us/article/Learn-about-Power-Query-formulas-6bc50988-022b-4799-a709-f8aafdee2b2f

        Finally, here’s the code!
        //
        let
        Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
        #”Grouped Rows” = Table.Group(Source, Table.ColumnNames(Source){0}, {{“temporary_Count”, each _, type table}}),
        #”Added Custom” = Table.AddColumn(
        #”Grouped Rows”,
        “GroupedElements”,
        each Table.ToList(
        Table.Transpose([temporary_Count]),
        Combiner.CombineTextByDelimiter(“,”)
        ){1}
        ),
        #”Removed Columns” = Table.RemoveColumns(#”Added Custom”,{“temporary_Count”})
        in
        #”Removed Columns”

        // What do you think? Neat? 🙂