1 00:00:00,390 --> 00:00:04,960 Before we start the course, why don't I demonstrate how to apply power, pivot to some data. 2 00:00:05,700 --> 00:00:10,740 This video will have some dense content, but don't worry, I will guide you through every step that's 3 00:00:10,740 --> 00:00:13,410 covered in this video at different points in the course. 4 00:00:13,860 --> 00:00:16,890 If you work with Excel, you're probably familiar with the old. 5 00:00:17,130 --> 00:00:21,120 I need a report that summarizes these three different data pieces request. 6 00:00:21,630 --> 00:00:27,450 And naturally, those three data sets just have to be structured a little bit differently, enough so 7 00:00:27,450 --> 00:00:31,000 that you need to use some FS to summarize the data across each source. 8 00:00:31,920 --> 00:00:33,480 So what have we been asked for? 9 00:00:35,020 --> 00:00:39,020 Well, our boss has asked for a summary of net profit by store, location by month. 10 00:00:39,460 --> 00:00:40,510 Sounds easy, right? 11 00:00:41,170 --> 00:00:46,300 I mean, this isn't hard, but it will take a lot of time, plus a really solid idea as to what you 12 00:00:46,300 --> 00:00:48,080 want to do before you actually do it. 13 00:00:48,730 --> 00:00:52,540 We need to split the month and year off of our dates for at least two of our tables. 14 00:00:52,870 --> 00:00:57,330 And then we'll need to create our summary table layout with this summary table. 15 00:00:57,430 --> 00:01:03,310 We're finally ready to write some really, really long formulas using some s, which is a fairly advanced 16 00:01:03,310 --> 00:01:04,270 function itself. 17 00:01:05,320 --> 00:01:08,630 Now, our data isn't that complex, so isn't there a better way? 18 00:01:09,440 --> 00:01:15,670 Well, as the course name implies, I will show you a better and easier way with power pivot with no 19 00:01:15,670 --> 00:01:18,250 need to understand these pesky Sonmez functions. 20 00:01:19,240 --> 00:01:20,450 So how can we do this? 21 00:01:21,190 --> 00:01:23,770 Step one is to structure our data and data tables. 22 00:01:23,890 --> 00:01:29,890 As I showed in the review session, this can be done by selecting the data processing control t pressing 23 00:01:29,890 --> 00:01:30,790 OK in the pop up. 24 00:01:30,790 --> 00:01:35,400 That appears once all of our data is in tables and don't forget to name them. 25 00:01:35,830 --> 00:01:37,510 I need some dimensional tables. 26 00:01:38,110 --> 00:01:43,300 The first dimensional table I'll create is the dates table with the list of appropriate dates plus fields 27 00:01:43,300 --> 00:01:45,220 describing the year, month and day. 28 00:01:45,550 --> 00:01:47,500 And of course I'll make it a data table. 29 00:01:48,040 --> 00:01:52,210 I'll explain what a dimensional table is early in the course and discuss why we do this. 30 00:01:53,080 --> 00:01:56,410 The other dimensional table will create will list are unique store names. 31 00:01:57,500 --> 00:02:02,300 To do this are simply paste to store answer from one of my other sheets into a blank workbook, go to 32 00:02:02,300 --> 00:02:05,720 the data tab with my ribbon and use to remove duplicates command. 33 00:02:06,780 --> 00:02:12,510 I'll make this a table and call it stores, and now I'm ready to find my power, private relationships. 34 00:02:13,640 --> 00:02:19,140 On the data tab, there is a button called relationships that I will click on to launch a new window. 35 00:02:19,790 --> 00:02:24,860 I'm going to go quickly in this demonstration, but will guide you through each of these steps and explain 36 00:02:24,860 --> 00:02:26,710 the concepts in full detail later. 37 00:02:27,590 --> 00:02:31,550 I needed to find six new relationships, two for each of our tables. 38 00:02:32,150 --> 00:02:37,610 For my first relationship, I'm going to connect the labor table with its month column to the calendar 39 00:02:37,610 --> 00:02:38,870 tables dates column. 40 00:02:39,830 --> 00:02:45,410 This tells Power Pivot that the month column from the labor table contains the same values as the calendar 41 00:02:45,410 --> 00:02:46,560 tables dates column. 42 00:02:47,550 --> 00:02:52,790 Further, it says that our calendar table can be used to look up specific information from the labor 43 00:02:52,790 --> 00:02:54,470 table to you. 44 00:02:54,480 --> 00:02:56,750 This might seem obvious, of course. 45 00:02:56,750 --> 00:03:02,720 The date in the calendar table and the date in the labor table are the same, but we're laying out the 46 00:03:02,720 --> 00:03:06,060 specific details in a way that power pivot can connect them together. 47 00:03:06,770 --> 00:03:11,720 I'll do this for both of the other tables with their date field and then I'm going to repeat the process 48 00:03:11,720 --> 00:03:16,370 for all three tables and connect them via the stores table and it's Starfield. 49 00:03:17,720 --> 00:03:23,870 These relationships all tell power how to connect the three tables together through two dimensional 50 00:03:23,870 --> 00:03:24,410 tables. 51 00:03:25,490 --> 00:03:31,130 Once we've defined these relationships, Power Pipit will work a bunch of magic in the background and 52 00:03:31,130 --> 00:03:33,650 all we need is a pivot table to take advantage of it. 53 00:03:34,550 --> 00:03:39,800 So on a clean sheet, I'm going to insert a new pivot table that uses this Workbox data model as the 54 00:03:39,800 --> 00:03:40,510 data source. 55 00:03:41,540 --> 00:03:47,870 When I do this, I get my usual pivot table fields window with a slight twist Apple store from the stores 56 00:03:47,870 --> 00:03:54,800 table and added a zero entry drop a month from the calendar table and add it to in the value section. 57 00:03:54,800 --> 00:03:59,810 I'll add the sales from the sales table, the purchase orders from the purchase orders table and the 58 00:03:59,810 --> 00:04:03,190 employee wages from the employee table to the. 59 00:04:03,590 --> 00:04:09,110 We now have a pivot table with all of the information from our complicated summit summary displayed 60 00:04:09,110 --> 00:04:10,510 at the beginning of this video. 61 00:04:11,060 --> 00:04:16,110 We created this in half the time with half the trouble, with twice as many features. 62 00:04:16,730 --> 00:04:19,670 So now are you ready and eager to learn how to use power? 63 00:04:19,670 --> 00:04:20,000 Pivot.