Welcome to Highline BI348 Class Video Number 33.
If you want to download this workbook, BI348Chapter02.5--
and this is Import04-- either the finished or the start file.
And also here's a picture of the downloadable links.
Be sure and download the zipped folder
with all of our Excel files.
Because we're going to take all those
and import them using Power Query.
Now you are not going to believe what we're going to do.
This is our end result. We need to take survey data, Yes No,
and display it by Department, Finance, Accounting, Admin,
and show the percentages.
But you're not going to believe where this raw data comes from.
Now when you download this, this is the import
for Excel not proper data set.
And inside is a folder called New Files and Survey
by Department.
I'm going to just open this.
And we're going to take a look.
Double-click it, open it up.
And that's how the data comes.
On the sheet it does say the department name,
which will be helpful for us.
Because like our last video, we'll extract that sheet name
and put it into a column, so we know all these Yeses
and Nos are from the Accounting department.
But look at this data.
There is no proper data.
It says the question here, Employee
slash Answer, Yes and No.
How in the world-- plus we need all these employee names--
how in the world are we going to get from this data
into a proper data set?
And that data is set up the same in each one of the workbooks.
Power query is going to have no problem.
It will take the data from the sheet and the actual department
name and import it into a proper data set.
Let's go over to our start file.
Here it is.
And we're going to go up to Power Query.
And man, this is the fifth or sixth time we're
doing From File, From Folder.
We're going to choose to Browse to the folder.
There it is.
Click the arrow.
And we want to start with Survey By Department.
Now we're telling Power Query to look in that folder
and get everything in that folder.
I'm going to click OK.
Click OK.
We get data about all the files from that folder.
I'm going to come over and give it a smart name.
Now I called it Import Survey Data from Excel workbooks.
And I said Bad Data.
Because that data is not set up the way we want.
Now we don't need to do anything with the Extension column.
In earlier videos we did, but we're always going to have
our Excel files that are .xlsx.
We don't even need the name of the file
like we did in the last video.
Even though the file has the actual department name,
the sheet tabs will give us that.
So I'm going to come over to Content.
Right-click.
Remove Other Columns.
This is not a text file, so we can't simply
click this and expand it.
We have to add a column, Add Custom Column.
We're going to call this new column Get Excel Data--
and I think this is the third time we're
going to use this amazing Power Query function--
Excel.workbook.
Open parentheses.
I'm going to double-click on the Content.
Notice that's that field right there.
Close parentheses.
Click OK.
We do not need Content anymore.
Right click Remove.
We want to expand this to get to data, item like Sheet Names,
kind like Tables, Define Names and Sheets.
Click OK.
Now in an earlier video we had tables, sheets,
and defined names here.
We only have Sheets, so we don't need to mess with that.
We definitely want Item.
And we don't have any sheet names in there
that are not named properly, so this will work fine.
We simply need to select the Item column and the data.
I use Control to highlight them both.
Right click Remove Other Columns.
Now I can click the little double arrow pointing off
to the side to expand.
Click OK.
Wow.
That is some messed up data.
But Power Query will have no problem with this.
Now the first thing is we want to look at this column right
here.
Now this column has the sheet name, so all
of the answers from Accounting, all the answers from Admin.
But this column we need to look at.
Now since the template is the same on each Excel sheet,
we're always going to have question colon and then
the question at the top, then an empty cell, then
employee slash answer-- that's like a double field name.
All of the employee names we want to keep.
And at the bottom of each sheet there was a count with
the total for Yeses and No's.
So we can do a simple filter to clean this data up.
I'm going to uncheck No.
I want to get rid of that.
Scrolling down.
Count.
I want to get rid of that.
Scrolling down.
Oh, there's Employee slash Answer.
I want to get rid of that.
Then I'm scrolling down.
And then Question-- now the question
is exactly the same on each sheet.
If it wasn't, we could use the filter like Begins
With Question colon.
But all four of those can simply be filtered.
And when I click OK, instantly this entire column
has only the sales rep.
Now we want to highlight both of these columns.
And I held Control.
Go over to Transform.
And I want to say, Unpivot Columns.
Instantly it puts the Yes's and No's in a column.
It puts this column-- we don't want it--
so I'm going to select it and right click Remove.
Now we can double click.
I'm going to call this Department.
Double click this and call this Employee.
And Enter.
And then double-click and call this Answer.
And Enter.
Now we have our data set.
That is just amazing, that it's going
to go through every single one of those files
and take that data that is not set up correctly
and put it into a proper data set.
Here's all of our steps.
There's our name.
I'm going to select Home, Close and Load To.
I want it in a table on the existing sheet.
And click Load.
And there it is, 59 records, Department, Employee,
and our Answer.
Now we're going to actually build our pivot table report.
And then some of the slacker departments,
not Accounting, not Admin, not Finance, Human Resources,
but there's a few of departments that have not submitted
their workbooks on time.
So we're going to build our report and look at it.
And then later we'll dump some new files
into that folder and everything will update.
I'm going to click in a single cell, Alt, N, V. Existing
Sheet location may be E1.
And click OK.
Now we're first going to build just a simple-- give me
the answer in the rows, Yes and No.
And then I'm going to drag Answers down to Values
once, and then Answers down to Values twice.
Click in the cell for our Frequency and type
Count and [INAUDIBLE] percent of total.
Enter.
Right click.
Show Values As.
And I want Percent of Column Total.
There it is right there.
And instantly I get the overall rate of no's and yes's
for the entire company.
I better come up here to Design, Report Layout, Show in Tabular.
Now here's the original question that
was a survey done for the entire business.
And the data was collected by department in that
not proper data set way.
Do you want to perform community service work for two days
a year, if we pay time and a half?
So it looks like that 50% of the people said no and 49%
said yes.
But we want to do this by department.
Now I'm actually going to cheat and copy this, paste this over
to the side.
And we'll take Department and drag it down
to Rows, right above Answer.
And then come over and expand our columns,
so we can read the labels there.
And that's not exactly the calculation I want.
Now this is polite.
We told this column to do percent of column total,
but really, this is called a parent.
And so I want to right click Show Values As,
and do Percent of Parent Total.
It's going to ask me which field I want to base this on,
and I definitely want it On Department.
So I'm going to click OK.
And there we go.
So we can see Accounting.
Well, Accounting said 60% no, 40% yes.
Admin 50% no, 42% yes.
Finance said 75% no.
Human Resources was a tie.
And look at this, Operations.
67% of the people said yes.
We would like to do two days of community service,
if you paid us time and a half.
Now when we scroll over here, I'm going to close this.
There's our report, right?
Well, let's go-- and it's a week later or whatever
and we got the rest of the Excel files-- so I'm going to go back
and when you download this, they're in the new.
So the Delivery department, Maintenance, and Marketing.
I'm going to copy these.
And I'm going to paste them inside of our survey
by department.
Now remember, when we did Power Query,
we told Power Query to look in this folder.
So now, when we come back over to Excel-- so
we're back over here.
We can come to Data.
Refresh All.
Or we can simply use the keyboard, Control, Alt, F5.
And it's trying to load.
And look at that.
That was quick and easy.
Control down arrow.
I definitely have Operations, Marketing, and Maintenance.
Control Home.
It doesn't look like the pivot table worked.
I'm going to come over and force the issue.
Right click Refresh.
And just like that, look at that.
And over here.
That is simply amazing.
And really, the more amazing part
is we got a complete single proper data set
from all those Excel files with all that messed up data.
And on top of it, our finished report totally updated.
Now it did take two refreshes, but I don't really care.
That is amazing.
Now in our next video, we will actually
take data from multiple sources, so we'll
have Access, text file, Excel, and combine them
all into a single table using Power Query.
All right.
We'll see you next video.