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.