-: All right, hey, everybody.
So I'm gonna introduce a tool to you all
which is mind-bendingly amazing, and it's a tool
that most people have never heard of,
don't even know it exists, and yet it's one of the things
you ever didn't learn, okay?
And it's for data cleaning, it's for data transformation,
and it's built into Excel.
It's also built into Power BI.
Now, caveat on that, it's built into Excel on the PC.
There actually is a version of it now on Excel for the Mac,
but it is not nearly as complete.
You can barely do anything in it,
so unfortunately, this is only for Windows users.
So what it is, is if you go into the data tab in Excel,
you have this button over here called get data.
And as you can see, there are different places
I can get data from, from Azure,
from databases, all kinds of things,
but I'm gonna get data from a file,
and in this case it's a CSV, a comma-delimited text file.
So I'm gonna say from that kind of a file,
and it opens up a little dialogue box where I can choose
where to get that file from, and that file lives
on my computer, and I gotta sort of dig around for it,
and so that file's right here.
So I open it up and like I said,
it opens up essentially this other window.
This is the beginning of the Power Query experience,
and as you can see, it tells me, you know,
essentially this is like a formatting thing.
It's using comma-delimited text,
meaning every comma separates each field,
and data type detection.
We'll talk more about that in a second.
I can either load this data and it'll bring it right
into Excel, or I can click on transform data,
and when I click on transform data,
this is where you get the complete Power Query interface.
This is where all the magic happens, so let me explain this.
What this is is a tool, like I said, to transform your data.
We're in the home tab up here in the upper left hand corner.
Again, I can close and load, and this will take this data
and pull it into Excel.
That's great, I can refresh.
If I happen to change that Excel file,
or that text file rather and save it,
I'm not gonna see any updates here until I hit refresh.
Okay, fine, and there's some other stuff happening
over here, which I'm not gonna talk about.
But let's say as an example of data cleaning,
let's say that I decided that I did want to get rid
of this overview column.
I just don't need all that text, it's just annoying.
I can click the column, and I can click on this button
over here, remove columns,
and it's gonna remove that column, and it's gone.
Now, that introduces an amazingly cool thing
about Power Query.
You may notice on the right hand side here,
there's this applied steps box,
and you can see remove columns shows up here.
That's great, and it has a little X next to it, guess what?
If I click that X, it's gonna undo that change.
Now, that's not that interesting,
because a lot of software has undo, but what's amazing
about this applied steps thing is that every step,
every change, every thing I do gets added to that list,
and if I save this file, close it, and come back
three months from now, I can undo those steps.
I can change how those steps are done.
I can even undo steps in the middle of the process,
and not mess up the rest of the process,
although there's a lot of caveats there.
I might mess up the rest of the process,
but I always have access to try to do that.
So we're always saving these steps, it is amazing.
All right, now, I'm gonna keep this column for now.
Let's go and think about some of the things we noticed,
a couple things, number one, in the released year,
and by the way, if I click this little arrow here,
it's like filtering, right?
I can see what's going on here, and if I zoom now,
remember there was this one movie that had PG as a rating.
Now, that movie was "Apollo 13,"
and if I was gonna do this the right way,
I would probably go and look up "Apollo 13,"
figure out what year it was released, and just fix it
in the source data, or fix it here or whatever.
For today, I'm just gonna filter that out.
I just want to ignore movies that have a weird wrong answer
there, but again, in a real analysis,
you would probably just fix that value.
So I filter the rows, so you can see that over here,
and by the way, there's this little gear over here,
so not only can I hit the X and undo that, remove that step,
if I click the gear, it pops open this little dialogue box
where essentially, I can tweak what that change was,
so I can keep rows where released year does not equal PG.
So I can change what's happening, and even do
like multiple parts, multiple steps to that filtering,
so that's pretty sweet to be realized.
Some of these steps have the little gear, some don't,
so you can just keep your eyes open for that.
Let's say that I noticed something weird about the data,
like I wanted to remove the top five rows
for whatever reason.
Imagine you had a data set that had a bunch of extra stuff
in there and you had to get rid of 'em.
There's a remove rows button, and if I click on that,
there are different choices in here, remove top rows.
Let me click on that, and then I can say, oh yeah,
I wanna remove the top five rows.
If I click okay, it's just gonna remove
the top five, that's good.
I'm not gonna actually do that now.
I have another option in here, I have this run time.
Remember we noticed this one?
183 space MIN, that's not very useful.
This is text, so how can I do numeric calculations
with text?
I can't.
Now, I could fix this a bunch of different ways.
One way is I could split the column,
and this will be familiar for Excel users.
I can say split the column, and I can split by delimiter,
or by the number of characters, or by other things,
but in this case, I'm gonna use a delimiter,
and the delimiter I'm gonna use is a space.
So in other words, take this column,
break it into two columns, splitting on any spaces
that you see in the text,
and you'll see split at the left-most,
or you can also split every time you find that delimiter,
which we'll check out in a moment.
I'm gonna split at the left-most.
I'm gonna say, okay, and yeah, it created a column called
runtime one with just the numbers,
a column called runtime two with MIN,
and now I could just delete this column if I wanted to,
but I'm gonna undo that,
and I'm gonna do this a different way.
Another thing I can do is just replace the values.
See this button over here, replace values?
And what if I find space MIN, and replaced with nothing,
and say okay, and it has the same effect,
although you will notice
this is still actually being treated as text.
How do I know that?
Because it's left aligned numbers.
Let's see if we can find any that it is properly recognizing
as numbers, here you go, numbers are right aligned.
So I can recognize the data type is incorrect,
and by the way, I noticed that the same thing with year,
'cause remember we had that PG in there.
It got confused, okay?
Brings up an interesting point about Power Query,
and there's a little text down here that says it,
column profiling based on top 1,000 rows.
Now, this data set only has 1,000 rows of data,
so it's basing the "profiling" based on all the rows.
But if I had 100,000 rows of data,
it would only be figuring out things like data types
based on the first 1,000 rows,
so sometimes Power Query gets data types wrong.
In this case, it thinks it's text,
and see this little button over here,
and see how it highlights when I roll over it?
ABC, it thinks that this is text.
I can click on this and change it to, in this case,
a year, a whole number, and boom,
it's now a whole number and it fixed it.
You can even see it visually because it's right aligning it.
I'm gonna do the same thing with run time,
whole numbers, okay?
I don't think there are any decimals in there.
Now, we have our genres, and like I mentioned before,
I don't like the fact that it's one column
with comma-delimited values.
I want to split that one by delimiter, in this case commas,
and I want it to happen for each occurrence.
And when I do that, it created three columns,
genre one, two, and three.
Now, I didn't know how many it would create,
but I now have confirmed what I saw visually
that there are, in fact, never more than three genres.
If there were 176 of them, and like if one column
or one value had literally 176 genres separated by columns,
it would've created 176 columns, okay?
So you know, it's kind of neat that you don't have to know
the number to do that.
IMDB rating looks good.
The overview I'm gonna keep for now.
Meta score, I have a bunch of blank values.
Do I wanna filter those out maybe?
I think for now I'm gonna leave those.
Directors and the actors, I'm gonna leave those for now.
Number of votes and gross,
I'm not gonna touch those for now either.
Now, I wanna show you a couple of other things.
Number one, there are other buttons available in here
which are very, very powerful,
and I'm gonna walk through the whole interface.
One of them is this use first row as headers.
Now, we didn't have to do that.
In this particular data set, it's a CSV file,
and the first row in the comma-delimited text file,
in fact, if we look back at it, row number one had
the actual headers, the actual field names.
Now, Power Query was smart enough to actually use those
as the column headers.
It converted them into column headers
rather than as a first row of data.
Sometimes that doesn't happen.
Sometimes Power Query gets confused,
and your column headers will actually read column one,
column two, column three, and your first row
of data will actually have these values down here.
If you ever see that, click on use first row as headers,
and it essentially does what's called promote the headers.
And you can see in fact, in the applied steps,
this happened up here.
It just happened automatically, all right?
I'm gonna talk about merging queries in a second,
and nothing else here we need to talk about at the moment.
There's also the transform tab up here,
where I can do some of those same things I just saw before,
use first row as headers.
I can also do what's called transpose the rows,
literally turn rows into columns and columns into rows,
which sometimes is useful.
Reverse rows, I don't know why
you would need to do that, but you can.
I can also change the data type, so like I clicked
this button up here to change a data type before,
I can change this to something else if I wanted to.
I could also do it by clicking this button right up here.
Same idea, so sometimes you can do things
in multiple different ways, in fact,
a lot of things I can do in Power Query, if I click
on the column, if I right click on it,
or control click maybe, depending on what your computer is,
I also have access to a lot
of different things I can do here.
As you can see, by the way,
I can do things like remove duplicates, remove errors.
Sometimes Power Query will show you it's detected
an error and do things that way,
so always multiple ways to do things.
What else, I can do things like fill values.
So let's, I'm gonna do that for a second,
and then I'm gonna undo it.
Let's say I noticed, you know, I have in these two rows,
sometimes you get Excel data where somebody sets
the first row with a value,
and that's implied that it should be carried down.
In this case, that's not how this one works,
but you know, sometimes you see that.
Essentially, they use like a column as like a header,
and then the next column over has more values in it,
and the header is supposed to apply to all of those.
It's like a formatting thing.
If that was the case here,
I could use this fill down button,
and watch what happens, it fills down.
Any blanks anywhere in the entire data set,
it'll take anything above those blanks.
It'll find it and fill it down just for those blanks,
so definitely not what you wanna do
with this data set today here,
but a really useful thing for certain data sets.
A very, very powerful and important feature built
into Power Query which we are going
to use today is called unpivoting,
so the basic idea here is this.
A lot of data analytics is much easier to do if each row
of data is like a self-contained thing.
One way that it's described is like
it's more transactional data, which makes sense.
It's sort of like a transaction.
So if I was looking at data for a store,
each transaction is someone bought something,
and I want that separated so I can compare
this person's transactions to this person's transactions,
or the transaction for this hour compared
to the transactions for another hour, et cetera.
When we have a row that has multiple things
like our genres, and by the way,
our people, broken into separate columns,
sometimes that makes it very difficult to do analysis.
So what I'm gonna do is I'm gonna do what's called
unpivot those columns.
Essentially, I'm gonna create a row of data
for every one of those, so in other words,
for this movie "Dil Chahta Hai,"
I'm sure I mispronounced that.
There are some Bollywood movies in this data set.
It's gonna essentially generate three rows,
one that says this is a comedy, one that says it's drama,
one that says it's a romance.
This will make it easier later to figure out
which genres are associated with better movies.
This isn't always required,
but it can make it a lot easier to do things like actually,
by the way, creating what's called pivot tables.
That's why this is called unpivot.
We're unpivoting the data and making it more transactional.
So all we have to do to do this is select
those three columns, shift click on the first one,
regular click and then shift click for the last one,
and it'll select all the columns in between.
And then I can say unpivot columns,
and it does exactly what I just said it would do.
It creates three rows for this single movie.
All the other data is repeated.
Obviously, the release year is the same,
the run time's the same, et cetera, et cetera, et cetera,
but then each one has a single, where did it go?
Oh, I think it maybe dropped it at the end.
Yeah, a single now genre.
Now you'll notice it has the attribute, in other words,
the column header gets sort of put over here,
and then it has the actual value,
and it calls it attribute and value.
Now, in this case, I don't need to save this column,
it's genre 1, 2, 3, 1, 2, 3,
'cause these numeric things have no bearing.
So I'm just gonna right click on that or control click,
and I'm gonna remove that.
Now, my value, I don't want that called value.
I actually want that called genre.
Okay, so now I have transactional data for my genres.
Now, if I wanted to do the same thing with my people,
I could do the same thing, so I'm gonna do that.
I'm gonna select the director column,
and then I'm going to shift click to select the other,
the actor columns, and once again, I'm gonna unpivot.
And now each one of these has multiple entries,
one for each person, and I have the associated role
for each of those people.
Now, you will notice that now I have a whole bunch
of duplicates for this movie, and this may be a bad thing,
this may be a good thing, depending on the type of analysis
that you're doing.
There are different ways to do this.
I can't get into that in this class, but long story short,
for simplicity's sake, this sort of made sense to me.
In this case, unlike genre, I want to keep this role,
and I'm gonna call it role,
because I wanna separate my directors maybe from my actors.
But I do want to replace the values.
Instead of star 1, 2, 3, 4,
that makes them feel like different types of things,
what I actually want to do is replace values,
and if I find star one, I'm gonna just call that actor.
Okay, so it did it all down the entire data set.
I'm gonna do that again with star two, actor,
and let me shift or copy that so I can just paste later on.
I'm gonna do it two more times.
I'm also gonna replace star three with actor,
and then I'm also gonna replace star four with actor.
So now I just have the different roles
for each one of these people.
Long story short,
the data is a little bit easier to work with.
You know, I probably could have done it
in a slightly different way so I didn't have literally
a dozen rows now for each one of these movies,
but for now, this is good enough.
So we're gonna do one more thing, and in fact,
we might, we're gonna do two more things.
We're gonna do something which brings up
a data literacy question, which is this.
Look at the gross revenues column, and by the way,
you'll notice it actually is using currency,
so it actually has, you know, two decimal places.
It's actually currency, it's treating it as actual dollars,
not just a number.
The difference between currency and just a number,
I don't know if it even matters.
I'm actually gonna convert this back to a whole number,
'cause I don't even wanna look
at those stupid decimal places.
Now put your data literacy hat on for a second,
and think about this gross revenues column.
Do you have any concerns about that gross revenues column
that you think we should clean up?
Now, if this was a live in-person class, I would call
on people and ask them to tell me what they noticed.
Can't do that here, so let me just tell you the answer.
The answer is this.
Let me scroll back over to released year,
and let me sort in ascending order.
"1920," this movie "1920" had gross revenues
of whatever it had, gross revenues, where'd that go?
There it is, well, and that one is null.
This other movie from 1920 had gross revenues
of $5.45 million, over $5 million in 1921 in that case.
That seems like a lot of money for 1921,
but if I compare that to like "Avatar," that made nothing.
That's the problem with this column.
It's the gross revenues in actual dollars.
It's not adjusted for inflation,
so what I wanna do is I want to adjust for inflation.
How am I gonna do that?
It's a two-step process, and this brings up one
of the insanely amazing wonderful things about Power Query.
I can bring in additional data and merge data sets together,
and do all kinds of fancy stuff, so I'm gonna do that.
I'm gonna click up here, I'm on the home tab over here,
up here on the far right, new source.
I'm gonna pull in another file.
I have another CSV file waiting for me,
which is a CPI, consumer price index file
that I downloaded from a government website,
and as you can see here, it has a bunch of data in it.
I'm just gonna load it in,
and you'll notice over here on the left, I have two queries.
These are like my two data sources.
I have the IMDB data source we were just looking at,
and I have the CPI data source.
Now, as you'll notice, this is a good example
of what I've talked about before.
I have a row that is useless, and I have another row
that actually has my column headers,
and then I have these useless column headers up here,
so I wanna remove the first row, remove top rows,
row number one, and I want to promote the headers,
use first row as headers, so now I actually have the year
and then the each whatever other column as it is.
Now, I have monthly CPI values.
I don't need monthly values.
What I really need is just the annual value,
so I'm gonna delete all these extra monthly columns
and get rid of all those, and just keep this average.
I assume that that's the annual average.
So I'm just gonna remove those columns,
and I'm also gonna get rid of these two things.
I don't even know what they are.
I don't care, useless to me.
These are all the only numbers I need,
the year and the CPI value.
Now of course, I'm gonna explain what I'm gonna do
with those in a second.
So I now have this query ready to go,
and I have this query ready to go.
What I wanna do is I wanna merge the CPI data
into this data, essentially create a new column
with that CPI value assigned and essentially matched up
with each year, and then I'll show again.
I'm gonna show you what I do with that.
So I have my IMDB top 1,000 data source.
I'm gonna click on this button up here, merge queries.
You can see there's one called append queries.
If I append, it just adds more rows at the bottom,
but I wanna merge 'em together.
I want this row data to be matched with this row
from that other data set, so that's what merging is.
So I'm gonna click merge, and it says, okay,
so this is what you wanna merge into, okay, got it,
and then I say, yeah,
and what I want to use is the CPI data.
And then I have to tell it which one of these things match?
What am I merging on?
And I'm merging on the released year for this data set,
and the year column for this data set.
And sometimes you get this little warning, privacy levels.
Just click on ignore, save.
If that just comes up, you can just ignore it.
You can see there are different types of joins,
which if you ever learned data, relational data,
SQL, et cetera, you can use those in different ways.
I'm not gonna worry about those, can't explain 'em now.
This is also what's called fuzzy matching,
sort of a smart join, also not useful for us here.
Long story short, I'm merging on year.
I click okay, and watch what happens.
Now, it brings in something called CPI,
and it brings in the whole table, so it looks kind of weird.
There's this little button up here, I can click on that.
Instead of bringing in both the year and the average,
'cause the year is already established,
what I just wanna bring is that average, of that number.
So I click okay and boom,
I have this CPI value for each one of these years.
So the "1920" CPI number happens to be 20,
and again, I'll explain what this all means in a second.
I'm just gonna rename this CPI
so I don't get confused by the labeling.
Now the question becomes, what am I gonna do with it?
All right, here is where the magic happens, more magic,
'cause that was already pretty damn magical.
Okay, so I'm gonna do what's called add a column.
What I'm gonna be doing is I want to convert
those gross revenue dollars into an adjusted gross,
using the CPI, the consumer price index,
and there's a formula for this.
And so what I'm gonna do is I'm gonna add a column,
and I'm gonna use what's called a custom column.
In a custom column, I can essentially write a formula.
And I'm gonna call this adjusted underscore gross, okay?
And the formula is you want to take the gross revenues
for each row, so this is gonna happen for every single row.
Take the gross revenue, so what I wanna do is
I want to multiply that number
by the most recent year's CPI value.
So if you look at the CPI dataset, you'll notice that
for 2021, which is the most recent year in that data set,
the CPI value is 270.97.
So I'm just gonna hard code that for now.
In a real project, I'd probably figure out a way
to actually tie that dynamically to the last row,
blah, blah, blah, but for this purposes,
I'm gonna multiply by that number.
And then the formula says,
and then divide by this CPI value.
So no matter what the gross revenue is,
it multiplies by that most recent,
which is gonna be the largest number,
and then divide by that year's number, and when I do this,
it creates this new column called adjusted gross.
This first movie had a null gross revenue.
That's why it's null here for the adjusted gross.
But this other movie that made $5 million back in 1921
or whatever it was made 82 million
or whatever that number is, you know, overall,
when you adjust for inflation.
This is how you get adjusted for inflation numbers
just by that simple formula.
So you'll notice other things.
I can also do what's called an index column.
If I do an index column, it'll actually numerically create
a column with a number for every single row.
Now, I didn't do that here, and I wouldn't wanna do it now
'cause I wouldn't wanna have 12 rows with the unique values.
I'd probably want to do that earlier in the process.
I'm gonna show you the final cleaned up version
of this data set in a moment when we start looking
at the data to analyze it.
This was about how to work with Power Query,
an incredibly powerful tool.
I strongly recommend you play around with this.
There's other stuff in this tool that is fantastic
and amazing, and by the way, you'll notice,
like I said before, the applied steps.
Look at this long list of applied steps.
Let's say I wanted to go back here
and change these replaced values.
Remember I replaced the word star one with actor?
What if I change my mind, and I want to replace star one
with smiley face, okay, that word?
I can do that, and it's gonna do it,
but every single other step that I took
after that still exists, so obviously,
I don't want to actually do that.
Lemme revert to what I had there in the first place,
but you get the point.
You can really change things, and by the way,
if I try to to replace this step,
it's gonna give me a warning and say,
"Are you sure you wanna do that?
You might break something and happen later."
I can get away with doing that in this particular type
of a step, but sometimes you gotta be careful about that.
One other note, on the home tab,
we had this advanced editor over here.
Watch what happens if I click this open.
What this is is code, but it's code for all those steps.
In other words, you can do all those steps by writing code,
or let's say down the road, I want to do,
replace all the times I do replace this with the word actor.
If I just wanted to change that to smiley face,
I could just copy, paste, paste, paste in here, hit save,
and rerun it, and it'll do it.
So sometimes it's faster to work with the code
than to work with button clicking.
Long story short, Power Query,
an amazingly fantastic and useful tool.
Use it, learn it, use it.
I promise you you're gonna love it.
I'm gonna click close and load now,
and you'll see it loads the data into Excel.
So I have the CPI data set,
'cause that was one of the existing queries.
I have the IMDB top 1,000 data set.
That's one of the queries, and I also have a new sheet
that I can start to do other stuff in.
So that's really, really powerful.
Now, you may then ask, "Okay, well, now what?"
If I need to do this again, or load a new data set,
or just go back into the changes I just,
oh, I made a mistake, lemme fix it,
I can just under here queries and connections,
double click into it, and boom, Power Query comes back up.
Like I said, all those applied steps saved.
I can go back in here and tweak, and change, and update,
delete, all to my heart's content, and this lasts forever.
So it's not like undo, it's much, much, much more powerful.
So that is data cleaning.
We're gonna talk next about data analytics.