(upbeat music)
- Why Power Query?
What's the big deal?
Well, if you deal with data and it comes to you
in a condition where it's not immediately ready,
you should be using Power Query.
If you get two datasets and have to figure out
what's on both of them or what's on one
that's not on the other,
there are features in Power Query for that.
If you have to clear out duplicates, and if you get
10 workbooks that have a whole bunch of worksheets in them
and you need them all stacked up in one place, Power Query.
Over the years that I've been teaching Power Query
folks talk about saving days and hours a week
because they were able to automate something in Power Query
and save time, save pain.
And one lady said she saved her employer $30,000 a year
because of what she was able to do in Power Query.
We're going to look at a small example
of what Power Query can do.
Here I have a workbook that has first name, last name and ID
and the ID is stuck on to the last name.
So Teagan67966, okay?
That's the purple group,
and then here's the blue group.
Same thing going on.
What we want to know is if they passed two exams
that they had to take.
Here's a list of the exam results by ID
for everybody who took these two exams.
And in order to pass, they need an 80% average.
And notice, yeah, there's just IDs, not names.
So what we have to do is bring in the purple group
and the blue group's data, get that stacked up
and then figure out who passed and who didn't.
First, I'm going to go and I'm going to close that other workbook.
Here it is, close it.
Is saved on my desktop.
What I'm going to do right away is import that data.
Here we go, this is going to be sweet.
Data, get data from file from workbook.
There we are on the desktop.
There it is, Power Query example, double click it.
Blue group, purple group, great.
I'm going to highlight the folder
and then I'm going to go to transform data.
Now this is showing me the sheet name and then our data
is in that data column.
With the name column already highlighted,
I'm going to hold down control, select the data column,
right click, remove other columns.
Now expand, I don't want to use
this original column name as prefix, okay?
Look it at, there is our data.
I'm going to rename this group,
and then first name, then double click here, last name.
We don't need this third column, right click it, remove.
I don't want those other column headers
called first name, last name, ID.
I'm going to filter that out.
First name, OK, now that's gone.
How am I going to split the ID from the last name?
I'm going to highlight that column and I'm going to split it
where a non digit changes to a digit.
Whole, look at that.
All right, call this ID.
We are moving now.
Call this last name.
Then I'm going to close and load this to the workbook.
Next, I want to get those exam results.
Data, from table/range, OK.
Now I want to get the average.
I'm going to go to add column, add a custom column.
I'm going to do this.
Open parentheses, exam one plus exam two, close parentheses,
divided by two, OK.
Now I can add a conditional column
to see who passed and who didn't.
Conditional column, pass fail.
If that custom column we made
is greater than or equal to 80, then pass.
Else, we're just going to leave it blank.
We don't want to be mean to the people who failed.
OK.
The next thing I'm going to do is merge those two datasets.
I'm going to set myself up to do a merge.
So what I need to do is I've got to change this ID
to text, and then this other one
that ID needs to be text, which it is, okay.
And I'm going to call this our results query.
All right, so now let's do a merge.
Merge queries, merge queries as new.
I want example on top, the results on the bottom.
Match up ID and ID.
We're leaving this as a left outer join, OK.
I'm going to slide over, expand, I don't need the ID,
I don't need the exam scores,
I just want to know pass fail, OK.
Now, close and load this to the workbook.
There's the result of who passed and who failed.
We just got a call that there has been an update
in the workbook and now there's a yellow group.
Do we have to go through all of those steps in Power Query?
I'm going to save this, close it, go back to where we did
all of our work, and I'm going to go to data, refresh.
Check it out.
Now we have the yellow group
and we know who passed and who failed.
That is just one example of what's possible with Power Query
and how it can save people hours,
days or even $30,000 a year.
Back before Power Query, if we had to split a last name
and an ID, that was a mess.
But Power Query has all those tools in it
and it helped us out a lot.
And that is why is worth getting to know Power Query.
(upbeat music)