1
00:00:00,210 --> 00:00:05,610
In this video, I'll briefly cover a few potential examples for how you can use relationships with data

2
00:00:05,610 --> 00:00:09,900
sets and power pivot to quickly garner some useful tidbits of information.

3
00:00:10,410 --> 00:00:13,680
I have a brief overview as to what kind of property is a problem.

4
00:00:13,680 --> 00:00:19,740
Needs to make it friendly for power pivot, plus a pair of scenarios that I'll review and how I generate

5
00:00:19,740 --> 00:00:20,730
a solution for them.

6
00:00:21,060 --> 00:00:22,440
Let's start with a checklist.

7
00:00:22,660 --> 00:00:27,930
Does your problem involve multiple data sets that need to be linked together and some type of aggregation

8
00:00:27,930 --> 00:00:30,210
of results for informational purposes?

9
00:00:30,810 --> 00:00:32,190
Is that the entire list?

10
00:00:32,910 --> 00:00:38,490
Yes, it's a very short checklist, but encompasses a huge variety of different challenges that you

11
00:00:38,490 --> 00:00:42,030
might encounter with your data in our next chapters.

12
00:00:42,030 --> 00:00:47,670
When we get into more complex manipulation of the data model and pick up some DACs will add some additional

13
00:00:47,670 --> 00:00:52,800
special scenarios to this list that are somewhat related to what we've done, but require additional

14
00:00:52,800 --> 00:00:55,260
tools to solve our course.

15
00:00:55,260 --> 00:01:01,830
Example so far looked at a summary of costs and sales by store and date, and it's very obvious, yet

16
00:01:01,830 --> 00:01:07,380
straightforward example for how to leverage these relationships to link and aggregate information.

17
00:01:07,740 --> 00:01:10,650
But what are some less obvious solutions?

18
00:01:11,980 --> 00:01:18,590
For example, here's a series of invitations that sent out and a list of RSVP that have been received.

19
00:01:19,080 --> 00:01:24,390
We have a common field point them across the name field and we'd really like to know how many yeses,

20
00:01:24,600 --> 00:01:29,520
how many no's and how many no responses we have as an additional benefit.

21
00:01:29,670 --> 00:01:33,570
It might be nice to drill down and figure out who has responded in each way.

22
00:01:34,290 --> 00:01:37,290
And I may have given a secret away by saying drill down.

23
00:01:37,620 --> 00:01:38,880
So let's go build this thing.

24
00:01:39,720 --> 00:01:44,040
Conveniently, our data is already in tables so I can go straight to the relationship manager.

25
00:01:44,490 --> 00:01:50,040
While I do not have a standard dimensional table, I can assume that both my invitations and my responses

26
00:01:50,040 --> 00:01:51,150
are uniquely named.

27
00:01:51,720 --> 00:01:56,850
The accounts that I want to determine will be based upon my invitations, as I will want information

28
00:01:56,850 --> 00:01:58,470
about who has responded to what.

29
00:01:59,370 --> 00:02:05,760
This means that I'll specify my invitations in the top table with names column and then responses as

30
00:02:05,760 --> 00:02:08,790
the related lookup table with its name column as well.

31
00:02:10,500 --> 00:02:13,530
Once this relationship is complete, let's go build a pivot table.

32
00:02:14,640 --> 00:02:20,850
I'm going to summarize a lot of information in one place by dragging my yes no from responses to the

33
00:02:20,850 --> 00:02:25,620
row section and my plus one from my responses to my column section.

34
00:02:26,160 --> 00:02:32,280
Then I'm going to take name from my invitations and place it in the value section where it will automatically

35
00:02:32,280 --> 00:02:33,720
assume that I want to count them.

36
00:02:35,340 --> 00:02:38,720
Now, my pivot table looks like this under no plank.

37
00:02:39,150 --> 00:02:45,330
I have 83 entries, those 83 entries have a blank plus one since the plus one column is empty.

38
00:02:45,330 --> 00:02:49,140
If they aren't coming in the next row, I have one hundred and eleven.

39
00:02:49,140 --> 00:02:49,500
Yes.

40
00:02:49,500 --> 00:02:54,150
Responses who are not bringing a plus one and then one hundred and thirty six.

41
00:02:54,150 --> 00:02:54,360
Yes.

42
00:02:54,360 --> 00:02:57,330
Responses from people who are bringing a plus one.

43
00:02:57,780 --> 00:03:02,780
Overall, we can quickly see that two hundred and forty seven responses say that yes, they will attend.

44
00:03:03,960 --> 00:03:09,540
Finally, the fourth row shows blanks for both entries with a total of one hundred and three.

45
00:03:09,870 --> 00:03:13,320
These one hundred and three people are those who have not yet responded.

46
00:03:13,560 --> 00:03:18,210
In just a few seconds, we've created a table that tracks the current tally of responses.

47
00:03:18,450 --> 00:03:22,260
And better yet, do you want to be able to figure out who hasn't responded?

48
00:03:23,820 --> 00:03:29,430
Well, double click on that one or three and voila, you have a list of people and addresses who haven't

49
00:03:29,430 --> 00:03:32,460
responded yet, so you can send out your next mass mailer.

50
00:03:32,850 --> 00:03:38,910
That's one quick example for using relationships between data to quickly gain some insights and utilize

51
00:03:38,910 --> 00:03:44,310
it with a very straightforward and classic relationship, much like the one we used for our sales data.

52
00:03:45,540 --> 00:03:49,200
Sometimes, however, a relationship might not be quite so clear.

53
00:03:49,590 --> 00:03:53,760
You may be asking yourself, why are these colors randomly appearing on my screen?

54
00:03:54,120 --> 00:03:57,740
You might also ask, why is there a number associated with each one of them?

55
00:03:58,410 --> 00:04:00,480
Well, that's the premise of the next example.

56
00:04:00,810 --> 00:04:02,040
The order seems random.

57
00:04:02,040 --> 00:04:05,550
However, I'm not entirely sure that it is wink wink.

58
00:04:06,420 --> 00:04:11,020
In fact, I think the next color in the wheel depends upon the previous color in the wheel.

59
00:04:11,700 --> 00:04:15,810
It may not seem obvious, but this is another opportunity to use relationships.

60
00:04:16,470 --> 00:04:21,990
The traditional Excel approach to this would be to write a formula or simply copy and paste everything

61
00:04:21,990 --> 00:04:24,470
off by one row in either direction.

62
00:04:24,870 --> 00:04:30,990
However, that both violates the premise of this course, and b really isn't the best way to do this

63
00:04:30,990 --> 00:04:36,150
type of computation, especially if you might need to test different levels of offset, such as one

64
00:04:36,160 --> 00:04:38,100
row, two rows, three rows or more.

65
00:04:40,200 --> 00:04:45,090
Now, to make a truly flexible solution here, you need both power query and the power pivot.

66
00:04:45,330 --> 00:04:47,580
Here's what my PowerCore Apeace looks like.

67
00:04:48,120 --> 00:04:53,340
You could just as easily do this by copying and pasting the existing table, adding one to every row,

68
00:04:53,430 --> 00:04:57,230
deleting the last row and adding the resulting table to power pivot as well.

69
00:04:57,630 --> 00:05:03,570
In any case, our data model now has two versions of nearly the same table, except one has the turn

70
00:05:03,570 --> 00:05:09,030
in which the color curtain and then the other table has the turn that we think that the color impacts.

71
00:05:09,780 --> 00:05:14,550
So row one in our first table is one in red, which is what actually happened in turn one.

72
00:05:15,120 --> 00:05:20,670
Row one in our second table is two and red, which says that we think the color red somehow impacts

73
00:05:20,670 --> 00:05:22,380
what the color in turn to is.

74
00:05:23,550 --> 00:05:29,100
This gives us a relationship we can utilize on our turn and next turn column, along with two different

75
00:05:29,100 --> 00:05:35,640
color fields to evaluate permutations in my relationships editor, I'll create a relationship between

76
00:05:35,640 --> 00:05:41,820
our color by next turn with its next turn column and the color by turn and its turn column pressing.

77
00:05:41,820 --> 00:05:46,830
OK, I'll now insert a pivot table that uses our data model and note that we have two tables.

78
00:05:48,440 --> 00:05:54,080
I'll put the starting color for our next turntable in the row section, I'll put our color in the columns

79
00:05:54,080 --> 00:05:58,930
section and I'll drag our next turn to the value section and change it to account.

80
00:05:59,680 --> 00:06:05,420
Upon doing this, I now have a two dimensional table that shows me where the color started on the left

81
00:06:05,660 --> 00:06:12,110
and where it ended up in at the next turn along the top and then in the each cell it will tell you that

82
00:06:12,110 --> 00:06:14,060
number of times that this occurred.

83
00:06:14,720 --> 00:06:19,940
So in this scenario, we can see that when it lands on yellow, it tends to stay on yellow for a long

84
00:06:19,940 --> 00:06:24,020
time as yellow is the starting color and the ending color 80 times.

85
00:06:24,770 --> 00:06:26,530
And then it only ever goes to green.

86
00:06:26,540 --> 00:06:33,320
Otherwise, similarly, blue never follows blue and green and red generate a fairly random response.

87
00:06:35,470 --> 00:06:40,990
Based on this and the fact that I built the random algorithm that generated these colors, this seems

88
00:06:40,990 --> 00:06:46,690
to provide some meaningful insight into our color patterns, perhaps it could be used to predict colors

89
00:06:46,690 --> 00:06:50,710
into the future if there were some need to do that anyhow.

90
00:06:50,710 --> 00:06:52,870
With that will conclude this chapter.

91
00:06:53,170 --> 00:06:58,690
I introduced you to Relationships and Excels Power Pivot, along with a brief overview of some simple

92
00:06:58,690 --> 00:06:59,440
data theory.

93
00:07:00,190 --> 00:07:05,590
This lesson showed you two alternative uses for the pivot table relationships outside of the simple

94
00:07:05,590 --> 00:07:08,260
aggregation example I used in the prior lessons.

95
00:07:08,800 --> 00:07:14,440
In our next chapter, I'll introduce you to some more detailed aspects of exile's data model, along

96
00:07:14,440 --> 00:07:19,900
with how to write pivot table formulas using DACS and will really begin to take advantage of power pivot's

97
00:07:19,900 --> 00:07:20,800
infrastructure.
