Loan Survey Explosion
May 17, 2018 - by Bill Jelen
Today's question from Quentin who was at my Atlanta Power Excel seminar. Quentin has to generate the same 7 survey questions for each of 1000+ customers in Excel.
As you can see in this figure, the customers are in A. The questions to be repeated are in column D.
You could solve this with VBA or formulas, but this is Power Query week at MrExcel, so I am going to use a cool trick in Power Query.
If you want a blank row between each survey, add a sequence number and add the number 7 after the last question.
Press Ctrl + T from both data sets. Name the second data set with a name you can remember, something like Questions or Survey.
From the second data set, use Data, From Table.
The Power Query editor opens. From the Home tab, choose the Close & Load drop-down and choose Close & Load To.... In the next dialog, choose Only Create a Connection.
You are now back in Excel. Choose any cell in the customer table in column A. Data, From Table. Once the Query Editor opens, click on the Add Column tab in the Ribbon and then choose Custom Column. The formula is
=#"Questions" (including the # and the quotation marks).
A new column appears in the editor with the value Table repeated in each row. Click the Expand icon in the column header.
Choose both fields in the Table. From the Home tab, choose Close & Load.
A new worksheet will appear with the 7 questions repeated for each of the 1000+ customers.
Learn Excel From MrExcel Podcast Episode 2205: Loan Survey Explosion.
Hey, welcome back to MrExcel netcast, I'm Bill Jelen. Now, just yesterday in episode 2204, it was Kaylee from Nashville who had to do a VLOOKUP explosion-- for every item here in column D, we had a matching bunch of items over in column G and needed to explode those. So, if Palace C had 8 items, we would get 8 rows.
Now, today, we have Quentin. Now, Quentin was in my seminar in Atlanta, but he's actually from Florida, and Quentin has almost 1000 customers over here-- well, more than 1000 customers-- in column A, and for every customer, he needs to create this survey-- this survey of 1, 2, 3, 4, 5, 6 questions. And what I'm going to do here is I'm going to add a Sequence number just with the numbers 1 through 7, so that way, I can create a nice blank row in between. I'm going to make both of these data sets into a table; so, we're trying to get these 7 rows exploded for every one of these 1000 customers. That's the goal.
Now, I can do this with VPA; I can do this with formulas; but it's kind of "Power Query Week" here, we're on a run of this is our third Power Query example in a row, so I'm going to use Power Query. I'm going to make this left one into a table. I'm going to be very careful to name this not Table 1. I'm going to give it a name. We're going to have to reuse that name later, so I'm going to call it Questions-- like that. And then this will be Table 2, but I'm going to rename that to be Customers-- not so important that I rename this one because it's the second one that has to have the name. So, we're going to choose this; Data; and we're going to say From Table/Range. Get and Transform Data-- this is known as Power Query. It's built into Excel 2016. If you have 2010 or 2013, on Windows, not a Mac, not iOS, not Android, you can download Power Query for free from Microsoft.
So, we're going to get data From Table/Range; here's our table-- we're not going to do anything to it, just Close and Load; Close & Load To; only Create a Connection; alright, and see, the name of that Query is Questions. It uses the same name as here. And then we come back to this one, and, Data; From Table/Range; so, there's a list of our 1000 or more customers.
Hey now, here's a shout out to Miguel Escobar, my friend, who's the coauthor of M Is For (DATA) MONKEY). I'll put a link to that in the video-- great book about Power Query-- helped me with this. We're going to put in a brand new Custom Column, and the Custom Column formula is this right here: =#"the name of the query". I never would have figured that out without Miguel, so thanks to Miguel for that.
And when I click OK, yeah, it doesn't look like it worked-- we just get table, table, table, but that's exactly what we had yesterday with Kaylee and the ticketing. And all I have to do is expand this, and I'm actually going to say that I probably don't need the Sequence...well, let's put it in just in case. We can take it out after we see it. Right now, we have 1000 rows, and now we have 7000 rows-- beautiful. I can see now that it's appearing in Sequence, so I don't need that. I'll right click and Remove just that one column. And then I can Home; Close & Load; and BAM!-- we should now have more than 7000 rows with 6 questions and a blank space for every customer. Quentin was thrilled with that one in the seminar. Cool, cool trick-- avoids VBA, avoids a whole bunch of formulas using Index, and things like that-- great way to go.
But, hey, today, let me send you off with M Is For (DATA) MONKEY. Ken Puls and Miguel Escobar wrote the greatest book about Power Query. I love that book; in 2 hours you'll become a pro with that book.
Alright, so, wrap-up today-- Quentin needs to generate an identical survey for 1000 different customers. There's 6 or 7 or 8 questions for each customer. Now, we could do this with VBA or macro, but, since we're on a Power Query run here, let's do a Power Query. I added an extra blank question to the Questions; I added a Sequence Number, to make sure that blank stays there; make the customers into a table; make the questions into a table; it's really important that you name Questions something you can remember-- I called mine "Questions." Add the questions to Power Query, As a Connection Only; and then, as you're adding the customers to Power Query, create a new custom column where the formula is: #"the name of the first query" and then Expand that column in the Power Query editor; Close & Load back to the spreadsheet, and you're done. An amazing trick-- I love Power Query-- greatest thing to happen to Excel in 20 years.
I want to thank Quentin for showing at my seminar. He's been to my seminar a couple of times before-- great guy. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Download Excel File
To download the excel file: loan-survey-explosion.xlsx
Power Query continues to amaze me. Check out the book M is for Data Monkey to learn more Power Query.
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"You can do anything with AGGREGATE except understand it."
Title Photo: jens johnsson on Unsplash