How to combine rows in Power Query

In this post, we look at how to combine rows with Power Query. The user interface gives us a way to merge columns, but there is no Power Query merge rows transformation. Therefore, I will show you how to achieve it in this post.

There are two common scenarios in which we need to merge rows:

  • Merge all rows in the data set
  • Merge specific rows (e.g., header rows)

Both are covered in this post.

Table of Contents

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0024 Power Query – Combine rows into a single cell.zip

Watch the video:

Power Query - Combine rows into a single cell | Change data to readable format | Excel Off The Grid

Watch the video on YouTube

Power Query combine rows across the entire dataset

While having a flat table is excellent for data manipulation, that may not always be optimal:

  • It’s not how users wants to view the information
  • May need to optimize for merging with other data sources

Therefore, even if we have a structured data layout, we may still need to combine rows.

Scenario

Here is the data from the example file:

Scenario lists of equipment and attributes

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

The goal is to combine all 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 opens and displays the data from the Table.

Data loaded into Power Query

Now we’re ready to start the transformations.

Initially, we will combine Equipment and Equipment Attribute into a single column, then combine the rows into a single row for each employee.

Merge 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 columns. 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 are combined into one, as shown by the screenshot below.

Power Query 2 Columns Merged Into 1

Group by with Sum

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

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 opens. The fields to complete are:

  • New column name: Enter a name for the 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.

Equipment Column with Error

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

The operation drop-down has many options: Sum, Average, Median, Min, Max, Count, Count Distinct Rows, and All Rows. Unfortunately, none of these is the option we want.

But don’t worry; we used Sum as a placeholder function, we will fix the error in the next step.

Editing the M Code

Next, we edit the M code in the formula bar to change the List.Sum function for the Text.Combine function.

Show Formula Bar with the Sum operation

Hopefully, the formula bar is visible; if not, click View > Formula Bar to enable it.

View Formula Bar

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

We are using the Text.Combine function to merge the text: 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 the text string 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], "; ")

This 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 data back into Excel.

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

Home Close and Load To

The Power Query Editor closes, 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 dialog box.

The final data in Excel looks like this:

Data following transformation

Power Query merge specific rows

The second scenario occurs when we want to combine specific rows. This typically occurs when there are multiple header rows in our source data. Since Power Query can only have a single header row, we must combine the row text into a single row.

Scenario

Here is the data from the example file.

Scenario 2 Data

In the screenshot above, we have multiple header rows. For example, we can see that First and Name are used to describe the cells in column A.

The goal is to concatenate the text into a single row (as shown below).

Headers - Merged rows in Power Query

OK, let’s give this a try.

Convert the data into a Table

Our data is not currently in a Table format. Select any cell in the data and click Insert > Table (or press Ctrl + T).

Create Table Dialog box

In the Create Table dialog box, ensure the entire data range is included, then click OK.

Next, select a cell in the Table. Then, click Table Design > Table Name and provide the Table with a meaningful name. For this example, I have chosen tblEmployeeEquipment.

Rename a Table

NOTE: If you do not want to convert the source information into a Table, the alternatives are: (1) create a named range that includes the data, (2) connect to the data from another workbook.

Load the data into Power Query

Select a cell within the Table, then click Data > From Table / Range. The Power Query window opens and shows a preview of the data.

Multiple header rows data included in Power Query

Now we’re ready to start the transformations.

Data preparation

Depending on your default settings, the Applied Steps box may include a step called Changed Type; if so, delete this step. We do not need it.

Because our data has come from a Table, the header row is automatically used as the header row in Power Query. We don’t want this, so click Transform > Use first row as headers (drop-down) > Use headers as first row

Transform - Use Headers as first row

Transpose, merge columns, transpose

There is no merge rows transformation in Power Query. However, if we transpose data, rows become columns. Which means we can use the merge columns transformation instead.

Click Transform > Transpose.

Transform - Transpose

Select Column1 and Column2, then click Transform > Merge columns.

In the Merge Columns dialog box, enter the following:

  • Separator: Space
  • New Column Name: Header Row
Merge columns dialog box using space character

The preview window should display the following:

Scenario 2 - Preview window after Merge Columns

The Equipment column only had a single header row; therefore, the text has been combined with the words Column1 to give a value of Column1 Equipment. Let’s clean that up.

With the Header Row column selected, click Transform > Replace Values. Replace “Column1 ” with blank.

Replace values to clean the column headers

NOTE: Make sure Column1 has a space character after it.

Transpose the data back by clicking Transform > Transpose.

Next, promote headers by clicking Transform > Use first row as headers.

The preview window should look like this. PERFECT!

Preview Window after Power Query merge rows

Load data into Excel

We are done. We can now load the data into Excel. Click Home > Close & Load (drop-down) > Close and Load To…

Using the Import Data dialog box, load a Table into cell F1 of the existing worksheet.

The final data in Excel looks like this:

Headers - Merged rows in Power Query

Conclusion

What would have been complex scenarios for Excel have been made simple with Power Query. However, to combine rows in Power Query takes a slightly different mindset. Therefore, we need to understand the transformations available and how we can join them to achieve the desired results.

Related posts:


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

3 thoughts on “How to combine rows in Power Query”

  1. Hi,

    Thanks a lot for your explanation.
    In addition, how can you retain all the other columns in the table, for the sake of clarity ?
    Thanks in advance

    Reply

Leave a Comment