| 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:
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 Essentials for the Real World - Complete CourseLearn 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.
Master Excel Functions in Office 365 & Office 2021 - Complete CourseExcel 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.
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. |
