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.
