In this live Dueling Excel podcast, you have to filter out a bunch of columns and filter rows by using MATCH or VLOOKUP. Bill solves this with an Advanced Filter. Mike solves it using amazing steps in Power Query.
Transcript of the video:
Bill: Well, it has been two years since I'm able to say: "live from Highline Community College!" It's enough time for another Dueling Excel podcast, I'm BilL Jelen from MrExcel, I'm joined by...
Mike: Mike Girvin from Excel Is Fun!
Bill: This is our episode 174 - A Little Bit of Data from a Huge Data Set!
Alright, so here's my problem today, this is a massive dataset, all these countries for each country, there's a whole bunch of indicators, years going across, from 1970 to 2013. And our goal is to get the GDP, so just this gross domestic product, GDP, that cell, for only these countries, for only the years, from 2000 to 2010. And initially, I said well, Power Query is going to be the way, but we're going to have to merge two tables in Power Query, which... I just got the great new book from Ken Puls and Miguel Escobar "M is for (DATA) MONKEY", I have to tell you that's a chapter 15 thing, and I'm going to read through chapter 10, so I'm not going to try that today. Instead I'm going to do Alt D E, Data, Text to Columns, delimited by a comma, Finish. Right, so then, I have all of these countries, CTRL+C. I'm going to come out here on the right hand side. Before we started, I already put the countries there.
I'm going to do Alt E S E, to transpose, BAM.
And then, OH, look at that, there is some spaces! Oh, I wonder if I have any countries with... Yes, we do! I was going to just do CTRL+H to get rid of spaces, but United Arab Emirates what gets screwed up. So =TRIM ! Mike: OH hair time, WOO! Bill: TRIM function, alright.
CTRL+C, Alt E S V! Alright, so that's my list of countries.
And what do we want for each of those countries, we only want the cell that says Gross Domestic Price, this one right here. CTRL+B and double-click to shoot that down, hopefully it's not in a custom list- whew, it's not. Alright, so we come back here to the original data set, we got a Data, Advanced filter, it automatically expands to all the data. I'm going to Copy the data to another location, I have a criteria range, that criteria range is these two columns right here, AV and AW. Choose those, that's going to limit me to just the countries in the list, and just GDP, and then copy to, now check this out, created this Output Range.
Before we turn on the video, it has just the years what we want, and click OK, and BAM, here we are. Let's do a little bit of cleanup here. Alt O C A, and we have just the countries, just GDP, didn't have to touch Power Query at all, although I would have loved to have done it in Power Query. Mike, let's see what you have!
Mike: WOO! Now Advanced filter, that's going to be quicker than Power Query. Not only that, but I think I could do with MATCH functions, and a couple other things more quickly than Power Query, but. I'm going to go ahead and try Power Query here, especially since in my classes here at Highland, we've been using Power Query a lot. Now I'm going to get this table, bring it into Power Query, I'm going to CTRL+T to convert it to a table, Alt J T A, to jump up to Name, and I'm going to call this StartTable, and Enter. And then I'm going to create this as a table here, and call this Countries, Ctrl+B, CTRL+T, Enter, Alt J T A, and we're going to name this something like CountriesStart, and Enter. Now I'm going to go up, click in a single cell, go up to Power Querym, over to From Table, there's the name. Alright, I'm going to start off, I'm just going to come over here and Filter!
It looks like it's not seeing everything, so I'm going to click Load More, unselect everything, how about just GDP. But I'm going to highlight, let's say columns 1970 all the way over to 1999. Right click, remove, 2011, Shift, 2013, right click, remove. Alright, I don't need this column anymore, right click, remove. Now I need to bring in that other table, I'm going to Close and Load To... a connection only, click, Load.
Now I'm going to come back over here, click in that cell, From Table. Boy, this is already taking a long time. Ah, but when you want to split by delimiter. Oh, and it's so cool, instead of comma, I think we can come down to custom, looks like we're going to have a comma then a space. So we're going to try to have two delimiters together, At each occurrence, click OK, and highlight all the columns- Oh, Unpivot transform, Unpivot. Right click, WHOA, not right click remove, I want to just remove this, right click, remove, double click, I'll call this Country, and Enter. Now I'm going to Close and Load To... this has already taken 10 times longer than that amazing Advanced filter. Only Create Connection, and Load.
Now I think we can merge these, I'm going to click the Merge button up at the top. The first one we'll say Start. Down here we'll say Country, and I'm going to click on each one of these, Only include matching rows!
Bill: That is so sweet, oh my God. Mike: Click OK! Bill: Look at that.
(exceisfun) Now we have a table off to the side, but we're not going to need it, we have everything. And if you scroll down, there's 29 of them. Scroll over here, right click, remove. I'm going to give this a better name, I tell you, I can't tell you how many times I've gotten in trouble when I'm not naming the tables, we'll call this GDP-Output! Enter, and then now we can close, when we say Close and Load, it'll just slap it onto a new sheet, and that's what we'll do. Bill: That is awesome, that is awesome, I LOVE, love Power Query, every time I see someone else use Power Query I learn new tricks! I've been right-clicking and choosing rename, I never knew you can just double click! That is incredible.
Mike: Oh my heavens, should we fix this? This is terrible, what's all this junk here? I actually do not know how to add the custom number formatting inside a Power Query to get it to output, and I think if we ever updated this, it might erase this, but CTRL+1... (MrExce) Let's edit the group, right click, Edit the group, and then select all of those columns, and then up in the Transform, change to a decimal number with 0 decimal places. Mike: No, I want to do custom, I want to show this in the millions. I don't know how to do that up here(?). Bill: Unless you actually divided by millions, which you could do on added column(?), and that would be to tough. Mike: I'm going to close this. Oh, I have to click uhh...
No, I'm going to close the query editor. Are you ready, CTRL+1, I think I learned this from one of your first books, #,###,, click OK. Now I need to indicate that this is in millions, right click, Insert, something like uh... Equals, down arrow, and the thing is, that's text from Power Query, right? So I can't do custom number format, so I have to do something crazy like this, I think. Right, (&M) , CTRL+Enter, and then I learned this from YouTube, right click and drag, and then copy here as Values Only. Oh my heavens. Bill: That is awesome, that is awesome.
Alright, so that Power Query, although this is one of the rare times that I think Advanced filter was faster, because it was a one-time gig, we only had to do it once, I think Advanced filter's a way to go. But we had to do this every single day. Power Query could just repeat over and over and over, we were getting new data every single day. Mike: Absolutely, I think there's some other ways, I think we could have used the MATCH function and regular filter too. Bill: Absolutely, yeah, that's the beautiful thing about Excel. Mike: But Power Query for refreshable data, nothing can beat it! Bill: Absolutely, it's amazing.
And again I'm going to plug "M is for (DATA) MONKEY", great, great book on Power Query, I think it is going to be an amazing, amazing seller, because everyone who uses Excel, it has to deal with bad data, it's going through a lot of Power Query, and unfortunately with hardware you can't just figure it out, it's not intuitive. I was using Power Query for a year, and I read that book, and by chapter 7 I already knew ten times more, than if I just figured it out. (both) WOOHOO!
Bill: Alright, thanks for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel, Mike: and excelisfun!
Mike: Mike Girvin from Excel Is Fun!
Bill: This is our episode 174 - A Little Bit of Data from a Huge Data Set!
Alright, so here's my problem today, this is a massive dataset, all these countries for each country, there's a whole bunch of indicators, years going across, from 1970 to 2013. And our goal is to get the GDP, so just this gross domestic product, GDP, that cell, for only these countries, for only the years, from 2000 to 2010. And initially, I said well, Power Query is going to be the way, but we're going to have to merge two tables in Power Query, which... I just got the great new book from Ken Puls and Miguel Escobar "M is for (DATA) MONKEY", I have to tell you that's a chapter 15 thing, and I'm going to read through chapter 10, so I'm not going to try that today. Instead I'm going to do Alt D E, Data, Text to Columns, delimited by a comma, Finish. Right, so then, I have all of these countries, CTRL+C. I'm going to come out here on the right hand side. Before we started, I already put the countries there.
I'm going to do Alt E S E, to transpose, BAM.
And then, OH, look at that, there is some spaces! Oh, I wonder if I have any countries with... Yes, we do! I was going to just do CTRL+H to get rid of spaces, but United Arab Emirates what gets screwed up. So =TRIM ! Mike: OH hair time, WOO! Bill: TRIM function, alright.
CTRL+C, Alt E S V! Alright, so that's my list of countries.
And what do we want for each of those countries, we only want the cell that says Gross Domestic Price, this one right here. CTRL+B and double-click to shoot that down, hopefully it's not in a custom list- whew, it's not. Alright, so we come back here to the original data set, we got a Data, Advanced filter, it automatically expands to all the data. I'm going to Copy the data to another location, I have a criteria range, that criteria range is these two columns right here, AV and AW. Choose those, that's going to limit me to just the countries in the list, and just GDP, and then copy to, now check this out, created this Output Range.
Before we turn on the video, it has just the years what we want, and click OK, and BAM, here we are. Let's do a little bit of cleanup here. Alt O C A, and we have just the countries, just GDP, didn't have to touch Power Query at all, although I would have loved to have done it in Power Query. Mike, let's see what you have!
Mike: WOO! Now Advanced filter, that's going to be quicker than Power Query. Not only that, but I think I could do with MATCH functions, and a couple other things more quickly than Power Query, but. I'm going to go ahead and try Power Query here, especially since in my classes here at Highland, we've been using Power Query a lot. Now I'm going to get this table, bring it into Power Query, I'm going to CTRL+T to convert it to a table, Alt J T A, to jump up to Name, and I'm going to call this StartTable, and Enter. And then I'm going to create this as a table here, and call this Countries, Ctrl+B, CTRL+T, Enter, Alt J T A, and we're going to name this something like CountriesStart, and Enter. Now I'm going to go up, click in a single cell, go up to Power Querym, over to From Table, there's the name. Alright, I'm going to start off, I'm just going to come over here and Filter!
It looks like it's not seeing everything, so I'm going to click Load More, unselect everything, how about just GDP. But I'm going to highlight, let's say columns 1970 all the way over to 1999. Right click, remove, 2011, Shift, 2013, right click, remove. Alright, I don't need this column anymore, right click, remove. Now I need to bring in that other table, I'm going to Close and Load To... a connection only, click, Load.
Now I'm going to come back over here, click in that cell, From Table. Boy, this is already taking a long time. Ah, but when you want to split by delimiter. Oh, and it's so cool, instead of comma, I think we can come down to custom, looks like we're going to have a comma then a space. So we're going to try to have two delimiters together, At each occurrence, click OK, and highlight all the columns- Oh, Unpivot transform, Unpivot. Right click, WHOA, not right click remove, I want to just remove this, right click, remove, double click, I'll call this Country, and Enter. Now I'm going to Close and Load To... this has already taken 10 times longer than that amazing Advanced filter. Only Create Connection, and Load.
Now I think we can merge these, I'm going to click the Merge button up at the top. The first one we'll say Start. Down here we'll say Country, and I'm going to click on each one of these, Only include matching rows!
Bill: That is so sweet, oh my God. Mike: Click OK! Bill: Look at that.
(exceisfun) Now we have a table off to the side, but we're not going to need it, we have everything. And if you scroll down, there's 29 of them. Scroll over here, right click, remove. I'm going to give this a better name, I tell you, I can't tell you how many times I've gotten in trouble when I'm not naming the tables, we'll call this GDP-Output! Enter, and then now we can close, when we say Close and Load, it'll just slap it onto a new sheet, and that's what we'll do. Bill: That is awesome, that is awesome, I LOVE, love Power Query, every time I see someone else use Power Query I learn new tricks! I've been right-clicking and choosing rename, I never knew you can just double click! That is incredible.
Mike: Oh my heavens, should we fix this? This is terrible, what's all this junk here? I actually do not know how to add the custom number formatting inside a Power Query to get it to output, and I think if we ever updated this, it might erase this, but CTRL+1... (MrExce) Let's edit the group, right click, Edit the group, and then select all of those columns, and then up in the Transform, change to a decimal number with 0 decimal places. Mike: No, I want to do custom, I want to show this in the millions. I don't know how to do that up here(?). Bill: Unless you actually divided by millions, which you could do on added column(?), and that would be to tough. Mike: I'm going to close this. Oh, I have to click uhh...
No, I'm going to close the query editor. Are you ready, CTRL+1, I think I learned this from one of your first books, #,###,, click OK. Now I need to indicate that this is in millions, right click, Insert, something like uh... Equals, down arrow, and the thing is, that's text from Power Query, right? So I can't do custom number format, so I have to do something crazy like this, I think. Right, (&M) , CTRL+Enter, and then I learned this from YouTube, right click and drag, and then copy here as Values Only. Oh my heavens. Bill: That is awesome, that is awesome.
Alright, so that Power Query, although this is one of the rare times that I think Advanced filter was faster, because it was a one-time gig, we only had to do it once, I think Advanced filter's a way to go. But we had to do this every single day. Power Query could just repeat over and over and over, we were getting new data every single day. Mike: Absolutely, I think there's some other ways, I think we could have used the MATCH function and regular filter too. Bill: Absolutely, yeah, that's the beautiful thing about Excel. Mike: But Power Query for refreshable data, nothing can beat it! Bill: Absolutely, it's amazing.
And again I'm going to plug "M is for (DATA) MONKEY", great, great book on Power Query, I think it is going to be an amazing, amazing seller, because everyone who uses Excel, it has to deal with bad data, it's going through a lot of Power Query, and unfortunately with hardware you can't just figure it out, it's not intuitive. I was using Power Query for a year, and I read that book, and by chapter 7 I already knew ten times more, than if I just figured it out. (both) WOOHOO!
Bill: Alright, thanks for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel, Mike: and excelisfun!
