Split Delimited List into Rows

This is the second of two related posts that demonstrate how to use Power Query to deal with rows and delimited lists. In the first post, we combined rows into a delimited list. In this post, we’ll do the opposite and convert a delimited list into rows. Well, what are we waiting for … let’s get to it!

Objective

Let’s say our export contains a delimited list of values, as shown below.

But, we need to split the delimited list and put them into rows, like this:

Each OrderID may have a variable number of rows, for example, some orders have 4 rows and some 3. We’ll use Power Query to help us out as follows:

• Get the table into Power Query
• Do the transformations
• Return it to Excel

Let’s just jump right in.

Note: The steps below are presented with Excel for Windows 2016. 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.

Get the table into Power Query

To get the data table into Power Query, we select any cell in the table and select the Data > From Table/Range command. The Power Query window is displayed, as shown below.

Now, it is time to perform the transformations.

Do the transformations

The Custom column for each order contains one or more attribute:value pairs, separated by a colon. Each pair is separated by a semicolon. We want to see each attribute:value pair in one row and two columns. That means that we really need to split the Custom column twice. First, we’ll split the column at each semicolon into rows. Then, we’ll split the column at each colon into columns.

First, let’s split the column at each semicolon into rows. Bill Jelen mentioned this in his year-end tips for the MyExcelOnline podcast, and if you’d like to check it out, it is here.

We select the Custom column, and then the Transform > Split Column > By Delimiter command. The Split Column by Delimiter dialog opens, and we select the Semicolon delimiter, expand the Advanced options, and pick Rows, as shown below.

We click OK, and bam … the results are shown below.

Now, we need to split the Custom column again, this time at each colon into columns. So, we once again click the Transform > Split Column > By Delimiter command. We select Colon, as shown below.

We click OK, and bam…

With the hard part done, it is time to send the results to Excel.

Return it to Excel

To return the results to Excel, we use the Home > Close & Load command. The workbook is shown below.

And, the best part is that we don’t need to go through all that trouble tomorrow, next week, or next month when we export an updated table. We just right-click the green results table and hit Refresh. Nice 🙂

If you’d like to practice, feel free to download the sample file below.

If you have any related Power Query tips, please share by posting a comment below … thanks!

Sample File

• SplitList.xlsx