- #Merge duplicate rows in excel while keeping all text how to#
- #Merge duplicate rows in excel while keeping all text install#
- #Merge duplicate rows in excel while keeping all text windows#
The Custom Column dialog opens where we specify any column name and then write the following formula: =Table.Column(,"Merged") We need to create a new column, so, we select Add Column > Custom Column. 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 click OK and the updated query is shown below. 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 can do this by clicking the Transform > Group By command. Now, we need to create one row for each OrderID.
We click OK, and the updated query is shown below. The Merge Columns dialog is displayed, we pick the colon Separator and set the new combined column name to Merged, as shown below. We do this by selecting both the Attribute and Value columns at the same time (Ctrl + click) and then select Transform > Merge Columns. 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. Now the fun begins ? Do a few transformations And, just like that, we have our data loaded into the Power Query window, as shown below. So, we select any cell in the table and click Data > From Table/Range. Create our basic queryįirst, we need to get our data table from our worksheet into Power Query.
#Merge duplicate rows in excel while keeping all text install#
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.
#Merge duplicate rows in excel while keeping all text windows#
Note: The steps below are presented with Excel for Windows 2016.
#Merge duplicate rows in excel while keeping all text how to#
Currently, I’m on a Power Query kick, so this post will demonstrate how to do it with Power Query. Basically, we want it to look like this:Īs with anything in Excel, there are multiple ways to accomplish this task. This is illustrated below:īut, what we need is a single row per OrderID, with the Attribute and Value strings combined in a single delimited list. In other words, some orders have 4 rows while others have 3 and so on. Plus, the orders have a variable number of rows. Rather than displaying one row for each record, our system uses many rows for each order. Let’s say we’ve exported data from our accounting system.
Well, what are we waiting for … let’s get to it! Objective In the second post, we’ll do the opposite and convert a delimited list into rows. In this first post, we’ll combine rows into a delimited list. As an example, if cells A2 and B2 contain "David" and "Smith," respectively, begin entering "David Smith" or "Smith, David" and press "Enter."Ĭlick and drag your mouse across the two original column headers, right-click the selection and select "Delete" to remove the columns without affecting the data in the consolidated column.This is the first of two related posts that demonstrate how to use Power Query to deal with rows and delimited lists. When you see the suggestion, press "Enter" to automatically combine the data in both columns for each row and continue using your format. This time, Excel suggests the value as you type.
The order and punctuation doesn't matter Excel will automatically recognize that you're combining the data and mimic your format. As an example, if you had "John" in cell A1 and "Doe" in cell B1, type "John Doe" or "Doe, John" (without quotes here and throughout) in cell C1. Type the data from the first cells of the original two columns in the first cell of the newly created column. As an example, if the data exists in columns A and B, right-click column header "C" and select "Insert." Right-click the column header immediately to the right of the second column and select "Insert" to add a new column, if necessary.