Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Power Query – Combine rows into a single cell

Featured Image - Combine rows into a single cell

Last week we saw how to split a delimited cell into separate rows.  This week we are going to do the opposite; combine rows into a single cell with Power Query.

While having a flat table is excellent for data manipulation, but it’s not always the way a user wants to view the information.  If we’re going to combine this data with other sources, then a summarized single cell is often a better option.

Download the example file

I recommend you download the example file for this post.  Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download Icon
Download the file: 0024 Combine rows into a single cell.zip

Watch the video:

Watch the video on YouTube

Scenario

Here is the data from the example file.  We will be using this throughout the post.

Scenario lists of equipment and attributes

In the screenshot above, we have a list of employees, along with any equipment the company has allocated to them.  We can see that Anna Watkins has a laptop, access card, phone, and home printer.

The goal is to combine all the equipment information for each employee into a single cell (as shown below).

Data following transformation

This all sounds simple, doesn’t it?  Before Power Query, we would need to use complex formulas to achieve this result.  But with Power Query, it becomes a few simple transformations.

OK, let’s get started.

Load data into Power Query

Select a cell within the data table, then click Data -> From Table / Range

Data from Table Range

The Power Query window will open and show the data from the table.

Data loaded into Power Query

Now we’re ready to start the transformations.

First, we will combine Equipment and Equipment Attribute into a single column, then secondly, combine the rows into a single cell for each individual.

Combine columns

Select the Equipment and Equipment Attribute columns, then click Transform -> Merge Columns

Merge Columns from Transform Ribbon

For our example, we want to place a colon and space between the two column values.  So, we need to select –Custom– from the Separator drop-down list, and in the box below enter Colon ( : ), followed by a space character.

Merge Columns dialog box using Colon space

I’ve given the new column the name Equipment; then we can click OK.

The Equipment and Equipment Attribute columns have now been combined into one, as shown by the screenshot below.

Power Query 2 Columns Merged Into 1

Combine rows into a single cell

Now for the exciting part.

In our next transformation, we will create an error.  But don’t worry, because I’ll explain why it occurs and what we do about it.

Group by: Sum

Select all the columns, except the column to be combined, in our example, we need to select the First Name and Last Name columns.

Click Transform -> Group By

Transform Group By from Ribbon

The Group By dialog box will open.   The fields to complete are:

  • New column name: Enter a name column of combined cells (I’ve used Equipment)
  • Operation: Select Sum from the drop-down list
  • Column: Select the column to be reduced from rows into a single cell (Equipment.1 in our example)

Then click OK.

Equipment is a text column, and we have selected the Sum operation.  This creates an error, as we cannot sum text.  The operation drop-down has many options: Sum, Average, Median, Min, Max, Count, Count Distinct Rows, and All Rows.  None of these are the option we want.

We will use Sum as a placeholder function, then edit the generated M code to change the List.Sum function for a Text.Combine function.

As you can see below, our column now contains errors.

Equipment Column with Error

Editing the M Code

Next, we are going to edit the M code in the formula bar.

Show Formula Bar with the Sum operation

Hopefully, the formula bar is visible between the ribbon and the data preview window.  If it is not, click View -> Formula Bar to enable it.

View Formula Bar

Power Query’s formula bar is like the one in Excel.  However, the formulas must be   M Code, rather than standard Excel formulas.

You can learn more about M Code functions here: https://docs.microsoft.com/en-gb/powerquery-m/power-query-m-function-reference

We will be using the Text.Combine function: https://docs.microsoft.com/en-gb/powerquery-m/text-combine

Text.Combine has the following syntax:

Text.Combine(texts as list, optional separator as nullable text) as text
  • Texts: needs to be a list of text strings, which is what we have, because each individual has a list of equipment
  • Separator: needs to be a text string which is used to separate each item

So, if we wanted to separate by a semi-colon ( ; ) and a space character, the formula would be:

Text.Combine([Equipment.1], "; ")

Which means in the formula bar, we change the M code from this:

= Table.Group(#"Merged Columns", {"First Name", "Last Name"}, 
    {{"Equipment", each List.Sum([Equipment.1]), type text}})

To this:

= Table.Group(#"Merged Columns", {"First Name", "Last Name"}, 
    {{"Equipment", each Text.Combine([Equipment.1], "; "), type text}})

Now the preview window has reduced each person’s equipment down to a single cell. AMAZING!

Combined data into a delimited cell



Load data into Excel

That’s it.  We’re done.  Let’s push the table back into Excel.

Click Home -> Close & Load (drop-down) -> Close and Load To…

Home Close and Load To

The Power Query Editor will close, and the view returns to Excel.  In the Import Data dialog box, select to load a table into cell F1 of the existing worksheet.

Close & Load to Existing Worksheet

Click OK to close the Import Data options box.

The final data in Excel looks like this:

Data following transformation

Conclusion

What would have been a complex scenario, has been made simple with Power Query.  Having to change the M Code, is a bit trickier than we might like, but it’s still significantly easier than in standard Excel.


Related pages which may be useful to you are:



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

26 thoughts on “Power Query – Combine rows into a single cell

  1. adisilatongga says:

    How about the number? I just got the error for the number which is not in the string:
    Expression.Error: We cannot convert the value 896 to type Text.

  2. Haz says:

    Is there a way we can show the values as :
    | A | B
    ———————————
    1 | Name_1 | Address Line 1
    | | Address Line 2
    | | Address Line 3
    ———————————
    2 | Name_2 | Address Line 1
    | | Address Line 2
    | | Address Line 3

    instead of showing:

    Name 1 | Address Line 1, Address Line 2, Address Line 3
    Name 2 | Address Line 1, Address Line 2, Address Line 3

    Is there anyway to return multiple values in new line? Instead of using comma to separate the values?

  3. jon says:

    in case this is non-obvious to some other users – you can also add multiple columns by using the same Text.Combine term, i.e.

    #”Grouped Rows” = Table.Group(#”Filtered Rows1″, {“Site Name”}, {{“Email list”, each Text.Combine([True Email],”;”), type text}, {“Name list”, each Text.Combine([True Name],”;”), type text}})

  4. Mudassir Khan says:

    Is there a way to merge the duplicate rows into one row instead of a single cell.
    Following your example, the result of your example shows all combine data in one Cell H2 but what I want is to show the data in H2 to Z2.
    Thank you.

    • Excel Off The Grid says:

      Once it’s combined in a single Cell you can then use the Split options to separate it into different columns.

      But the Pivot option may be a better fit for what you’re looking for.

  5. sjohri says:

    Really nice guide. Thanks for putting this together. Found it very useful and the same logic could potentially be leveraged with other functions.

  6. Bopanna says:

    This is extremely helpful. Allowed me to combine a set of multiple RFP questions arising out of a single requirement. Thanks a ton!

  7. Larissa Paredes Muse says:

    The formula just worked for me when I add #”content.State”.

    {{“State”, each Text.Combine([#”Content.State”], “; “)

  8. Sven H says:

    I sent this comment several days ago but since it hasn’t been published yet I assume there has been a problem.

    Thanks for the tip! It solved a problem I had.
    But when I tried with your first example (ABC 1 –> XYZ 5) it didn’t work. No error condition occurred so the values were summed. The solution was to change the data type of the number column to “Text” in PowerQuery. Of course, you also have to skip the step of merging columns.

Leave a Reply

Your email address will not be published.