digest
Welcome to Power Query video number three. If you want to download this workbook and follow along, click on the link below the video. This is Power Query video number three, but I didn't do a one and two. I actually renamed two of the Power Pivot videos. When you click on the link below the video, you get to this page, and you have to scroll down. This has thousands of files, so I just want to scroll down and look. Here's the Power Pivot series with all the workbooks, and it was video 11 and 12, which actually turn out to be number one and two for Power Query. Here are the files. When you download this, there's an empty workbook with a few things, and then here's what we're going to do: we're going to see how to take three text files and import them not in one, two, three steps, but all in one step.
The more I learn about Power Query, the more I am amazed. Maybe it's not Power Pivot or pivot tables that's the most amazing feature in Excel, but Power Query. Now, that's not really true, because they all do their own thing, but Power Query is profound in how it can import, transform, clean data, and bring it into Excel. Now we want to go back over to our workbook, because I've got to give some shout-outs to Bill Jelen, Miguel Escobar, and Ken Puls. These guys are helping me to learn Power Pivot. Here's the link where you can go check out their stuff. Ken Puls is an Excel MVP with tons of amazing things on his blog. Miguel Escobar has amazing Power User videos on YouTube and has been doing them for a long time, much longer than me. Bill doesn't usually post videos, but he puts amazing comments below my videos, and he is one of the smartest Excel guys I know.
All right, let's check out how to do this. Wait a second, I want to show you the end result first, because I want to build three pivot tables and build this report. Here is the grade data. We have three classes, and we're analyzing way back in time all the way to today. We want to figure out the mean, standard deviation, and count. We're going to build three pivot tables, but I want to summarize this visually in one chart. We have a slicer over here, and we can click on any one year and instantly see how the data has changed.
All right, let's go over and import our data. The first part of this is going to be Power Query, and the second part will just be straight pivot tables and charting. Now, for Power Query, you have to go and download it. Just Google "download Power Query." It works in Excel 2010 and 2013. Here's the amazing thing: From File, From Folder. This is amazing. It'll allow us to import text files easily, all in one step. Let's actually go look at our files to understand how this feature will interpret them in one step. This is just straight tab-delimited records in rows, and each one of the files is like this. So the import using From Folder will just automatically know to stack all three tables together. In later videos we'll see that if we are doing a different type of data import, either from text or from Excel files, it won't be as easy as a single step.
Here we are: Power Query, From File, From Folder. Here's our Browse button. We go to wherever our folder is. Inside that folder there are no more folders, only those three text files. I'm going to click OK, click OK. Wow, check that out. It actually brought in information and attributes about the actual files, but it's this binary one that has all the data. Now we're going to delete all these. Watch this: I'm going to right-click and say Remove Other Columns. Before we do anything else, let's rename this to "Grade Data." Then check that little arrow. Let's click, and instantly there is our data set. We imported these from text files, and it interpreted the first row as header or field names. That won't always happen, but another thing is that when we import these, and these are huge data sets, these field names can show up in the middle of the data. So I want to click on the filter. This is an amazing feature of filters everywhere: filter, pivot tables, Power Pivot, Power Query. It gives you a unique list of items that you can check. The list may be incomplete, so I'm going to try to load more, and there it is: there's that field name way down there somewhere. You can prove this to yourself by running a little tiny data set and looking through, and you'll see the field names often do show up. I'm going to uncheck this. This becomes part of the query, so I'm going to click OK. You can see there are a bunch of steps over here in our query.
Now our data is good to go. We could come over and look at each one of these. The list may be incomplete, and we can see there are all of our numbers. We could also come over to Year and select, and the list may be incomplete. I'm going to load more, and sure enough, there are all the years. Click OK. Now we can simply close and load to. We are definitely going to load it to a table in Excel. If you wanted to load it to your data model, there it is. I'm going to put it on the existing sheet, click the collapse button, select A1, click OK, click Load. You've got to be kidding me, that is absolutely amazing. Control-down-arrow, and there's our 75,000 rows of grade data for these three classes. Control-Home.
Now we're going to build a pivot table on another sheet. Insert Pivot Table, or we'll use the keyboard: Alt, N, V. Since we're putting it on a new sheet, I'm simply going to hit Enter. Here's our field list: Class down to Rows, Grade down to Values. Here's our first pivot table. I'm going to close this field list since we don't need it anymore. Right-click, and I'm going to the One-Stop Shopping for pivot tables: Value Field Settings. I want to change the name, and actually, I want to change the calculation first. We're going to change this to average, call it GPA, and add some number formatting. I want to show just one decimal, so click the decimal button. Click OK. One, two, three: our One-Stop Shopping lets us change three things. Click OK.
Now let's go to Analyze, Insert Slicer, Year, and click OK. We could format this if we want four columns, right to the edge, however you want to do it. I'm not going to change the row labels, because we're not really going to look at this or print it out. We're just going to make a chart. But watch this: I'm going to press Control-Asterisk to highlight the whole table, Control-C, Control-V. Now I want to right-click for One-Stop Shopping, and we want to change it to Standard Deviation of the Sample. For number formatting we're going to show a bunch of decimals. I'm going to include four decimals. Click OK. Now I'm going to come up here and name this "SD." Control-Asterisk to highlight the whole table, Control-C, Control-V. Click inside the values, right-click, Value Field Settings, and now we want Count. We'll change the name, and then do number formatting. I'm just going to include zero decimals. Click OK, click OK.
So now we have our three pivot tables, and we can already see that it's working. You know what, I'm going to change this right here: since I don't need One-Stop Shopping, I'm going to go to number formatting and increase it to two decimals. Click OK. Now let's create our chart. The tricky thing is going to be the label that we want on top, but we'll see a cool Excel trick. I'm going to use the keyboard for the default chart: Alt, F1. My default chart is a column chart. That's looking pretty good already.
I had an idea. I want to come back over to the pivot table, right-click, Show Field List, and drag Year to the filter area. This is basically similar to the slicer. The slicer is just a fancy visual way to do this. I'm going to close this, but the reason I want that there is that I want to make a label up here. I'm going to do a few different labels for this chart, where the labels are based on formulas from the cell. So I'm going to say "Label:" and our formula will be =“Student Data in ” and join it. If I just click on this cell, it'll give me the GETPIVOTDATA function, and I'm just going to type B1. All right, you ready? When I change the year over here, my label changes. Now I'm going to click on the title, either click in the formula bar or type = and click on F1. So there we have "Student Data in 1980."
Now the tricky thing is that I'd like to pull the data, one, two, three, from three different pivot tables, and I want to link these data labels to the actual cells. That's an awesome new feature in 2013. So I'm going to type "Data Label" and I like to label everything so I know exactly what's there. I'm actually going to color these green. These are not really part of anything we're going to print out, but I like to indicate that this is a formula with some color, just so I don't make a mistake later. Right now I know that changes the chart, and we'll mess with the chart later, but let's make our data labels. I want one, two, three: I want the GPA, the standard deviation, and the count. So GPA equals, with a space at the end, and I'm going to click in... no, I'm not going to click on B4. I want to type B4, because clicking would give me that GETPIVOTDATA function, and I don't want that.
Now I'd like to have all three here, and in order to show up nicely in the chart, I need to put a hard return in my formula. I want one, two, three listed vertically on top of the column, so I'm going to use the CHAR function. We know our ASCII characters, and there are 255 of them, and 10 is actually a hard return. Now let's join the rest of it. Here's the rest: Standard deviation equals B10, which is in the second pivot table, then CHAR(10), and Count equals, and we're looking at the third pivot table. We're going to run into a problem with number formatting. Control-Enter: formulas cannot see that number formatting, so I have to hit F2. We're going to use the TEXT function, which converts a number to a text string given a custom number format, in double quotes "0.0", and close the parentheses. We'll have to do the same over here. I did the same thing here, showing three decimals, because I want a little more on the standard deviation. We don't have to add TEXT for count, because that is a whole number. Let's see what this looks like. Control-Enter. That's looking great. We could add word wrap to see that it's working, but we don't need to. Control-Z, because the formula that connects these labels will understand the CHAR function. We'll copy this down.
Now let's go to our chart data labels. I click once, Control-1 to open the task pane, and we're going to uncheck Values, which removes them. Now we're going to use Value From Cells. Here's our data label range. We simply highlight it, click OK. That looks like it's working. I do want to indicate that GPA is actually the column, and SD, standard deviation, and count are different. So I'm going to go up to our formula, F2, and do something totally crazy: I'm going to add another hard return. Copy, Control-V, so there are two characters, Control-Enter, and double-click and send it down. All right, we could do this in various ways, but GPA is actually the column. This is extra data. Wow, check that out.
Let's close this, and guess what, we don't need the pivot tables or our formulas. I'm going to click on the edge of the chart to lodge it up in the corner. Now let's get our slicer, and you've got to be kidding me, look at that, absolutely amazing. So in this video we did some fancy charting, some fancy labeling with formulas, and there are some pivot tables under this chart and a slicer. We even started back over here using that amazing Power Query From File, From Folder. All right, we'll see you in the next video.