It has been two years since I've been
able to say live from Highline Community College.
Isn't it time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
I'm joined by--
Mike Girvin from ExcelIsFun.
This is our episode 174.
Little bit of data from a huge dataset.
All right.
So here's our 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.
And I have to tell you, that's a chapter 15 thing,
and I've only read through chapter 10.
So I'm not going to try that today.
Instead, I'm going to do Alt-DE, data text to columns, delimited
by a comma.
Finish.
So then I have all of these countries.
Control-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-ESE to transpose.
Bam.
And then, oh, look at that.
There are some spaces.
Oh, I wonder if we have any countries with-- yes, we do.
I was going to just do Control-A to get rid of the spaces,
but United Arab Emirates would get screwed up.
So equal trim.
Oh, haircut.
Woo.
Trim function.
All right.
Control-Z, Alt-ESV.
All right.
So that's my list of countries.
And then what do we want for each of those countries?
We only want to cell that says gross domestic product.
This one right here.
Ctrl-V, and double-click to shoot that down.
Hopefully it's not on a custom list.
Whew.
It's not.
All right.
So we come back here to the original dataset.
We go to 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.
I'm going to 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 we'll return on the video.
It has just the years we want.
And click OK.
Bam.
There we are.
Let do a little bit of cleanup here.
Alt-OCA, 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.
Woo.
Now, Advanced Filter.
That's going to be quicker than Power Query.
Not only that, but I think I can deal 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 my classes here at Highline,
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 Control-T to convert it to a table.
Alt-JTA to jump up and name this.
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 Control-B, Control-T, Enter,
Alt-JTA, and we're going to name this something
like CountriesStart and Enter.
Now I'm going to go up, click in a single cell
or go up to Power Query, over to Phone Table.
There's the name.
All right.
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, then 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.
All right.
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 we 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.
Then highlight all the columns.
Oh, Unpivot, Transform, Unpivot.
Right-click-- oh, not right-click removes.
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 two.
This has already taken 10 times longer
than that amazing Advanced Filter Only.
Create a 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 will say Start.
Down here, we'll say Country, and I'm going
to click on each one of these.
Only include matching rows.
Oh, that is so sweet.
Oh, my gosh.
Click OK.
Look at that.
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.
And if we say Close and Load, it will just
slap it onto a new sheet, and that's what we'll do.
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 could just double-click.
That is incredible.
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 of Power Query
to get it to output.
And I think if we ever updated this, it might erase this.
But Control--
You should do just-- let's edit the query.
Right-click, edit the query.
Now that went in.
And then select all those columns.
Whoop.
And then up in the transform.
Change to a decimal number with 0 decimals.
No, I want to do Custom.
I want to show this in the millions.
Oh.
I don't know how to do that up here.
Unless you actually divide it by millions, which you could do
Add Column.
That would be too tough.
I'm going to close this.
Oh, I have to click-- no, I'm going
to close the Query Editor.
Are you ready?
Control-1.
I think I learned this from one of your first books, pound,
pound, pound, comma, comma.
Click OK.
Now I need to indicate that this is in millions.
Right-click, Insert something like 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.
End parentheses, dollar sign million, end parentheses.
Control-Enter, and then I learned this from you too.
Right-click and drag and then copy here as values only.
That's true.
All right.
Oh, my heavens.
That is awesome.
All right.
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 Advance Filter is the way to go.
But if we had to do this every single day,
Power Query could just repeat over and over
and over if we were getting new data every single day.
Absolutely.
I think there are some other ways.
I think we could have used the Match function and Regular
Filter too.
Absolutely.
Yeah.
That's the beautiful thing about Excel.
But Power Query for refreshable data, nothing can beat it.
Absolutely amazing.
And, again, I'm going to plug M is for Data Monkey.
Great, great book on Power Query, and I
think it is going to be an amazing, amazing seller,
because everyone who uses Excel that has to deal with bad data
is going to love Power Query.
And unfortunately, with Power Query
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 10 times more
than I had just figured out.
Woohoo.
Woo.
All right.
Thanks for stopping by.
We'll see you next week for another Dueling Excel podcast
from MrExcel and ExcelIsFun.