Repeat Table Values x Times Using Power Query

This post will demonstrate how to create a “List of Lists”, also known as “Nested Lists”.

This is where you have one list that repeats for as many entries as you have in another list.

For example: Repeat a set of colors for each item or repeat a set of sizes for each clothing item.

This is not difficult or very time-consuming on short lists, but suppose you have hundreds of items and the number of entries in the lists is constantly changing.

Having to manually update each item in the list can be quite irritating.

Let’s see a fast, dynamic way to achieve the initial lists along with perpetual, free and easy updates.

We begin with a list of names and months.

The objective is to have each name in the first list repeated for as many months as exists in the second list.

THE BAD WAY would be to Copy/Paste all the months repeatedly for as many entries in the names list.

Next, Copy/Paste each name for each unique entry in the month’s list.

What makes this inefficient is:

  • Everything is a manual process.
  • The repetition does not scale upwards in an efficient way.
  • If additional months are added, the previous lists must be updated.

Any way you think about it, it’s a losing proposition.

THE GOOD WAY is to produce the lists with just a few clicks using Excel Tables and Power Query.

Excel basics course Leila Gharani

Excel Essentials for the Real World - Complete Course

Learn Excel from Scratch or Fill in the Gaps.

Become Confident.

Packed with Challenges & Files (Excel 2016, 2019, Office 365).

GET ACCESS

Preparing the Tables

Our first step is to “upgrade” each of the lists into proper Excel Tables.

Click anywhere in the list of names and press CTRL-T.

In the Create Table dialog box, verify that the cell range holding the names is correct and place a check in the “My table has headers” option (if not already checked.)

On the Table Design ribbon, change the Table Name to “Names”.

Repeat the above steps to convert the list of months into a proper Excel Table and name the table “Months”.

Now it’s time to bring these Excel Tables into Power Query and work our magic.

Bring the “Months” Table into Power Query

To bring the tables into Power Query, we’ll start with the easiest table first, the Months table.

Click anywhere in the table of months and select Data (tab) -> Get and Transform (group) -> From Sheet.

NOTE: Older versions of Excel will have the button labeled “From Table/Range”.

This brings the “Months” table into Power Query and interprets the data as text.

We don’t need to perform any additional transformations to the table, so we will return to ‘regular’ Excel.

Click the lower portion of the Close & Load button and select “Close & Load To…”.

In the Import Data dialog box, set the option for Only Create Connection and click OK.

Bring the “Names” Table into Power Query

To bring the “Names” table into Power Query, click anywhere in the table of names and select Data (tab) -> Get and Transform (group) -> From Sheet.

This brings the “Months” table into Power Query and interprets the data as text.

We will use this query as the final output for the report, so let’s change the name of the query from “Names” to “Report”.

Combining and Repeating the Tables

Now it’s time to combine the two tables and perform the monthly name repeats for each name.

Begin by selecting Add Column (tab) -> Custom Column.

In the Custom Column dialog box, set the new column’s name to “Month” and enter the following formula and click OK:

= Months

This will produce a list of nested tables.

To extract the contents of each nested table, click the Expand Tables button located in the header of the newly added “Month” column.

Uncheck the “Use original column name as prefix” and click OK.

We now see a list of each name repeated for as many months as are in the “Months” table.

Before we load the results back to Excel, it’s considered a “best practice” to set the proper data type for each column.

We already have the “Names” column set to Text.  Let’s also set the “Months” to a Text data type.

Excel Dynamic Arrays Course Leila Gharani

Master Excel Functions in Office 365 - Complete Course

Excel has Changed Forever! DON'T MISS OUT!

GET ACCESS

Loading the Results Into Excel

To load the results into Excel, click the Close and Load button.

Set the destination to a Table and select a cell next to the original data as the output location.  In this case, we’ll select cell E1 so we can see the original data and results at the same time.  This will make testing easier to verify.

The results are as follows.

Testing the Flexibility of the Tables

We have two objectives to test to ensure our output table is fully dynamic.

  • Add/Remove/Change names from the “Names” table.
  • Add/Remove months from the “Months” table.

Change one of the names in the “Names” table.

Next, right-click on the output table and select REFRESH.

Now add months to the “Months” table, then right-click the output table and select REFRESH.

Think Outside the Proverbial Box

Users of Power Query know it’s the go-to tool when connecting to external sources and transforming data.

What we sometimes forget is that Power Query can be used on local data to create solutions that would require complex formulas and possibly complicated VBA macros.

With just a few simple clicks and a tiny bit of typed text, we’re able to easily solve problems that most users would walk away from.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Get the ULTIMATE Excel Power Query Course

Use Power Query Like an EXPERT From the Start

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Learn More