comma separated string to list excel for mac

COMMA SEPARATED STRING TO LIST EXCEL FOR MAC HOW TO
COMMA SEPARATED STRING TO LIST EXCEL FOR MAC INSTALL

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.

COMMA SEPARATED STRING TO LIST EXCEL FOR MAC 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. Note: The steps below are presented with Excel for Windows 2016.

COMMA SEPARATED STRING TO LIST EXCEL FOR MAC 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. This is the first of two related posts that demonstrate how to use Power Query to deal with rows and delimited lists.

Comma separated string to list excel for mac