Microsoft Software

How to combine values from a column into a single cell using Microsoft Excel’s Power Query

Sometimes the present Microsoft Excel knowledge construction doesn’t match necessities of different customers and software program. Learn how to combine columnar values into a single cell using Microsoft Excel’s Power Query.

Microsoft Excel logo on textured paper
Image: Renan/Adobe Stock

The article How to concatenate values in a single Excel column to a single row reveals you ways to combine columnar knowledge into a single Excel cell. It’s an odd method, however knowledge makes use of usually require a totally different construction than we’re used to using in Microsoft Excel.

Fortunately, it isn’t tough to transpose column values into a single cell so long as you don’t need to group the outcomes. In that state of affairs, you should use Microsoft Excel Power Query. In this tutorial, I’ll present you ways to use Microsoft Excel Power Query to combine values into a single cell, inside teams.

SEE: Windows, Linux, and Mac instructions everybody wants to know (free PDF) (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system. Power Query is obtainable as a part of the interface by way of Excel 2016. Excel for the online doesn’t assist Power Query, however you possibly can run current queries in an Excel workbook.

How to load the Excel knowledge into Power Query

When using Microsoft Excel’s Power Query, step one is to load the Excel knowledge into Power Query. If you’re not working with a Table object, Power Query will immediate you to convert the info vary to a Table as a result of Power Query works solely with Excel Table objects.

The instance knowledge is in a Table named TableGross sales. You don’t have to title the Table, however a descriptive title does make a Table simpler to work with, particularly when there’s multiple.

Let’s begin by including the info in TableGross sales to Power Query:

  1. Click wherever contained in the Table.
  2. Click the Data tab.
  3. In the Get & Transform knowledge group, click on From Table/Range.

That’s all there’s to it. Figure A reveals the info in Power Query.

Figure A

Transfer the Excel Table into Power Query.

How to add a new column in Power Query

Our objective is to combine the t-shirt gross sales by shopper into a single cell for every shopper. Doing so entails grouping by the purchasers. For instance, for Client 1, we would like one row with a column that mixes the gross sales as follows: Medium: 50, X Large: 20, Small 15. To accomplish this, we’d like to add a new column for the mixed values as follows:

  1. Hold down the Shift key as you click on the Size and Quantity columns to choose each.
  2. Click Transform within the Table group after which click on Columns Merge within the Text Column group.
  3. In the ensuing dialog, select Custom from the Separator dropdown. We’ll use customized as a result of we would like to use a colon and area because the separator and that possibility doesn’t exist within the dropdown.
  4. In the following management, enter the character you need to separate the dimensions and amount. I entered a colon and a area character. You can’t see the area, it’s there.
  5. Enter a significant title, SizeQuantity, for the brand new column (Figure B).
  6. Click OK to see the outcomes proven in Figure C.

Figure B

Add a new column for the mixed measurement and amount values.

Figure C

Power Query combines the dimensions and amount values for every report.

As you possibly can see, this activity mixed the dimensions and column worth for every report. Now, we would like to combine these values into one row for every shopper. That entails grouping.

How to group in Power Query

Right now, we’ve got the dimensions and amount values mixed in a single cell for every report. The subsequent step is to group the info in Power Query by the Buyer (shopper) values, as follows:

  1. Select the header for the Buyer column to choose it.
  2. Click Group By within the Table group. In the ensuing dialog, the Basic possibility is the default as a result of the choice comprises just one column.
  3. Identify the column by getting into SizeQuantity1 within the New Column Name discipline.
  4. From the Operation dropdown, select Sum. This will generate an error, however we’ll modify the components immediately to appropriate it.
  5. Choose SizeQuantity from the Column dropdown (Figure D).
  6. Click OK.

Figure D

Configure a new column for combining the dimensions and high quality values for every shopper into one cell.

Figure E

The Sum operator returns an error.

As you possibly can see in Figure E, the duty grouped the purchasers, however the Sum operator returns an error. To appropriate this error, we should modify the components. Specifically, we should exchange List.Sum with Text.Combine, which requires a separator argument.

To add this character, place the cursor after the closing bracket following SizeQuantity1 and earlier than the closing parenthesis and enter the next as proven in Figure F: , "; ".

Figure F

Modify the components to combine the textual content values for every SizeQuantity1 worth.

After restructuring the info, you possibly can return it to an Excel sheet.

How to load the Power Query knowledge into Microsoft Excel

Power Query restructured the info as required. You have one report for every shopper and a cell that comprises all the dimensions and amount knowledge for the corresponding shopper. At this level, you possibly can load it again into Microsoft Excel as follows:

  1. In Power Query, click on the Home tab.
  2. In the Close group, click on Close & Load.

Figure G

Return the info to Excel.

As you possibly can see in Figure G, Power Query saves the desk in a new Excel sheet. At this level, you should use the info or move it alongside.

Using Power Query to restructure Microsoft Excel knowledge requires no expressions or capabilities. You add a column for the mixed values after which group if you would like to combine these values additional into a single report.


Leave a Reply

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