Combine Rows into a Delimited List

This is the first of two related posts that demonstrate how to use Power Query to deal with rows and delimited lists. In this first post, we’ll combine rows into a delimited list. In the second post, we’ll do the opposite and convert a delimited list into rows. Well, what are we waiting for … let’s get to it!


Let’s say we’ve exported data from our accounting system. Rather than displaying one row for each record, our system uses many rows for each order. Plus, the orders have a variable number of rows. In other words, some orders have 4 rows while others have 3 and so on. This is illustrated below:

But, what we need is a single row per OrderID, with the Attribute and Value strings combined in a single delimited list. Basically, we want it to look like this:

As with anything in Excel, there are multiple ways to accomplish this task. Currently, I’m on a Power Query kick, so this post will demonstrate how to do it with Power Query.

Here are the steps:

  • Create our basic query
  • Do a few transformations
  • Return the data to our worksheet

Let’s get to it.

Note: The steps below are presented with Excel for Windows 2016. If you are using a different version of Excel, please note that the features presented may not be available or you may need to download and install the Power Query Add-in.

Create our basic query

First, we need to get our data table from our worksheet into Power Query. So, we select any cell in the table and click Data > From Table/Range. And, just like that, we have our data loaded into the Power Query window, as shown below.

Now the fun begins 🙂

Do a few transformations

In this case, we want to retain both the Attribute and Value text, so, we’ll combine them into a single column and use a colon : delimiter. We do this by selecting both the Attribute and Value columns at the same time (Ctrl + click) and then select Transform > Merge Columns. The Merge Columns dialog is displayed, we pick the colon Separator and set the new combined column name to Merged, as shown below.

We click OK, and the updated query is shown below.

Now, we need to create one row for each OrderID. We can do this by clicking the Transform > Group By command. The Group By dialog is displayed. We want to Group by the OrderID column and we want the new column to be named Data and to contain All Rows, as shown below.

We click OK and the updated query is shown below.

Now, this is the cool part. And this part I learned from Ken Puls and Miguel Escobar during their workshop, which, was totally awesome by the way 🙂 They taught a bunch of the content from their wonderful book called M is for (Data) Monkey, which, I highly recommend.

We need to create a new column, so, we select Add Column > Custom Column. The Custom Column dialog opens where we specify any column name and then write the following formula:

  • Where [Data] is the name of the table column, and “Merged” is the column name we set up previously.
  • Note: if you used different names then you’ll want to update the formula accordingly.

Now, this creates a new list column, as shown below.

Now, from here, we click the Expand icon on the right side of the Custom header and select Extract Values as shown below.

This displays the Extra values from list dialog, where we specify our desired delimiter, in this case a Semicolon, as shown below.

We click OK … and Bam! (shown below)

Since we really don’t need the Data column any longer, we can select the column and then use the Remove Columns command. Now, we are ready to send the results to Excel.

Return the data to our worksheet

This part is really easy. We just click Close & Load, and now we have the results loaded into a worksheet, as shown below.

Now, the best part about this approach is that tomorrow, next week, or next month when we have an updated export table, we just right-click the results table and hit Refresh. And done. No need to go through these transformation steps again. Booya!

If you’d like to practice, feel free to check out the Sample File below.

And in the next post, we’ll do the reverse and assume our export contained a delimited list that we need to expand into multiple rows.

If you have any related Power Query tips, please share by posting a comment below.

Sample File

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.


  1. Frances Reed on June 1, 2018 at 12:01 pm

    Cool Transformation!

  2. Mary on February 12, 2019 at 10:58 am

    I have another situation:
    I have a column with names of projects, and another column with names of locations. There is a 3rd column which says, main project or sub project. My wish is to have 1 row for 1 project with locations merged (separated by comma).

    could you pls help me?

  3. Tahir Mehmood on March 3, 2019 at 9:16 am

    I have a list of students and their registration of courses. I want to select the course(s) and unique list of courses without students clashes with other courses. Can you help me in this regard how I can prepare exam schedule without clash of students/courses.


  4. Daniel on November 5, 2019 at 9:04 pm

    great hack! Thanks for sharing

  5. Sheridan Heinze on November 11, 2019 at 2:32 am

    i have 3 columns – student name, buddy first name and buddy surname. each buddy has multiple students and I need to group them together. eg buddy Sheridan has students sam, john and lisa allocated to her. ( sam john and lisa on seperate lines of the spreadsheet)( hope this makes sense) I want to use the list to make name tags and at present when I merge I have to cut and paste the students names. I can sort them so they are together and easily see how many students each buddy has but this is no good for my mailmerge.
    so i guess from my 3 columns, I want 2 columns that read ….Sheridan and sam, john, lisa
    I can get as far as having 2 columns in power query . 1 with my buddy names and the second with a list of tables containing the student names, so I’m on the right track, I just can’t now get the names from the table to a comma separated list.
    Please help!!

  6. Al on March 21, 2020 at 10:14 am

    Great solution! How would I reverse the solution, assuming I started with the result?

  7. Manish Agarwal on October 8, 2020 at 10:17 am

    Hi Jeff,

    Amazing work …
    I am trying to connect to outlook, and when i do that, I see an email with more that one attachement, so the Attachments column appears as a table which has multiple rows. When I expmad it, then each file name goes into a separate row. What i want to achieve is that all the file names of the attachment come together in one cell separated by a delimiter. I trid the above steps but I guess the “Data” column that you are referring is a list and not a record. Any Idea on how can I do this with my situation?


  8. Vincent LeBlanc on October 29, 2020 at 10:24 am

    Thanks, that saved my butt from hours of work. Cheers!

  9. Mark Biegert on March 2, 2021 at 12:59 pm

    Excellent post! I made use of this method this morning.

  10. Dan Brill on February 1, 2022 at 4:49 pm

    I just have this page permanently bookmarked. I seem to need this every 6 months or so but can never remember the syntax. But I know it can be done and know where to look. 🙂 Thanks! This is such a great technique.

  11. Angela on September 22, 2022 at 5:23 pm

    Is there a way to do this (very helpful by the way!!!) and not have any duplicates? E.g. I’m getting something like A, A, A, B, Q and I want A,B,Q

  12. John on November 17, 2023 at 5:26 am

    Fantastic ! Much obliged, I don’t think I would ever have worked this out

Leave a Comment