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.

Watch the video:

Watch the video on YouTube

Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

Click below to subscribe and gain access to the subscriber area.  You will also receive:

  • My favorite tips and tricks direct to your inbox
  • Exclusive content (which is only available to subscribers)
  • FREE tools and downloads

Download Icon

If you’re already a subscriber, click here to log-in to the subscriber downloads area.

The filename for this post is 0024 Combine rows into a single cell.zip

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

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

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.

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:

Leave a Reply

Your email address will not be published. Required fields are marked *