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.
Featured Course
Excel Essentials for the Real World
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.
Featured Course
Master Excel Power Query – Beginner to Pro
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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
