From the course: Excel: Power Query (Get & Transform) (2020)

Power Query example

From the course: Excel: Power Query (Get & Transform) (2020)

Start my 1-month free trial

Power Query example

“

- [Instructor] 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 data sets and have to figure out what's on both of 'em 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 'em and you need 'em 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. 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. There is no exercise file along with this so just watch and follow along. Here I have a workbook that has a first name, last name and ID and the ID is stuck on to the last name, so, Tegan67966. 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, it's 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, OK. Look at that, 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. Whoa, 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. And I want to get the average. I'm going to go to Add Column, Add a Custom Column, going to do this. Open parenthesis, Exam1 plus Exam2, close parenthesis 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 data sets. 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. 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 to call that there's 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 it is worth getting to know Power Query.

Contents