Welcome to Highline BI348 class video number 37.
If you want to download this workbook BI348 Chapter 2.5,
Import7, click on the link below the video.
In this video we actually want to look at Excel 2016
and see where in the world power query is.
And our specific goal is we have a data set.
Business Instructor, these are the average weekly hours worked
during the academic year.
These are the hours worked for Chemistry, for Physics,
and Accounting instructors.
And we need to take this improper data set
and convert it into a proper data set.
Now what that means is, for business instructors,
I actually need another column next to each one
of these numbers that lists business instructor.
Same for chemistry instructors.
Each number needs to have the criteria
for what this number is, specifically,
Chemistry Instructor.
Now we're going to use the feature Unpivot Columns.
Now you can think of Unpivot Columns as this.
If we had a proper data set, like with two columns, Teachers
and Average Hours, we would simply
drag teachers to the columns in a pivot table.
And it would give us this unique list of instructors.
But we want to do the opposite.
We want to tell this data set to unpivot.
Give me a column just with the Instructors
and just with the numbers.
No problem.
Power Query does this with just a few clicks.
Now where in the world is Power Query?
Well, actually it's on the Data Ribbon, Get and Transform
group.
Now Get and Transform makes perfect sense,
because what does Power Query do?
It goes out and gets data from all sorts of sources.
In fact, earlier in this series of videos,
we saw how to use New Query.
And there's all sorts of sources where we can get data from.
Or we can use the From Table to get data
from inside the same workbook that's going
to have the Power Query output.
So that's the Get part and then the Transform part, of course,
is converting the data into proper data set
or converting it to whatever form you want.
First we have to convert this table to a proper Excel table,
so insert and click on Table or simply
use the keyboard Control-T and Enter.
That's an official Excel table.
I want to be sure to come up to Table Tools and name this.
I actually gave this a long name, Teacher's Hours Worked
Improper Data Set.
So the actual table name is going to say Improper Data Set.
Now with a single cell selected, I can go up to Data.
And the new Power Query in Excel 2016, Get and Transform.
Now I click From Table.
Instantly I get my data set in Power Query.
I come over here in Teacher Hours Worked Improper Data Set.
That's the table name from back over in Excel.
I'm actually going to rename the query Teacher Hours Worked
Proper Data Set and hit Enter.
Now I can click on the first column,
hold Shift, click on the last column.
I have all of the columns.
Now I go up to Transform and this amazing Unpivot Columns,
just like that proper data set.
Now I have to come up and double click to rename,
Teachers column and Enter.
Double click and call this Enter Average Weekly Hours Worked.
Now we can go to Home and Close and Load To.
And I'm actually going to just Close and Load
because I'm going to send this to a new sheet.
There's the 47-row query listed in our query pane.
There's the table.
Double click and rename the sheet, Teachers Hour Data,
and Enter.
Now I have a proper data set.
I can do things like come over and Filter.
Click OK.
I could sort if I want to.
I can clear the filter.
I can come over here and say, show me
for a number filter greater than--
and I want to see greater than 55 hours worked
in a week on average-- and look at that.
Unfilter.
Clear.
Now what I really want to do is make a pivot table
and calculate the mean and the standard deviation.
So it's a single cell.
Insert Pivot Table or use our keyboard,
Alt, N, V. And Existing Sheet Location, maybe something
like E1.
Click OK.
Close this Query Editor, drag the Pivot Table field list
over.
Teachers down to rows to get my unique list.
Average Weekly Hours I'm going to drag it
twice down to the Values area.
Close the Pivot Table field list.
Inside I want to go up to Design, Report Layout,
Show in Tabular.
Now for each one of these I want to right click and go down
to Value Field Settings.
This is the one-stop shopping for the function,
the number formatting, and changing the name.
I'll call this Mean.
And enter.
Now I want to come over, right click Value Field Settings.
We'll change this to Standard Deviation.
The p means Population.
Without the p means Sample.
Number formatting.
Number.
Click OK.
We'll change the name up here.
I'll leave it as [INAUDIBLE] and Enter.
And there we have-- looks like they're all
working over 40 hours, standard deviation of 4
for the accounting.
And look at that crazy standard deviation, almost 50-hour
average with a standard deviation of 12 hours.
In this video, we saw how to create this proper dataset when
we had an improper data set, a table that
looked like it had pivoted criteria at the top,
and we wanted to unpivot.
And of course we saw data, the Get and Transform group.
That is the new Power Query in Excel 2016.
Next video, we'll actually see how
to import four million rows of data into Excel
and do analysis on it.
We'll see you next video.