Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data Model

Welcome to Highline Excel 2016 class video number three. Hey, if you want to download this XL file, Business 218 Video 3 Start. There is also a finished file. There is also a zipped folder with a bunch of text files and the PDF notes. If you want to download any of those files and follow along, click on the link below the video. Wow, we got an amazing video here. This is going to be Excel fundamentals for data analysis and business intelligence. We are going to talk about Sort, Filter, Pivot Table, Power Query, and PowerPivot. Now this is going to be an epic video-- like an hour and 40 minutes long. But if you want to do data analysis and business intelligence, this will give you the fundamentals. Later in the class, we'll go in more detail to these, but this is going to be our building blocks. Here are our topics. Now before we start with Sort I want to go over to the sheet "Require." Because all of the built-in data analysis features in Excel-- Sort, Filter, Pivot Table, Excel Table Feature, Get and Transform, which is really Power Query and PowerPivot-- all require that you have a proper data set, field names at the top, records and rows, and empty cells all the way around your data set. And when we click on the button to enact any of these, you either have to have a single cell in the proper data set or highlight the entire table. Now we're going to start with Sort. I'm going to click on the sheet "Sort." Now sorting is pretty straightforward. It's the most basic of the data analysis features. We might want to show the numbers smallest to biggest, or biggest to smallest, or sort alphabetically. Now here is a data set. We have date, where the race was-- this is BMX bicycle racing-- the name of the racer, the age, and the time. So for this first sort, we simply want to sort one column. We want to bring the fastest times to the top. So we're going to sort the time column. But the question is when you sort for the first time, if I'm going to sort this column, will the rest of the records remain intact when I sort the time column? So I've added some yellow here. When we sort this time column, we'll see that the entire record will move together. Now we have a proper data set and we need to click in a single cell. Now there are three different ways that we can sort. We can actually go up to the data ribbon. We can use the buttons A to Z , or smallest to biggest, or Z to A, biggest to smallest. So we can use buttons, we can use the Sort dialog box or we can come and right click. And down here is Sort with a bunch of options. Now when you're using right click Sort or the buttons, you actually have to click in the column that you want to sort. If you use the Sort dialog box, then you can click anywhere in the data set. All right, so let's do this. Click in a single cell. And I'm going to use A to Z. And watch that yellow record. And instantly, the whole column is sorted and the record remains intact. So clearly we can see Zaine, 10 years old, had the fastest time at PI. Isaac, 7-year-old had the second fastest time. Now here, we just sorted a single column. Sometimes we want to sort more than one column. So I'm going to scroll down here. And we're going to see two different ways to sort more than one field. We'll see using the buttons and we'll see using the dialog box. Now our goal with this same data set is to sort times for each racer. You might also hear it this way. Sort times within the racer or column. Both of these phrases mean that we need to get all of the Isaacs together in this column. Then, all of the Logans. There's one Oscar. Then, we need all of the Zaines. So this has to be the final sort that we do. Then within that sort, I need multiple sorts over here. So I'm going to need for Isaac to have his times smallest to biggest. Then for Logan's, smallest to biggest. Oscar and then Zaine's smallest to biggest. If you're using the buttons or the right click method, the final column-- racer in this case-- is called the major sort. And you have to do that one last. So we're going to start with the time and I'm clicking in that column. I'm going to use the buttons. I'm going to say A to Z. Now the reason that we have to do this one first and the racer last is because this is the final sort. And we can see that it will work because before we even sort, if we look at all of Isaac's times-- one, two, three-- they're already sorted in the perfect order. So now when I come over and do the second sort on this column, all the numbers will fall into place perfectly. All right. So this is the major sort. We do it last when we're using the buttons. I'm going to say A to Z. And instantly, it is as if we have this column sorted, and then we have a bunch of mini sorts over here. There are Isaac's smallest to biggest. There are Logan's smallest to biggest. There's Oscar's single score, and then Zaine's. Now I'm going to Control-Z-Z to unsort that. Now let's do it with the Sort dialog box. And sometimes people like this better because they don't have to remember which order to click the button. You can click in any single cell. You go up to the Sort dialog box. Here is our first sort. We want to do our major sort at the top. Although it doesn't matter as much here because we can move around our sorts before we click OK. I'm going to select the racer. So that's this column here. I'm going to select values. And I want A to Z. Now I come up and click add a level. Notice it says "sort by racer, then by time. And some people like this better because the major sorts on top and the English here sort by racer, then by time sort of makes sense. We want values and smallest to largest. Now when I click OK, just like that, we have the same result. I'm going to go back up to that Sort dialogue box. Notice that if I had accidentally done time and then racer, I can clearly click on this and move the up button. Now I'm going to click OK. That'll do the same thing. Now this was two columns we wanted to sort. Let's go look at an example with three. And this will be an example where the Sort dialog box really will be easier. We need to sort sales descending. So on the sales column, we need biggest to smallest within each color. Now someone has color-coded these records. Red are the urgent ones, and we need them on top. If we scroll down-- and by the way, control down arrow-- this is like way over 4,000 records. But if you scroll to the middle, you'll see that there are some yellow ones and some red ones. The yellow ones are the second most important. Now I'm going to click in the cell control up arrow. So we in essence need to do this-- red then yellow. And within those two, I need descending biggest to smallest. All right, we're definitely going to use the Sort dialog box. I click on the button. By the way, there is a keyboard. It's Alt-D-S. Now the way I remember Alt-D-S is D is for data analysis, and S is for sort. That's actually an old keyboard, back to before we had ribbon tabs. There used to be a data menu. So you'd click Alt-D for data menu and S for sort. I'm going to do the major sort, and watch this. I can pick any one of these columns because the color is in all three columns. I'm going to select the ISO date. And once I come over to this drop-down, sure enough, cell color. That opens up this drop-down here and it automatically goes through the entire column and detects all of the different colors. I want red on top. Notice we can do on bottom also. Now I say add a level. I'm going to do date again. Values-- I don't want values. I want cell color. And then I'm going to select yellow. That's the second most important. And finally, I say add a level. And then we want sales, values, and we want largest to smallest. So now we have our hierarchy sort by date red, then by date yellow, then by sales largest to smallest. When I click OK, just like that, these urgent records, with the biggest ones at the top down to the smallest, the yellow ones biggest to smallest. All right, so those were three examples for Sort. Let's look at one last example. Because in those examples, we had the proper data types in each column. It was all text or all numbers, right? We talked about different data types in our first video. We can have numbers, text, false, true. We could also have errors in empty cells. And this is the order when we click A to Z. Numbers will come to the top, then text, then false, then true, then errors. Empty cells always go to the bottom. So one way to use Sort is to get errors all together or empty cells. Now a rule is click in a single cell and click on the button. But that will not work here. Because if we really do have empty cells, you have to highlight the entire field name and all of the data. Now we have numbers, text, empty cells, and some error values. We don't have any Booleans. So I am going to actually add right here some Booleans. Watch this. I'm going to click and drag this down here. I'm going to type true and then false, and highlight these. I like to have that border there. But now I'm going to highlight the entire column. And now when I click A to Z, we can sure enough see numbers from smallest to biggest. We have text smallest to biggest. And notice that the capitals come before the lowercase. False, true, errors, and empty cells went to the bottom. That's exactly as we saw this hierarchy up here. All right. Sorting is very helpful and very useful. Now we need to go over and talk about Filter. So I'm going to click on the sheet "Filter." Now, we're going to talk about Filter and what Filter does to a proper data set. Let's say for example, we wanted to see only the transactions for the month of January in 2017. Or you might want to only see the transactions for the sales rep Anna, or only Honda transactions. We might even want to filter all records and see only records for the top 10 sales. Now you might ask, hey, why don't we just sort? If we wanted to see all the Hondas, we could just sort and then go to the section where there's Hondas, and look at them, or highlight them and copy them somewhere else. But the problem with sorting is lots of times, the records you want are in the middle of the data set. And it might take a long time to find them. Filter will do something entirely different than sorting. It will actually hide all of the records that are not Honda-- if our criteria for filtering is Honda-- and display only the Hondas. And it's quick and easy. Now let's see how to filter. Same as always when we're doing data analysis on a proper data set, field names at the top, records and rows, empty cells all the way around. I click in a single cell. And up on the data ribbon we have this big Filter button. Now we can click the Filter button on and off. If I click it, notice wow, there's the drop-downs. And I can select from the drop-down whichever condition or criterion I want to apply for filtering. Notice that this is a toggle. I can click click, and it turns it off and on. There is also a keyboard-- Control-Shift-L is the keyboard-- and it is a toggle. Now if you can't remember the keyboard, you can hover your cursor, and it says right up there "Control-Shift-L." If you want an easy keyboard to remember and you like doing Alt keyboards, its Alt-D-F-F. D for data, F-F for filter. One other way to apply a filter-- and I like Control-Shift-L. I'm going to use Control-Shift-L to toggle that off. If I convert this to an Excel table using the Excel table feature with Control-T, it got the right data set. And when I OK or hit Enter to enact it, that of course will add our sorting and filtering options at the top. Now I'm going to Control-Z, because I want to convert this to a table. Now, let's turn on the filter-- I'm going to use Control-Shift-L-- and let's see filter in action. Here is the drop-down for the auto feel. Click the drop-down. And one amazing thing is it will always show a unique list. It actually for every column, it will look through and show one of each item in this column. So we can use the checkbox. And lets uncheck using select all. And now I'm going to select Honda. And when I click OK, all the records that do not match Honda are hidden, and only the Honda records are visible. Now notice there's a bunch of ways we can tell that this is Filter. One way is we see a little Filter icon. The other way is we can notice the color of the row headers are blue. And certainly yet another way is there are missing rows. 16 is missing. 18, 19, 20, 21, and 22 are missing. Now with only Honda records showing, we can look through and analyze or copy and paste this somewhere else. Now I'm going to unfilter this. And there's a few ways. You can click on the drop-down and clear the filter right here or you can go up to data. And there is "Clear Filter." and I'm going to click "Clear Filter." We can come over to the date field. And there is what appears to be a unique list. But to see the full unique list, we have to expand using the plus. That will show me the months. And I could actually get down to the day level by clicking the plus on January. And there is all the days. Now here, what we'd like to do is filter and see only January 2017. So I'm going to uncheck select all, open up 2017, and check January. When I click OK, instantly we have filtered the data set, showing only the records for January 2017. Now I can clear this. For each one of the columns, when we open up our drop-down arrow, we see a unique list. Now this is a column where it might not make sense to use that unique list. Now also, the filters will show as special filters based on data types. Now we know from our study of data types these are considered text items and these are numbers. But for filtering, there's some special date filter. So really in this data set, we have date, text, and numbers. Let's look at the special date filters first. Click the drop-down and date filters. You are not going to believe it. We can say equals a particular day. We could say before, after, between two dates. And look at this. There's all these amazing tape filters-- tomorrow, today, yesterday, next week-- based on months, quarters, and years. Now when would you have next month or next week? If you had some future projections. Hey, were going to try one of these date filters. I'm going to use between. Now between really has two conditions. And it is a type of and logical test. Now it says "is after or equal to," so I could select. And let's select January. So I'm going to go back to January 1st, 2016. And then the second part is "before or equal to." And here, I'm just going to type 3/31/2016. Now notice, this is the lower limit. This is the upper limit. It has to find dates between these two, including both the start and end date. And when I click OK, just like that, I have filtered the date column based on an upper and lower date. Now I'm going to clear this filter. There are special text filters. If I click the drop-down, it says text filters-- equals, does not equal, begins with, ends with, contains. Now we don't really have a good example for contains. But contains is great. For example, if you had lots of addresses, and you wanted to find only the records that were on San Pablo Ave, you could say, "contains San Pablo Ave," and it would filter down to all the addresses for San Pablo Ave. This is also a text field. Let's go over and look at the third data type in this data set. We're going to look at number filter. We could say, "equals does not equal greater than a particular hurdle," right? Greater than or equal to, less than, less than or equal to, even the same between. But look at this. We have top 10 and above average. Let's try top 10. Now we're not limited to top 10. We could go more than the top 10 or less. I'm going to say top eight. We also have bottom. There are items and there is also percent. We're going to say, hey, show me the top eight items, which will be the top eight biggest numbers. When I click OK, I have filtered down to just the top eight. Now I'm going to unfilter this. Perhaps the best use for Filter is this. Your boss emails you and says they need all of the records for the sales rep Alma and the autos sold Chevy. So we're going to apply criteria both to the sales rep and the autos column. So I'm going to select the drop-down, uncheck using select all, select Alma, click OK. So now it just shows Alma. But now I want to eliminate all the records except for Chevy. So the condition or criterion is going to be, hey, unselect all, and I'm selecting Chevy. Click OK. Now I have just the records I want. And my boss said they wanted us to email these records. So I'm going to highlight the entire data set using Control-* on the number pad. If you don't have a number pad, use Control-Shift-8. Then, I'm going to use Control-C to copy. And notice what happens. It is copying just the visible records. Those dancing ants are having a huge dancing party. There dancing all around. There dancing around only the visible records. Now I want to open up a brand new workbook, and I don't want to go File, New. So I'm going to use the keyboard for a new file-- Control-N. Now I'm already in cell A1, so I use the keyboard for paste-- Control-V-- and look at that. I'm going to double click up here. We have just the records the boss wants-- Alma Chevy records. Now I'm going to double click this sheet down here and call this "Alma Chevy Records," and Enter. Now I'm going to use the keyboard F12 to save as my Business 218 folder. And I'm going to give this the same smart name-- Alma Chevy Records-- and Enter. So with just a few clicks on our Filter, a few keyboards, and I have extracted just the records I want, put them in a new workbook, and I'm ready to email it to my boss. Now notice we have two columns filtered. In this case, I don't want to go to each one of these and select Clear. This is where the Clear All up here is pretty handy. And here's a great cheat that I use all the time. Since the keyboard for Filter is a toggle, I can do Control-Shift-L-L. And just like that, I have unfiltered and then added the filter back in. Now the question our boss asked us was, hey, I need to see the records, but only when the sales rep is Alma and the auto was Chevy. That means we use two criteria, or two conditions, to determine which record to extract. Now we need to distinguish between what's called an "or logical test" using or criteria, and an "and logical test" when we're using and criteria. Anytime we have more than one condition or criteria, we might be doing an or logical test, or we might be using an and logical test. Now here's an example of the or logical test using or criteria. We need to see all of the records based either on the sales rep Alma or the sales rep Rina. Now the way we enact or criteria on a single column is we come to the drop-down, we uncheck Select All. I'm going to select Alma. And as soon as I select down here Rina, because I have two check boxes on this column, I'm using or criteria, or making an or logical test. When I click OK, instantly all the records show only Alma or Rina. Now I want to get rid of this filter and talk about what Filter actually had to go through to display just the records for Alma or Rina. I'm going to clear this. That means if I want to see Alma or Rina, Filter had to ask the question every single time for every record, are you Alma or are you Rina? Because we got one true there, we would take this record. But the or logical test continues for every record all the way down. We would have to ask the question here. Are you Alma or are you Rina? We get two falses, so we are not selecting that record. We keep going down. This record we'd ask the question, are you Alma or are you Rina? We would get one true, so we would select that record. That is an example of an or logical test using or criteria. Now this is going to become very important through the rest of the class, because with Filter, formulas, pivot tables and other features, we will either be doing or logical tests or an logical tests. Now I want to jump over to our note, because I have some very helpful notes on this particular topic. And again, this is the first time in this class we're trying to learn about what or a logical test and an logical test means. I'm going to click on the Filter feature in the table of contents in the PDF notes. There's some great notes for Filter, including all those keyboards we used to extract those records. But on the next page under Filter, here's the description of an or logical test. Now if we use the check boxes Alma and Rina, again what we're doing for each record, we're asking two questions. Is the sales rep Alma or is the sales rep Rina? Now notice if we ask those two questions of each record, here's the possible answers we can get-- true, false, meaning we found Alma but not Rina. We could get false, true, meaning we didn't find Alma. We found Rina. Or false, false. We didn't find either one. So when you're doing an or logical test, in order for the record to be extracted, you have to get at least one true. Now the reason I say "at least one" is because our or logical test here only had two criteria. We might have three or four. So in order for an or logical test to get a true, we want that record. And at least one true must be found. Now I want to go back over to our data set. Let's talk about an and logical test using and criteria. If instead the question is, hey, I need all the records where the sales rep is Alma and the auto is Chevy, I would have to get two trues in order to extract this record. That means when I'm asking the first question of the sales rep column for this record, are you equal to Alma? Well that would be true. Then the second question of the autos column for this record. Are you equal to Chevy? Well since that one is true, they're both true. We get to extract this record. Notice if I ask the question from the next record, are you equal to Alma? False. Are you equal to Chevy? True. Because I didn't get two trues, I do not take that record. So what distinguishes and logical test or AND criteria from OR logical test is I have to get two truths. Now let's go back over to our PDFs. So for an AND logical test using AND criteria, for each record, we're asking two questions-- is the sales rep Alma AND is the auto sold Chevy. Notice for each record, we can get four possible answers. We can get TRUE, FALSE, not taking that record. FALSE, TRUE, not taking that record. FALSE, FALSE, it's only when we get TRUE, TRUE are we allowed to take that record. Now for AND logical tests must get all our TRUE. Now, in this case, both are TRUE, right. But we could easily have an AND logical test where we have three or four or five conditions. That's why we say when we're doing an AND logical test, we must get all are TRUE in order to take that record for filter. Now again, we will see OR and AND logical tests in basically everything we do in this class moving forward. All right, I'm going to Alt-Tab. So if we're doing AND and I want Alma and Chevy, of course, I come to the check boxes. I select Alma for this column. There's all the Almas. And then I select Chevy for this column. And there I have TRUE, TRUEs all the way down. Now I'm going to click in a single cell and do Control-Shift-L, L to get rid of that filter. Now here's an example of AND when we have lots of conditions. I want to see from the date column. Uncheck Select All. I only want to see for 2016 February. Click OK. Then I want to only see Alma, uncheck Select All and Alma. And I want to see only the Fords. And click OK. So there are only 4 records that match one, two, three conditions. All of them have to be true. I have to get TRUE, TRUE, TRUE. Now I'm going to click in a single cell, Control-Shift-L, L. Similarly for OR, we could have many possible conditions. If I wanted to see all the records that were either Anna, Cynthia, or Miguel, I would select all three and click OK. For every single record, I had to ask three questions. And all I needed to get was one TRUE. All right, so I have a single cell selected, Control-L, L. Now there's two other important types of logical test. BETWEEN logical test, which actually is a form of AND logical test and NOT. Now we already saw BETWEEN when we asked the question of this column, were you BETWEEN January 1st, 2017 and March 31st, 2017, that was a type of BETWEEN. Each date had to get a TRUE. Yes, it was greater than or equal to the start date and yes, it was less than or equal to the end date. Now I want to look at an example of BETWEEN. But now we're going to do BETWEEN criteria on the sales column. I just want to see the sales BETWEEN 50,000 and 25,000. So I'm going to click the drop-down. And under Number Filters, there is BETWEEN. Now it comes up, is greater than or equal to and is less than or equal to. Notice there is a greater than sign and an equal sign. You do not have to leave the equal sign on both sides. You could say is greater than. In our case, we're going to say, yeah, yeah, we want to see all the values, 25,000. So it has to be greater than or equal to that. And it has to be less than or equal to 50,000. When I click OK, instantly I am only seeing sales values between that lower and upper hurdle as I scroll down. And for that BETWEEN, which is really AND, remember, we had to ask the question of each number-- are you greater than or equal to 25,000, true, and are you less than or equal to 50,000, true. Now I'm going to Control-Shift-L, L. The last important logical test is NOT. If we are looking at all of the auto sales and we're like, we need to see everything except for Toyota, we simply can come here, Text Filters, Does not equal. We come to the drop-down and there's our unique list. I select Toyota and click OK. Every single record went through and says, are you not Toyota, true. Are you not Toyota, true. It went all the way down. All right, Control-Shift-L, L. Now of course, once you've done all your filtering and copying and pasting, you can Control-Shift-L and leave those filters off. All right, so that was a lot of important things about filter, including learning for our first time about OR logical test using OR criteria, and AND logical test using AND criteria. Now we want to go over to this sheet and we're going to talk about PivotTables. And I'm clicking on the sheet PivotTables. Here is our data set that we're going to use to create a bunch of different PivotTables. Notice we have the Date column, SalesRep, Region, Product we Sold, the Color of the Product, Number of Units, and Revenue. Now at its heart, what does a PivotTable do? Here it is. Here's the full description. But really what does a PivotTable table do? PivotTables make calculations with criteria more easily than any other feature in Excel. Now I have a picture of our finished PivotTable over here. And we want to look at it before we even jump in and create a PivotTable. Now this is called a cross-tabulated report. And it's called a cross-tabulated report because that number, 34 right there, is actually the sum of Unit, from the Unit column in our table. But 34 is not all of the units. It's some of the units based on criteria. And the criteria, If you look at the top of the column, is Midwest, from the Region column. And for that 34, at the head of the row is the product FlatTop from the Product column. So 34 is adding units based on one, two conditions. But wait a second, that's just the column header and the row header. We actually also have Color up in the filter area. And look at this, over in the SalesRep slicer, we have Team Number 1, which consists of Colleen, Drew, Kyle, Shari. So that 34 was adding from the units column based on one, two, three, four, five, six, and seven conditions or criteria. And what's so amazing about a PivotTable is if we tried to create this same report with formulas with seven conditions or criteria, it would be quite difficult. Now we're going to go and create this PivotTable. We're going to scroll over and click inside our data set. And the keyboard for creating a PivotTable-- well, first, let's look at the menu path, a single cell, a proper data set. Insert, Tables, PivotTable. Or we can click in a single cell and use Alt, N, V. And if we want the PivotTable on a new worksheet, notice that is the default. So all we have to do is click the OK button by using the Enter key. Now remember, we always will get the right data set if we have a proper data set with empty cells all the way around. All right, I'm going to hit Enter to put this PivotTable on a new sheet. I'm immediately going to come down here and double-click this Sheet 3 and name this UnitsReport and Enter. Hey, I'm going to drag the Field List. Normally, the Field List will come lodged up in the upper-right hand corner. You can point to the PivotTable Field List and click and drag. Here is our Field List with all of our fields. Now remember, we drag and drop down to the row to get a unique list. So we're going to take product, drag it down to Rows. And just like that, we get a unique list, one of each item from the Products field. Now I'm going to drag Region down to Columns. And instantly, we get a unique list of regions from the Region column. Now, we're going to make a calculation on the Units field. So we drag Units down to Values. And instantly, we default to the SUM calculation because we have numbers in the Units column. There is the start to our cross-tabulated table. Right now, the intersecting value, right there-- 203 units were sold in the South Region, selling the FlatTop product. That is a calculation with two conditions or criteria. Now I'm going to come up and drag Color down to Filter. Now whereas column header and row header are actually the criteria for that individual cell right there, any time we add a condition or criteria to the filter area, it filters the whole report. When I come up here and select the color Blue and click OK, every one of these intersecting cells is using Blue as a condition. Whereas, each cell inside the PivotTable is using an individual column header and a row header. Now before we add our slicer, I want to change row labels and column labels to the actual field names. So for every PivotTable we make, we will come up to Design, over to Layout, and Report Layout, Show in Tabular or Show in Outline. Either one of these will show field names. When I Show in Tabular, I see those gray lines. When I Show in Outline, I do not see the gray lines. I'm going to choose Tabular. I would like to add Number Formatting. And what we don't want to do is to highlight the cells and right-click Format Cells or Control-1 because notice the Format Cells dialog box with all of the tabs for formatting the cells. That would actually format the cells. And if we ever pivoted our PivotTable, the number formatting would not follow the field. So what we want to do is click in any one cell, right-click. And down below Format Cells is Number Formatting. This number Formatting opens up the Mislabeled Format Cells dialog box. It should say PivotTable Field Number Formatting, or something like that. But notice, it only has one tab. Remember, if we're out in the cells, right-click. There is no Number Formatting option. That only shows up when you come inside PivotTable. Right-click Number Formatting. And we're going to add number. And I'm going to add comma separator. And since these are units, we do not want decimals to show. We also don't want currency or accounting. Click OK. So now we have Number Formatting. If I come up and Select All, click OK. We can see the Number Formatting is working just fine. I'm going to go back to Blue. Click OK. Now I would like to add the slicer. And the slicer is just like filter. Knows we have a filter, right here. It will filter or add criteria to all the calculations on the inside of the PivotTable. With a cell selected in the PivotTable, we go up to Analyze. And in the Filter Group, we can select Insert Slicer. We have an option, we can check whichever one we want. I'm going to select SalesRep and click OK. Now I can format this, Slicer Tool Option comes up. I'm going to add, say, this color right here. We'll actually add different style formatting to our PivotTable in a little bit. But here, up in our Slicer Tools Options, we have buttons. And I want to select Columns. Please give me two. Now I can click and drag. Now if I want to filter the PivotTable or add conditions or criteria, I come and select. When I select Colleen, those are the numbers for Colleen. When I click Drew, those are the numbers for Drew. Now notice, this is really amazing. This is something formulas just have an incredibly hard time doing. Drew Rogers only sold items in Midwest, South, and West. When I click on Colleen, notice Colleen sold in all four regions. That's pretty amazing. The PivotTable adjusts. Now in order to select items not next to each other, we're going to use our Control Key. I'm holding Control and I'm going to click Drew, Kyle, and Shari. And just like that, I have all seven conditions. There is my 34. Now if we were talking about AND criterion, OR criterion, trying to create a formula, we would have to say, hey, look through the Products column and find FlatTop AND look through the region and find Midwest AND look through the Color column and find Blue AND please go over to the SalesRep column and look for Colleen OR Drew OR Kyle OR Shari. That would be really complicated. But with a PivotTable, it is just flat out easy. Now I do want to show you something else that is new in 2016. Well, of course this has been there before. We can click to unfilter. And now I'm going to click Colleen and then come up here and click this and click Drew, Kyle, and Shari. So that Multiple Select button there is new. I don't use it because I know the keyboard for selecting items not next to each other, which works in all products that we use. It's the Control Key. Now, I want to add some formatting here. I want to click inside the PivotTable. And up in PivotTable, Tools, Design, here's some PivotTable styles. And most of them are not very interesting. Well, you can click on which one you like. But what I like to do-- click the More button and this More button shows up. If I click More, there's all sorts of options. And down here, it says New PivotTable Style. And so I'm going to create my own PivotTable style. Now it does take a little bit getting used to what all of these different elements in the pivot table are. But we can click on, for example, the whole table, and then click the Format button. I'm going to Add Format and go to Borders. Now I want borders everywhere, inside and out-- so the outline, that does just the outside, and the inside, that does all the inside lines. Click OK. And so now there is format, and you can tell because it's bold. And over here, we have a little preview. Now I'd like to do the header row. That's this up here. I'm going to click Format. And I'm going to try and pick the same color as my slicer, so Fill. And there it is right there. I'm going to go over to Font and select Color and White. Now we're going to make a mistake on purpose. And we'll have to come back here later so we can see how to edit. But right now, I'm going to click OK. The only other element in the PivotTable I'm going to format is the grand total row. I'm going to select Format and Border. And as we've learned in our first video, borders can be pretty effective, especially when we have like the bottom line here. I'm going to select that medium thick border and select the top line. And again, this is only adding formatting to the grand total line. And then the double line to say, hey, this is the bottom line. This is what we're trying to calculate. Click OK. So now I have one, two, three elements that I'm formatting. Those are actually almost always the only elements that I format. There are some other ones that we can format. Now notice set as default PivotTable style for this file here. But we're not going to do that. I'm going to click OK. And look at that, it didn't do anything. But now we're going to come up to the More button. And there it is, right at the top. I'm going to click Custom to apply it. Now, a couple of things. I don't think I like the all borders inside here. So we can go and edit the header. And also I'm thinking I would like some color right here in the filter area. So now it's as easy as this-- right-click, Modify, Header row. And I need to format. So I'm going to come to Border and say None. And then click Outline. That'll do just around the outside, which is what I would like. Click OK. I think also up here in the filter area, I see Report Filter Labels and Report Filter Values. I want the label. That's the actual label right there. So I'm going to click Format, Fill. I'm going to pick that same color to be consistent. Font, come over here and get White. Click OK. Now I have what I want or I think I want. I can always come back and edit later. Click OK. And there it is. When I create a PivotTable later, I can come up and apply this. Now I can come up and select Filter. And by the way, you absolutely could have added color not to the filter but to a slicer. And really that's the way to go. I just did this to show you that we have one, two, three, and four different ways to add criteria or conditions to our PivotTable. Now one thing about PivotTables is people will use different terms to describe-- for example, there's our 34 again, right. People use different terms to describe FlatTop, Midwest, Blue and all these. Sometimes, people say these are criteria. Other times, they say these are conditions. Still other times, people will call all of these filters. Whatever term you use, that calculation right there is not adding all the units from the Units column, it is adding units only for these seven conditions, criteria, or filters. Now before we go on to our next PivotTable example, I actually want to name this PivotTable. Now we only have one PivotTable here. And if you have one PivotTable, you don't really need to name it. But in our next example, we're going to create a couple of other PivotTables. And so when you have multiple tables, sometimes it's nice to name the PivotTable. We go up to the Analyze tab, over up to PivotTable. And there it is. It's currently called PivotTable3. I'm going to call this ProductUnitsReport and Enter. Now, we've named our table. So let's go create a new PivotTable. I'm going to click on the sheet PivotTables. And before we grab our data set. Let's look at a picture of the second PivotTable we're going to create. We're going to have Product as the row header. And look at this, we're going to have one, two, three different calculations we're going to do-- total revenue, count the number of transactions, and calculate the average revenue. Then we're going to create a second PivotTable where we're going to have to group the transactional daily dates into months. And we're going to have total revenue. And we're going to do a special calculation called show values as. And we'll be able to calculate the percentage of each one of these revenues of the total. And then we'll add two slicers. And we'll make sure that both slicers control both tables. Now let's scroll over. And here is our data set. I'm going to click in one single cell. And I'm going to put this on the new sheet. So I'm going to do Alt, N, V, Enter. Here is our Field List. I want to make sure and come down here and double-click and call this Product and Month Report and Enter. All right, so our first table is going to have Product in the row area. So I drag it down to row. Instantly, I get a unique list. Our first calculation is going to be revenue. Then we're going to count transactions. Then we're going to calculate the average revenue. That means from the Field List, I have to drag three different fields down to values. All right, we'll start with revenue. I'm going to drag it. And since it's a number field, when I drop it in values, instantly it defaults to SUM. That will work for our total revenue column. Right-click, and I'm going to point to Number Formatting. Now I could do Currency or Accounting, but I don't want to clutter up the reports. So I'm going to select Number. A separator, and these are big enough values where I'm going to display zero decimals. OK. I want a different title at the top. I'm going to type Total Revenue. And then I'm going to indicate the unit, which is dollars in parentheses. And Enter. Now I'll change the column width here. But one thing about PivotTables is if we change by dragging and dropping fields or pivoting the report, the column widths always change. So watch this, I'm going to right-click and go down to PivotTable Options. And PivotTable Option is under the Layout and Format tab, I can come down, Autofit column widths on update. I'm going to uncheck that. I can also name my PivotTable. So I'm going to highlight this and call this something like ProductCalculations. Now to enact that button, I'm going to hit Enter. Now we have a second and third column. So we have to come back to our Field List. And notice, if I take the product, what type of data is in that column? Text. So when I drag it down to values, it will default to count. And that's what we want here. We're counting transactions. Because I put product here, it's counting how many transactions included in the Aspen. I'm going to indicate that at the top here with a label. I'm going to call it Count Transactions, and Enter. We're counting, so that number formatting is fine. All right, now we need to calculate average. So when I take the Revenue field and drag it down to values, and notice, we have three fields stacked up on top of each other. Look at that, it defaults to SUM, which is most of the time what we're doing. But we can totally change it. Now actually, in this case, we want to change the label, the function, and the number formatting. So instead of doing it one at a time, I'm going to just right-click. And down here is Value Field Settings. Value because it's the value area, field because it's a field, and settings because all of the options for this calculation in the value area is in this dialog box. So when I click, there's the Value Field Settings. It's sort of like one-stop shopping for the value area. Now Summarize Value By-- these are our functions. And there's 11 aggregate functions. We're going to select Average. There are other types of calculations, which we'll see in our next PivotTable called Show Values As. They do things like calculate the percentage of the total. Now here's our Number Formatting button. So I'm going to click on it. And it opens that dialog box with only the Number tab. So we know this is apply number formatting to the field, not the cells. I'm going to select Number, Separator. And this is for Average. So I'm going to leave two decimals. Click OK. So we got Average and Number Formatting. Now I'm going to change the name at the top. So I put Average Revenue. I'm going to indicate the unit in parentheses with a dollar sign. So now I can click OK or Enter. Now let's change the column widths. Now we still have to format this. And I definitely want the Product field name there. So I'm going to go up to Design, over to Layout, Report Layout, and Show in Tabular. Now I'm going to come over, PivotTable, Styles, click the More. And there is our custom PivotTable styles, so I'm going to click and instantly it is applied. Now I want to add a slicer. We want color. So with my cursor in the PivotTable, I go up to Analyze, Filter Group, and Slicer. There is color. Check it and click OK. Now I actually want the slicer above the PivotTable. So I'm very carefully going to highlight row 3 all the way to 5, and right-click Insert. This will insert three rows. All right, let's click on the slicer, Options. Go over and I think we'll have each color on the same row. So there's five colors. So I'm going to click 5. Now we can click and drag. And then drag the slicer over here. Maybe change the color to match our PivotTable. So now when I click Blue, there, it is filtering the calculations for each one of these products. Now that's looking pretty good, but we need a second PivotTable right here. So I'm going to go back over to PivotTable Sheet, click in a single cell, Alt, N, V. No Enter here because I do not want the default. I'm allowed to put this anywhere in the workbook. So I click Location. Go down to Product and Month Report Sheet. Scoot this over. And I'm going to try and click in cell E6. When I click OK, there we go. Now notice, this field list governs this PivotTable. If I were to click inside of this PivotTable, oh, yeah, that's all of the work we did earlier. But when I click back in this PivotTable, new Field List. Now our goal here is to have months and then year up in a slicer. But I want to go back over to PivotTables and look at our data set. Now before Excel 2016, if we had individual daily dates and we dropped them into a row area of a PivotTable, it would give me a unique list of all the days. And then we would group them by year and month. But watch what happens in 2016. I'm going to go back over to this sheet. There's the date field. In Excel 2016, when you drag it to rows, it assumes that you want to group. There's the year. I can click the Expand Plus. There's the quarters. Click the Plus. There's the months. Down here, you can see it's added a new option in our Field List for years and quarters. Date still means months. Now one note-- if you really wanted individual daily dates, which you do when you're doing things like daily sales report, you're going to have to come over and right-click. And instead of like in earlier versions where we had to group in the first place, that's already done. If you want daily dates, you click Ungroup. And instantly, now you have a unique list of all the days from that date column. Now I'm going to Control-Z because we really do want it grouped. Years-- that new field, we do not want the row errors, so I'm going to drag it up here. We're allowed to add that to a slicer later. Quarters-- you can drag it up here or you can actually drag it anywhere off the row area and it is gone. So that's pretty cool in 2016. It automatically groups for us. Now we want two calculations. I'm going to take Revenue and drag it down to Values. And since they're both on [INAUDIBLE], I'm just going to drag them both down there. There they are. Now I can come up and I'm going to format the first column-- right-click, Number Formatting, Number, and that same separator, and zero decimals display. Let's click in the top. And I'm going to call this Total Revenue. And in parentheses, I'm going to indicate the unit with a dollar symbol. Now let's come over here. And how do we do this? We want each individual item as a numerator, and compare it to as a denominator of the total. So I really want each one of these as a percentage. Simply come over, right-click. And now there's two options for calculations-- Summarized Values By, those are the functions, Summarized Values As, these are amazing, Percent of Grand Total, Percent of Column Total. In fact, in our case, since we only have one field in the row area, these two things will give us the same thing, Percent of Row Total. We might do Differences From. If we did that, it would give us the difference between February and January, March and February, and so on. Running Total In, this would give us a running total adding each month as it went forward. But we want Percent of Column Total. And instantly, it adds that calculation and the Percentage Number Formatting. Now I'm going to come up here and give this a good title. I called it Percent Revenue of Total. Now we want to get rid of row label. So I come up to Design, Layout, Report Layout, Show in Tabular. I want the same style. So I come up More button and click on our style. I want a slice or For Year. So I come up to Analyze, Filter. And there is Slicer. And look at that, quarters and years are added. Check years, click OK. Now sometimes, we get these ghosts. Or sometimes, if we're clicking on buttons and some of them don't apply, they show in this light color here. But if I don't want those to show up, I can change the settings. Right-click, Slicer Settings. And down here, it says Hide Items with No Data. Notice, slicers automatically have a name. They're called whatever the field or created item is. In this case, Years. I'm going to click OK. Now let's change. Actually, before we do that, let's make this two columns. I'm going to try and fit it. Now watch this, here's a great feature. We can select both of them using our Control Key. And then in Arrange, I can come up and Align Top. And then I'm going to go look. And see over here, not buttons but size. Its 0.67 height. So I'm going to click on this one and say, oh, they're both 0.67. Look at that. Now with this one selected, let's add this same formatting. And there we go. Now wait a second, look at this. I'm clicking on these years, but it's only controlling this PivotTable. If I click on Purple, it's only controlling this one. No problem. Now before we fix that, let's come over here and I want to name this because we're going to have to know which PivotTable is which when we're connecting slicers to PivotTable. Right-click, PivotTable, Options. And I'm going to uncheck Autofit. By the way, sometimes, Autofit is fine. But here, I don't want it. And our name, I called it Month Calculations, and Enter. Now we can come over to the slicer, whichever one first. And right-click Report Connections. And this is where our naming comes in handy, otherwise, they're named PivotTable 1, 2, 3, 4, or however many you have. We want for color to connect both month and product calculations. And click OK. Right-click, Report Connections. And we want ProductCalculations and Month are both checked. I click OK. And now when I click 16, that is like magic. Here is red. So red products in the year 2016, there are our calculations. Red 2016, there are our calculations for each one of the months. Now another useful trick in PivotTables is sorting. Now for January to December, it's already sorted the way we want. Over here, even, we have alphabetical, which is nice. But what if we wanted to sort Total Revenue so the biggest revenues were on top? Now normally you think about sorting as clicking in a single cell, but let's come over and click on this drop-down. Two things, there's a bunch of filtering. So you actually can do filtering, just like we saw earlier in this video for sorting. But I would like to sort. And notice, it's A to Z. If I click those, it would sort the product names. But for PivotTables, we have this More Sort Options. I'm going to click on it. Drag this over here. I want it descending. And notice from the drop-down, I certainly am allowed product. But what I'd like is, oh, and look at that. Each one of the columns with our calculations are listed here. I'm going to say Total Revenue when I click OK. Instantly, that whole thing is updated. Quad was the biggest. V-Rang was the smallest. Now we actually have one last trick we want to see for PivotTables. Now notice here we are in essence filtering the whole report in both of these PivotTables with our slicer. Now when would you ever want to actually use, over here, the filter area. Well, here's a great trick. Let's go back over to our PivotTable sheet. Here's our data set. And the boss asked us for a regional report, which would be easy enough. We drag Region, we drag Revenue, and we have the total for each region. But the boss said they wanted an individual PivotTable on each sheet for each product. So instead of creating one PivotTable and then copying it over and changing the filter, there's a great built-in feature that will do it for us. Now I'm going to click in a single cell, Alt, N, V. I'm using enter because I want the default new worksheet. I'm going to double-click here and call this Region Total, and Enter. All right, so here we go. We have Region. I'm going to drag it down to Rows. There's our unique list. Revenue down to Values. Right-click, and I'm going to use Number Formatting. And this time, I'm going to use Currency, displays zero decimals, click OK. We're going to drag Product down to Filter because, remember, we're trying to find a good use for this because most of the time, we're just going to use slicers because this drop-down is not as easy to use as a slicer is. But here's the trick. And before we see how to use this to create an individual sheet, I definitely want to do a few things. I don't want row label, so I go to Design, over to Layout, Report Layout, Show in Tabular. I'm definitely going to click drop-down and add our custom style. Now, it says All There. So we're going to go over to the Analyze PivotTable group. There's the Options drop-down. And there it is. Show Report Filter pages. There's even a little icon that says, page, page, page, page because it will create a bunch of new sheets, one each for every product that we have. So when I click this, it's going to ask me Show Report Filter Pages for what. Well, we only have one field drop here. Sometimes, we have multiple ones. Hey, so Product is highlight, I click OK. Now before you click OK, watch down here. In fact, I'm going to move this down here just so we can see this. When I click OK, it's going to go brrr and add one new sheet for each product. So I'm going to click OK. And just like that, that was fast. There's Aspen, and there it is. Bellen, there it is. Carlota, there it is. Each time, you can see, up on the top, it's been automatically filtered, has the exact formatting we've done. Now I'm going to click on Crested Butte and use my keyboard, Control-Page-Down. And I'm going through each one of these. And look at that. The Regional Total is the last one. And to the left, each one of these sheets was named for each one of our products with, for example, the V-Rang. There is our PivotTable. So PivotTables, the most amazing data analysis business intelligence tool in Excel. Now we want to go onto our next two topics, Power Query and PowerPivot in the data model. But, both of these tools are related to PivotTables. And I want to remind ourselves of how did we get to this PivotTable, let's go back over to sheet PivotTable. Yes indeed, we had a proper data set with raw data. Now the question is what happens if the raw data is messed up or we don't have a proper data set. In that case, we would have to clean and transform our data. So one of the next tools we're going to talk about is on the Data ribbon tab. In the Get and Transform group, we're going to talk about Power Query. Not only that, but the second tool we'll talk about is PowerPivot and the data model. Now, the data model, we can see here on the Data ribbon and tab and the Data Tools, there's a Managed Data Model. Now, if you do not see this button on the Relationships button, remember, this class is being taught with Excel Professional 2016. But the Data Model will just allow us to make a PivotTable from more than one table. And it will also allow us to analyze and make PivotTable from millions of rows of data. And we'll see that in our next topic. All right, so we're going to start with Power Query. And we're going to go over to the sheet. I'm going to scroll over here. I have a sheet PQ for Power Query and Get and Transform. Now we need to talk a little history here because Get and Transform is a brand new group in the Data ribbon tab. Remember, we're in Excel 2016. And earlier versions 2013 and '10, you had to download and add in a separate tab called Power Query. But of course, Power Query is so important for cleaning and transforming data that, in 2016, they just put it right in their regular Excel. We don't have to download anything. Now I want to jump over to our PDF file, which you can download. And on the first page in our PDF, I want to click on the Table of Contents, Introduction to Power Query. Now what does the word query mean? The word query simply means, hey, we're asking a question. Query and data analysis means we're asking a question of the raw data or of the tables. Some of the questions we might ask the raw data and tables are questions like, hey, we need to clean the raw data. Can you please remove unwanted characters like spaces? Can you please split data apart into desired data? That's the example we'll do in this video. Other questions we might ask have to do with transforming the data. We might ask, hey, can you please unpivot a cross-tabulated table and put the data into a proper data set. Or we might say, hey, take a bunch of files and append them one on top of each other to create a single proper data set. So Power Query equals Get and Transform group. And it's a new feature in 2016 that will allow us to import, clean, and transform data. Now one other thing that is just unbelievable about Power Query is once we use Power Query to import, clean, and transform the first time, if the source data ever changes, we simply click Refresh and Power Query will rerun the import, clean, and transform. And our data set will be instantly updated. It is quite amazing. All right, let's go back over to Excel. Here's our first example. Notice, we get this data set every couple of weeks. And we're supposed to analyze it by taking the amounts and making a cross-tabulated table by product and region. But look at this. The computer system spits out a description, which combines product, date, and region. So no problem. What Power Query is going to do-- and I have it up here-- is we'll be able to clean the data, which means break this apart into three separate fields. And then we will make a PivotTable from the clean data. And then later, and this is what's so amazing about Power Query, when we add new records that we get next week to this data set, all we have to do is refresh the PivotTable, and everything will update. All right, here we go. We're going to clean the data, make a PivotTable, have clean data and PivotTable update when source data changes. Now let's look up here on the Data ribbon tab. If we are getting external data, it means from outside of Excel, we might say, hey, please go get it from a file or a database or other sources. We'll see an example in just a little bit. But if the data is in Excel, like it is here, we have to click this button. Now what is From Table mean? It really should say From Excel table because you can't bring data from an Excel spreadsheet into Power Query unless it's an Excel table. Now why is that a requirement? Very simply put, because Excel tables are dynamic. If I add any new records, and I'm going to Control-Down-Arrow, we only have about 350 records. But if I add new records to the bottom, I need the Excel table feature to incorporate them into the table so Power Query will see the new records and the PivotTable will see the new records. All right, you ready? If that means we have to convert it to a table, I click in a single cell, Control-T. It got it right because there's empty cells all the way around. My table has headers. Click OK or hit Enter. Now I immediately want to come up to Design and Properties, rename this table or Alt, J, T, A. This is going to be the StartRevenueTable, and Enter. Now with the entire table highlighted or a single cell, I go up to Data. And this is going to be the first time we get to use Power Query. I'm going to click From Table. Now there is a keyboard for this, Alt, A, P, T. And that opens up the Power Query Editor. Here is our two columns. There's the name from the table. It already has some steps. And this list of steps is quite useful. If we make a mistake, you just calm and delete it and start over. That's like the undo in Power Query. Now let's come over. We're going to click on Description. And we need to learn a new term in databasing. And notice product, date, region. It's as if we have one, two, three fields. There is a character, or in our case, a series of characters, space, forward slash, and space, that separate the fields or separate the bits of data. The term to describe the characters that separate data or fields is delimiter. Now the reason that that's so important is because when we come over to Home, Transform Group in the Power Query Editor, there's Split Column. I'm going to click the drop-down, and sure enough, By Delimiter. How do we want to split the columns? By delimiter. We could also do number of characters if we had an exact number of characters for each column. So I'm going to click By Delimiter. Here's our Split Column By Delimiter dialog box. Now there are some built-in options here, none of which apply in our case. So luckily there's Custom. And in the text box, I'm going to type a space, forward slash, and a space. We can choose at Left Most, that would break it just there. Right Most, it would break it just there. Or at Each Occurrence. That's the one we want. I'm going to click OK. And just like that, look at that, we have four columns. Now here's a couple of new steps. We still have a few more things to do. Double-click Description 1, and I'm going to call this Product. Enter. Double-click Description 2 and this is Date. Enter. Description 3 is Region. Enter. And I'm even going to change this column from Amount to Revenue. And Enter. And there it is, it added a new step. Now another thing we want to do is we want to select each column. Home, Transform. Look at that, we are allowed to define a data type for the whole column. Now in our prerequisite class, Business 216, we learned about building tables for databases. And we always had to define our data type. It's really hard to do that in an Excel spreadsheet. But because we're running this through Power Query, we're allowed to do that. Now in this case, I think Power Query guessed them all correctly. But you always want to check. So I'm going to click on Date. Sure enough, its date. Region, it is text. Revenue, and it is whole number, which is fine. Notice, we have a bunch of options here. I'm going to click Escape. There is our new table. There's the name from our table over in the Excel spreadsheet. And I do not want to leave it as StartRevenueTable. I'm actually going to change this to EndRevenueTable. And the reason why is because this Power Query, cleaning and transforming, will be loaded back into Excel as an Excel table. And we want that as the name. Not only that, but this will be the name of our query. And if we ever need to come back and edit or look at this later, we'll just find that name. All right, now we go to Close and Load To. And I'm going to select this one. This gives you the full options. If you use this one, it just puts it in the sheet, which actually we could use because we are going to put it on a new sheet. But I'm going to click Close and Load To. This one gives you the options. Yes, we want a table. Later, we'll see what Only Create a Connection does. Select where you want the data to be located. We want the default new worksheet. So I'm going to click Load. Now, it put it at the very end of our workbook. That is not where we want it. So I'm going to right-click Move or Copy. Now I'm allowed, in this case, I'm not going to check Create a Copy. We're going to move it. We want it in this workbook. And I'm just going to select the sheet, down-arrow, down-arrow. And I want it before the sheet Power Query Get and Transform. Click OK. Now I'm going to double-click this. And I called it EndRevenueTable. Now if I click up here, we can see there is the table name, EndRevenueTable, which will be the same name as our query. Notice over here, this is the workbook queries window. There is the name of our query. If this window was ever closed and we needed to access it, like we want to edit it-- Data, and there's Show Queries. That opens up this task pane with all of our queries. All right, there is our table. We are going to create a PivotTable now. So I'm going to click in a single cell, Alt, N, V. And I'm going to put it on this sheet because when we update, when the source data changes, I want to be able to update on this sheet and watch both the table and our PivotTable update. I'm going to put the PivotTable right here. There is our Field List with our new columns. This is simply amazing. Product down to Rows, Region over to Columns, Revenue down to Values. Row labels is not a good name design. Report Layout, Show in Tabular. PivotTable Styles, More button. I'm going to click my style. Right-click inside the table, Number Formatting. And let's try currency with zero decimals displayed. Click OK. Now we're going to add this field here. Now remember, this date field, when we drop it down into a row, it's going to already be grouped. So I'm going to drop it above Product. And notice, it gives us Months, Dates, and Product. I'm going to drag Date. And by the way, our last PivotTable, it had Years. Here, we don't have any different year, so that field doesn't show up. I'm going to drag Date off. And notice now we have April and May. There's each product for April. There's each product for May. Now, here's why Power Query is so amazing. We have the output from Power Query and our PivotTable. Now, I want to go back over to Power Query Get and Transform. And let's scroll over to the side because I added some data. And here it is. We get an email with the latest data. So I'm going to Control-Shift-Down-Arrow. I'm going to copy this. I'm going to use the keyboard Control-Home to jump to cell A1. And now, I'm going to click in our data set. Remember, this is an Excel table, which is dynamic. So I'm going to Control-Down-Arrow. Now so far in the class, we saw how to add records to a table one at a time. But I'm simply going to paste that entire new data set from over there, Control-V. And instantly, it is incorporated into this table, Control-Down-Arrow. So now we have like 490. Now one thing, I actually Control-Up-Arrow. I did kind of fast here. But notice, when I highlighted this, I did not highlight the field names, I only highlighted the records. But now, let's go see this. This is amazing. What? They didn't update? Watch this. I'm going to come over to the Power Query table, right-click, Refresh. And now if I Control-Down-Arrow, sure enough, all the way down to 482. Control-Home to jump back to cell A1. Right-click, Refresh. And you've gotta to be kidding me. That is simply spectacular. June and July records are totally incorporated into our report. So what did Power Query do? It took Control-Home, it took this data set here that wasn't in the proper form, it put it into the proper data set. We built a Pivot Table. And when we added new records to our table, we were able to refresh and have everything update. That is why, if you have to deal with data and create reports, Data ribbon tab, Get and Transform group. Power Query is so amazing. Now Power Query can do lots of amazing things. Let's go look at a second example on PQ2. Here we have a cross-tabulated table. It looks like we have the columns Region and Product. Here's the regions already set up as row headers. And here's all the products as column headers. And inside are units. Ah, but sometimes you get a cross-tabulated data set like this, and you need to unwind it into a proper data set. Man, if we had to do that by hand, that would be hard. But Power Query can come to our rescue. There is an Unpivot button inside of Power Query. And this Unpivot button up in Power Query will interpret this cross-tabulated table perfectly. It will know how to unwind these column headers into an individual column, and then will rename it Product. It will know how to take Regions and put it into a separate column. And it will know how to put Units in a separate column. And get this, you see right here, there are four records in essence for Units. It will know to repeat Aspen four times and then put East, Midwest, South, and West. Then it will come over to the Bellen column and then it'll be four more records, one for each unit. Bellen will be repeated four times, East, Midwest, South, and West, will be in the Region column. And it will do it for each one of those columns into a complete proper data set. All right now, I'm going to click in a single cell. We're going to convert this to an Excel table. Control-T, Enter. Be sure to go up to Design and name it up here, or use Alt, J, T, A. I'm going to name this CrossTabUnits, and Enter. Now notice we converted this to an Excel table. This is not a proper data set. But we're still going to use that feature to get this into Power Query. Single cell or the entire table selected, Data, From Table. There is our cross-tabulated table. I'm going to come over here and name this ProperDataSetUnits, and Enter. There is our name. There is our step starting to emerge. Now the way the Transform, Any Column, Unpivot Columns button works is you have to highlight all of the column headers, then click Unpivot. Ah, but there's another way because there are two options-- Unpivot Columns or Unpivot Other Columns. In this case, it's more efficient to simply click the first column with our row headers and either go up and click drop-down, Unpivot Other Columns, or even faster just come over, right-click, and down here, not Unpivot, but Unpivot Other Columns. And instantly, when we click that, one, two, three columns, exactly what we want. Now we double-click, and I'm going to call this Region, and Enter. Double-click. Call this Product, and Enter. Double-click. And I'm going to call this Units, and Enter. Now I'm going to click on each column. And before we use the Home ribbon to check the data type, but Transform also has Data Type Text. Data Type, Text. Data Type, Decimal Numbers. That's not what I want. So I'm going to click the drop-down and say, Whole Number. These are units. Now we have all of our steps and our name. I'm going to go up to the Home, Close and Load it. I'm going to select Close and Load this time because this will send it directly to a new sheet. It sent it all the way to the end. So I'm going to point down to our sheet tab scroll arrows. But before you click on it, I'm going to hold Control and then click. And that jumps me all the way to the end. Double-click and I'm going to call this UnitsTable, and Enter. Right-click, Move or Copy. And I'm going to put it before Power Query 3. So it'll go before that sheet. And click OK. And just like that, we went from cross-tabulated table to a proper data set. Now why in the world would you want to do that? If you want to sort or filter or make a Pivot Table, you have to have a proper data set. Now it is not uncommon to get data already cross-tabulated. So to have a built-in feature in Excel to unpivot it into a proper data set is quite useful, indeed. Now we have one more amazing Power Query example. I'm going to click on the sheet PQ3. Now our goal for this example, we need to import multiple files that contain more than one million rows of data and combine them into a single table. Then we need to create a relationship between the newly combined table, this million row table, and a lookup table. Instead of using VLOOKUP, we're going to use this button up here for the first-time relationships. Then we need to create a Pivot Table from two tables. Now this example here is going to illustrate our last example of Power Query. And it will be our introduction to PowerPivot and the Data Model. Now the term PowerPivot and the term Data Model are terms that Microsoft made up. PowerPivot just means, because now we're going to be able to have a million rows of data and multiple tables, it's like having a supercharged Pivot Table. The Data Model is the term they use for, yes, when we have multiple tables with relationships between them, we're not calling it a proper data set, we're going to call it a Data Model. Now up here on the Data ribbon, it says Manage Data Model. We can actually click that and go look at our tables. And there's also a relationship button here. But when we go to the Data Model, we'll see our two tables and the relationship between the tables, just like we did back in our prerequisite class, Business 216, when we learned about relationships between tables in Access. There is also a PowerPivot ribbon tab. And later in the class, not at this point in the class, we'll actually learn how to make our own formulas for a Pivot Table. All right, I'm going to click back here. So PowerPivot Data Model-- supercharged Pivot Table. Data Model-- more than one table with relationships between them. And PowerPoint Data Model will hold more than one million rows of data. We'll be able to build a relationship between the two tables and then build a Pivot Table from that big data. Now before we click on Power Query to import our multiple files, I want to go look at the files. Now when you download your Excel file in the PDF, there is also a zipped folder called Video3TextFilesForImport. Download this and unzip it. Inside, we have a text file.txt for 2017 Sales Data. There's also a folder called First. If I double click and look in here, there's the 2015 and 2016 data. Now the reason that they are text files is that different systems, different databases, sometimes have a hard time communicating with each other. How do we get data from one system to another? Well, text files have been around for decades. And they are files that all systems can read. So we have 2015 and '16 data. Let's go ahead and look. I'm going to right-click and open this. And here's an example of a text file. We have one, two, three, four, different columns or fields. This is actually tab-separated data. The delimiter in this text file is a tab. If you are actually to open this file in Word, you would see tabs between each one of the fields. All three files we have have the same columns-- Date, Sales Rep, Product, and Revenue. When we tell Power Query to import the tables, it will bring them all in and try to combine them. But notice, at the top of each one of these files is going to be a field name. So when it combines them, they'll be some extra field names down below in the data set. It will be easy to get rid of them. All right, field names at the top. Records and rows. We have three text files. Each one has a different set of data for the year. So this is 2015, 2016. And if I go back a level in 2017. Now here's what Power Query will do. We will actually tell Power Query to look at this folder. And it will take anything inside, import it, transform it, and dump it into our Data Model. And the beautiful thing about this is when I tell Power Query to look at that folder, if later there's any new files in there, and we click Refresh on our Pivot Table, everything will update. That's why to start this example, I have the 2017 file outside here. Later, we'll drop it in and go back to our Pivot Table Refresh, and everything will update. It will be quite amazing. Now let's go back over to Excel. Now one last step. Let's go over to the sheet Power Query Data Model. This is actually the second table. This is going to be our lookup table because in our end report, we're going to need to summarize Revenue by Region. Now I want you to notice something. There's Tamika. If we go look at the text file right here, notice, there's lots of repeats. Now normally what we do is we put a table like this in Excel and do a VLOOKUP column. But if it's millions of rows tall, we can't do a VLOOKUP column. No problem. With PowerPivot Data Model, we just make a relationship between the Managers and the Sales Rep column and boom, we'll be able to drag this column to the row area and the Revenue to the Values area. And we have our report. All right, I'm going to go back here. It actually doesn't matter which sheet we start on because we're going to start with Power Query. It's going to combine them. And then we'll tell Power Query to put it in this behind-the-scenes Data Model. All right, you ready. Data ribbon tab, Get and Transform, New Query. I want it from a file. But look at this down at the bottom is From Folder. Right-click on that. Folder path, I browse. Desktop, I already have it here. I'm going to click the triangle to expose, and click First. By the way, if I clicked on this one, Power Query would take everything inside this folder and in that one. So I'm very careful to illustrate the updatability of this process. I'm going to click just on First, click OK. You can see the full file path there. Now when I click OK, remember, Power Query is looking at a bunch of files. So when I click OK, when I get to the Power Query Editor, it does not look like transactional data. That's because what we did was we told Power Query to get everything from the folder. So there's only two files. There's the name of the 2015 file. There's the name of the 2016 file. That column right there actually has all of the million rows of data. Now I want to come over here and name this. I'm going to highlight. And I called it ImportTextFileData, and Enter. Now we do not need any of this. Notice, this gives us information about the file. We don't need any of that. So I'm going to come over here, right-click Content, Remove Other Columns. All right, because these are text files, this is how cool it is. That little downward pointing arrow there, that's the expand button. When I click this, instantly it goes and gets all the files-- in our case, when we only have two-- and dumps them down here. That is quite amazing. Now remember, if we look over here in some of our steps it automatically promoted the headers. But remember, that's only from the first file. Not only that, but later when we have other files in that folder, there'll be other rogue sets of field names down at the bottom. Here's the trick we can use. Product doesn't have very many different items in it. So we're going to click the filter. And as we learned with filter earlier in the video, when I click the drop-down, it gives me a unique list of all of the items from that column. Now we have to be careful. This is big data. I need to click the Load More. Notice it's being polite. It says, List may be incomplete. I'm going to click Load More. Now it took a while. It went down like 500,000 rows to get to the second data set. And sure enough, that word Product is not one of the autos we sold, it's a field name. Now think about this. What happens if I uncheck this, just like when we learned filter earlier, it will filter out the whole row. So the trick is, go to the field that has the fewest number of unique items, expose the unique list, and uncheck the field name that's further down in the data set. When I click OK, automatically it filters out all of the field names in the files below. Not only that, in future refreshes when it imports more files, that filter will always be in play. Now all we have to do is click and look at our data type-- Date, Text, Text, Whole Number. We're good to go. Now here is very important-- when we want to click Close and Load, we do not want this one. If we accidentally clicked Close and Load, it would try to put it in the Excel sheet. But you'd get an error message that says, it can't fit all the data into the sheet. So here's where we go Close and Load To. Now here's our Load To dialog box. We definitely don't want a table in our sheet because we have too much data. Here's where we check Only Create a Connection. Then, we come down here and look at this-- Add this data to the Data Model. That Data Model has that behind-the-scenes columnar database that can easily handle millions of rows of data. Now I'm going to click Load. And you can actually see it working over here. It's trying to import. And finally, look at that-- 1.168 million rows. Now here is where we want to go look. I'm going to click Manage Data Model. And this brings up the user interface. And notice it's the PowerPivot user interface. I'm going to click and drag. And down at the bottom here, look, it says, how many records? We have 1.168 million records. Now I want to look at a picture of our data model. So I'm going to say Diagram View. Now we only have one table. And notice, we get a Field List. Now we want to go back over to our Excel workbook. This PowerPivot user interface that allows us to look at the Data Model is sitting on top of our Excel spreadsheet. So I'm going to go back to our Excel spreadsheet. Now remember, we had a second table we needed to add. So I'm going to go to PowerPivot Data Model. We need to convert it to a table. Just like Power Query, we cannot put a table into PowerPivot unless it's an Excel table. Control-T, Enter. Be sure to go up to Design and name it, Alt, J, T, A. And I'm going to name this ManagerTable, and Enter. Now here's where we're going to go to PowerPivot ribbon tab. And here in the Table group, I'm allowed to take data straight from Excel, as long as it's in a table, and say, Add To a Data Model. And just like that, we have our two tables. If we go back to Data View, now we can see two tables. That little link right there means it's actually linked to Excel. If we were to change anything in the Excel spreadsheet, it would update. Now I'm going to go to Diagram View. And I want to notice something. In our ManagerTable, the managers are called Managers. That's the name of the field. But over here, it's called Sales Rep. Now no problem. We could build a relationship. But I want to show you an important trick. Let's go back over to Excel. And when we imported our text files, we actually wanted to rename the column. I like to be consistent here. So we can go to our workbook queries. Again, if that's not open, you go to Data. And then Show Queries. But I'm just going to right-click, Edit. And here's our steps. We are totally allowed to come back here and edit and do what we want. I'm going to come to Sales Rep, double-click. And I'm going to call it Managers, and Enter. Now I'm going to just click the Close and Load button. And see, it's loading over here. Now I'm going to go back to the PowerPivot window. You can either click this or Alt-Tab because I still have it open. And look at that-- the field name updated. Now we want to create a relationship because I want to be able to pull Region from this field into a Pivot Table and Revenue from this one. So we need a relationship. I'm going to click on Managers and click and drag over to the other Managers, and just like that. Now in Access when we studied relationships, there was an infinity sign to mean many and a 1 to mean one. This is a one-to-many relationship. That means this ManagerTable is in essence a lookup table. If this is the lookup table, that means in the first column there is only one of each manager. Whereas over here, there can be many repeats. In fact, let's go look at the two data tables. I'll go to Data View. Here's ManagerTable. There is a unique list in the first column. If we were doing VLOOKUP, we would find a match and then return this. So for example, Adelia, USA East, if we look over at our millions of rows, there's Adelia. If we were doing VLOOKUP, we just add an extra column, look Adelia up, and get her region. But we don't need that extra column if, back to Diagram View, we have a relationship. All I have to do is drag Region down to the Row area, Revenue down to the Values area in the Pivot Table, and we're done. Now we can create a Pivot Table directly here from PowerPivot. This is exciting, this is going to be a Pivot Table for millions of rows with the relationship. I'm going to click on the Pivot Table icon. I want it on the new worksheet. It'll actually jump us back over to Excel. And there it is. It's right next to PowerPivot Data Model. I'm going to double-click and call this RegionalProductReport. And now look at this Field List. Now we can tell that the tables with the dark line at the top, those ones have been added to the Data Model. These other ones are all of the other tables in this Excel workbook. But they're not in the Data Model. Only the tables with the dark line at the top are in the Data Model. Now I want to right-click and say Show in Active tab. And so now over in the Active tab, we have ImportTextFileData. Come back over to All. Right-click, click, Show in Active tab. And now look at this. This a pivot table with multiple tables. And we are allowed to-- watch this-- drag Region down to Rows. Instantly, we get a unique list from our ManagerTable. Now I can drag Revenue from Values. And just like that, from over a million rows of data, I have built a Pivot Table from two separate tables. Now I want to get rid of row labels. I'm going to go up to Design, Report Layout, Show in Tabular. I'm going to go up to the More Styles and click on our styles. I want to go over to Analyze. And in the Filter group, I want to say Insert a Slicer. And I would like Product. Click OK. There is our Product. And now watch this. I can slice and dice on a million rows of data. Come over to the Pivot Table. We definitely want to right-click Number Formatting. I'm going to say Currency, Display zero decimals. All right, so that was just our simple example of PowerPivot. Later, we'll learn how to actually build formulas for our millions of rows of data and build relationships between multiple tables. But for a supercharged Pivot Table, that is pretty cool. Now let's go back to our folder. We're going to drop 2017 data into this folder here. I'm going to copy this. And I'm going to double-click here, Control-V. Now notice, PowerPivot is looking at that folder. So when I come back over to Excel, on this sheet, I am going to right-click, Refresh. And look at that, you can see the little-- and over here, you can see Power Query updating. And now, the Pivot Table has updated. That is absolutely amazing. So for all of our regions for Toyota, I can slice and dice on Chevy, Honda. I can clear the filter. Wow. That was an epic video. In this video, we saw how to build a Pivot Table using PowerPivot's Data Model where we had millions of rows of records and a relationship between a table. You look over here. That was the initial Power Query data import. And after we refreshed, we had 1.6 million records. We definitely saw Power Query to import and transform multiple text files. We saw Power Query to unpivot a cross-tabulated table. Remember, we had a data set and we needed to break this apart. But the most amazing thing here was we added records to the bottom and our Pivot Table totally updated. We learned Pivot Table basics. We used this data set. We saw how to create a regional report and then generate a bunch of individual reports using Show Report Filter pages. We saw back here on product and month report how to build multiple Pivot Tables with different calculations, aggregate calculations, and even our Show Values As a Percentage of Column Total. And we had slicers, two of them, that control both Pivot Tables. We saw how to create a cross-tabulated Pivot Table where we had seven conditions or criteria. We talked all about filter. And we started off talking about sort. Hey, next video, we'll actually be talking more about calculations with conditions and criteria, but we'll do it with formulas. All right, we'll see you next video.

Channel: ExcelIsFun

Views: 353,982

Rating: 4.9555449 out of 5

Keywords: Highline College, Excel 2016, Mike Girvin, Busn 218, Spreadsheet Construction, Highline Excel 2016 Class, excelisfun, Learn Excel, Free Excel Class, Intermediate Excel, Advanced Excel, Basic To Advanced Excel, Excel Fundamentals, Data Analysis, Business Intelligence, Sort, Filter, PivotTable, Power Query, Power Pivot, Excel Data Model, Power Pivot Data Model, OR Logical Test, OR Criteria, AND Logical Test, AND Criteria, Import Big Data, Import Text Files, Relationships

Id: Jzt-I4mt8sQ

Length: 103min 5sec (6185 seconds)

Published: Sun Apr 17 2016