Loan Survey Explosion

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.