Welcome to High Line BI348 class video number 36.
Hey, if you want to download this file,
BI348-Chapter02.5-Import07, either the finished
or the start file, click on the link below the video.
Hey, in this video, we're going to see
how to unpivot a data set using Power Query.
Now let's go look at the final result. Here it is.
And this is common.
We have business instructors and we've
collected the average hours worked per week
in an academic year for business instructors, chemistry,
physics, and accounting instructors.
Now it makes sense we list them with the actual category
at the top.
But for data analysis, this is what we need.
Now unpivot means, well, think of a pivot table.
If we were to drag the category Teacher to the column
area of a pivot table, it would show the unique list
across the top.
And then below, it would have some summary calculation.
So when we say unpivot a data set,
it means I need to take this column
and repeat Business Instructor many times in a column
called Teacher and then list each one
of these numbers next to.
So, for example, Business Instructor
49, Business Instructor 31.
So there's the 49, there's the 31.
And then do it for each column.
Power Query makes this easy.
Now let's go over to our start file.
Now in order to take data inside Excel
and import it into Power Query, you either
have to convert the data set to an Insert, Table, Control T,
which I'm not going to do here.
Or if it's a one time deal, here is an awesome trick.
You can also turn on the filter and then turn it off.
Now watch this.
If I just have a data set and I do not need the dynamic range
aspect of an Excel Table, I simply
convert it to a filtered list.
Watch this-- Control, Shift L. And notice Filter drop down
is at the top.
And then I'm still holding Control, Shift.
I never let go out of it.
And then I'm going to hit the L again.
So here's what I do.
If I want to get it into Power Query quickly,
I do Control, Shift LL.
Now, no problem.
When I go up to Power Query, notice
Get Excel Group expects it to be in a table.
So by using a filtered data set, that's like a hack.
That's another way to get it in.
And by the way I learned that awesome trick from Bill Szysz
at YouTube.
So I'm going to click From Table.
And notice when it imports it says
the actual name of the sheet and then Filter Database.
And that's a fine name.
Now I want to click on the first column.
And before I click on the last column
I'm going to hold Shift because all I want to do
is go to Transform and unpivot columns.
You've got to be kidding me.
Just a few clicks and it's completely a proper data set.
Well, except for-- double click at the top.
I'm going to call this Teacher.
Double click and I named it something ridiculously
long-- Average Weekly Hours Worked.
Here are our steps.
There's a name for our Query.
Now I simply come over to home.
Close and Load to, I'm going to say
Table, on this existing sheet.
And I'm going to say click this Collapse button.
Let's put it in G1.
Click OK.
Click Load.
And there it is-- 47 rows loaded.
Now the beautiful thing about this
is I can do whatever I want.
I can use Sort, I can use Text Filters.
I can come over here.
Filter it just by a certain number or more importantly
filter it by like greater than 55 hours
worked in a week average.
Click OK.
So there's a few of them there.
Now I can unfilter.
But what I want to do here is build a pivot table
and look at the average and standard deviation
for each group of teachers.
So I'm going to click in a single cell.
Insert Pivot Table or of course our keyboard
Alt, N, V. I'm going to click Existing in location
maybe like J2.
Actually I'm going to put it in J1 and click OK.
Now I'm going to drag Teacher down to rows
to get a unique list.
I'm going to drag Average Weekly Hours Worked down to the values
area twice.
Scroll over here, right click Summarize Values by an Average.
Right click Summarize Values by-- I
don't see Standard Deviation but over here More Options.
I'm going to use Standard Deviation.
The P is for the population.
Without a P is for the sample.
Click in the top and how about Mean.
Enter, right click Number Formatting.
Something like that will be fine with two decimals.
Click OK.
Click in this top cell.
I'm going to hit F2 to put it in edit mode
and just call it STDDEV.
Right click Number Formatting, Number, and OK.
Average hours worked-- each one is over 40 standard deviation.
Wow, look at that crazy standard deviation there.
So about 50 hours per week with a standard deviation
of 12 hours.
All right, in this video we saw the ever important unpivot
feature into a proper data set using Power Query.
Now in our next video, we will see
how to import four million rows of data into Excel
using Power Query.
All right, we'll see you next video.