Insert Repeating Items into a List

This post will demonstrate how to insert the same few items into a list and create a new row for each item (or each combination, if multiple items).  For example, let’s say we have a list of some sort … we’ll use a list of T-Shirts for this illustration. We have a few T-Shirt options that we want repeated for each T-shirt in our list. For example, each T-shirt has the same size and color options. We’d like to expand the list of T-shirts and create a new row for each combination of color and size. I’ll demonstrate how we can use Power Query to accomplish this task.

Video

Objective

Before we get to the detailed steps, let’s quickly confirm our objective.

We have a list of T-shirts, like this:

We want to expand the list so that each T-shirt gets a new row for each size option (small, medium, large) and a new row for each color option (blue, black, white, red). The resulting list needs to have a new row for each combination of size and color, like this:

In the old days, this took quite a bit of copy/paste, or perhaps a clever macro. Nowadays, it is just a couple of clicks in Power Query.

Let’s get to it.

Narrative

We’ll accomplish our objective with these steps:

• Get list into Power Query
• Create size and color columns
• Split columns into rows

First up, getting the T-Shirt list into Power Query.

Get list into Power Query

To load our list into Power Query, we select any cell in the table and use the Data > From Table/Range command.

This will load our T-Shirt table into Power Query, and we should see something like this in the Power Query Editor:

Done. Time to create the size and color columns.

Create size and color columns

Our goal in this step is to create one column for each option list. Here, we have two options (size and color), so we’ll create two columns. Each column will contain a delimited list of values for example, small, medium, large. The end game is that we will ultimately split the delimited lists into new rows. So at this point, we just need to get the delimited lists into each row.

There are several ways to create the Size and Color columns. In this example, we’ll use Custom Columns. From within Power Query select the Add Column > Custom Column command. In the resulting Custom Column dialog, we enter the new column name as Size and the formula as = “Small,Medium,Large” (no spaces around the commas) as shown below:

We hit OK and now the size values are repeated in each row:

Now, we need to create the Color column. So, again, we use the Add Column > Custom Column command, and enter the corresponding formula:

Hit OK and so far so good:

Now that we have the desired option values in new columns, it is time to split these values into rows in order to create one new row for each combination of size and color.

Split columns into rows

First, we select the column we’d like to split. In this case, we’ll start with the Size column:

We select the Transform > Text Column > Split Column > By Delimiter command. In the resulting dialog, we identify the delimiter (in our case, a comma) and we want to Split at Each occurrence of the delimiter. Then we click Advanced options to reveal the Split into Rows option:

We click OK and confirm that there is one new row for each Size:

We repeat these steps for the Color column and bam:

We are looking good, so we just click Home > Close & Load To and send the results back into an Excel table:

The results are displayed in Excel:

Now, the beautiful thing about Power Query is that next period, when we have additional T-Shirts in our products table, all we need to do is right-click the results table and hit Refresh. And, if we have any additional colors or sizes, we can simply open up the query and modify our custom column formula (by clicking the gear icon) to include them. When we Close & Load, the updated values will flow into the results table!

Conclusion

If you have any other ways to accomplish this or suggestions on how this approach can be improved, please share by posting a comment below … thanks!

Sample File: RepeatOptions.xlsx