1 00:00:00,360 --> 00:00:04,830 This screen should look familiar with the pivot table and the empty relationships box. 2 00:00:05,310 --> 00:00:10,440 It's where we were about halfway through our last lesson when we introduced the relationship manager 3 00:00:11,070 --> 00:00:16,380 in this lesson, instead of manually creating all of our relationships, we're going to auto detect 4 00:00:16,380 --> 00:00:18,000 the relationships in our data. 5 00:00:18,570 --> 00:00:23,910 Before we actually click on Auto Detect, let's talk about what Excel looks for when it's creating the 6 00:00:23,910 --> 00:00:24,750 relationships. 7 00:00:25,740 --> 00:00:27,390 Excel looks for three things. 8 00:00:27,750 --> 00:00:30,210 One is matching field names between tables. 9 00:00:30,540 --> 00:00:35,620 So let's create a new relationship to demonstrate this in the new relationship window. 10 00:00:35,790 --> 00:00:41,580 I'm going to select my sales table on the top and the store name for the column in my related table. 11 00:00:41,580 --> 00:00:43,590 I'm going to select stores now. 12 00:00:43,590 --> 00:00:47,850 Notice that the related column automatically populates itself with the starfield. 13 00:00:48,420 --> 00:00:53,580 Since we picked the two tables and had one of the column field selected, Excel was able to choose the 14 00:00:53,580 --> 00:00:54,420 correct field. 15 00:00:55,530 --> 00:01:00,990 Let's cancel out of this and discuss the other component that Excel considers just having two columns 16 00:01:00,990 --> 00:01:05,280 that match up isn't sufficient for Excel to create a relationship between the two tables. 17 00:01:05,820 --> 00:01:11,100 Our sales and purchase orders tables both have a store field, but they shouldn't get matched up since 18 00:01:11,100 --> 00:01:13,070 they have multiple duplicates between them. 19 00:01:14,130 --> 00:01:19,140 The second check is for one of the tables to have a unique set of entries in the matching field. 20 00:01:19,650 --> 00:01:24,810 So with our sales and purchase orders, while they both have store columns, neither store column is 21 00:01:24,810 --> 00:01:25,710 a unique list. 22 00:01:26,490 --> 00:01:32,970 The third and final item that Excel considers is to look at existing pivot tables in the workbook to 23 00:01:32,970 --> 00:01:36,090 determine where relationships are expected to exist. 24 00:01:36,840 --> 00:01:41,850 This is particularly important for certain types of tables that we want cross-trained in the future. 25 00:01:42,600 --> 00:01:46,440 With all of that said, let's go look at our pivot table right now. 26 00:01:46,440 --> 00:01:51,710 I only have sales and store showing and we know that the field names match up between those two. 27 00:01:52,140 --> 00:01:57,510 So if I click on Auto Detect, a window will pop up and it looks for relationships and identifies and 28 00:01:57,510 --> 00:01:59,610 creates one new relationship. 29 00:02:00,480 --> 00:02:05,370 I can press close and we can see that our relationship has been created and it's active. 30 00:02:06,600 --> 00:02:12,540 Let's go structure our data so that we can protect all of the relationships we need on our calendar 31 00:02:12,540 --> 00:02:12,980 table. 32 00:02:13,200 --> 00:02:18,000 This means I need to change the dates field to date on our labor table. 33 00:02:18,210 --> 00:02:22,080 That means I need to change the month field to date and the store name to store. 34 00:02:23,160 --> 00:02:28,530 Once we've done that, I need to go back to our pivot table and add month to the table from the calendar 35 00:02:28,530 --> 00:02:33,540 table and then I need to add purchase order and employee wages to the value section. 36 00:02:34,720 --> 00:02:40,990 Now we're almost ready to order, detect all of our relationships if I run the detection now, Excel 37 00:02:40,990 --> 00:02:46,690 will only pick up three out of our six relationships because it will miss the date relationships. 38 00:02:47,290 --> 00:02:52,270 To catch these, we need to venture into the data model and do one quick thing. 39 00:02:52,540 --> 00:02:57,610 So I'm going to click on the manage data model button on the data tab of the ribbon and go to the calendar 40 00:02:57,610 --> 00:02:59,110 tab here. 41 00:02:59,110 --> 00:03:03,580 I'm going to click on the design tab and choose the mark as date table command. 42 00:03:04,390 --> 00:03:07,690 I don't know why this has to be done, but it just does. 43 00:03:08,830 --> 00:03:14,920 Once that's complete, I can close the data model, open our relationships and click on Auto Detect 44 00:03:14,920 --> 00:03:17,590 to find five more new relationships. 45 00:03:18,790 --> 00:03:24,490 This relationship detection feature can make setting up simple data sets like this one fast and easy 46 00:03:24,670 --> 00:03:27,310 with just a little bit of data preprocessing. 47 00:03:27,760 --> 00:03:32,920 You get your data, you throw it in Excel, you add it to the data model, create the pivot table you 48 00:03:32,920 --> 00:03:36,100 want, and then hit auto detect to get to relationships. 49 00:03:36,610 --> 00:03:38,140 It's really pretty impressive. 50 00:03:38,380 --> 00:03:44,650 However, I vastly prefer to define the relationships myself and I recommend that you not rely too heavily 51 00:03:44,650 --> 00:03:46,030 on the auto detect feature. 52 00:03:46,690 --> 00:03:50,470 You'll find that power pivot is much friendlier and much more powerful. 53 00:03:50,680 --> 00:03:55,420 If you're just a little bit familiar with your data structure, the more familiar, the better. 54 00:03:56,560 --> 00:04:00,430 This lesson nearly closes out our chapter covering the very basics of power. 55 00:04:00,430 --> 00:04:06,190 Pivot in the next lesson, I'll offer some tips and suggestions for how to use power, pivot and the 56 00:04:06,190 --> 00:04:12,520 relationships to handle a few scenarios with an excel excluding naturally, this particular example.