Key Concepts for Relating Data

Quick reference

• 00:04 The beginning of every PowerBI journey always starts with the same things.
• 00:08 It's getting and transforming data.
• 00:11 Now, we've already done that particular process twice, first bringing in our sales
• 00:15 transactions file, and the second time bringing in our budgets file.
• 00:19 The second step of the journey is where we actually go to relate those tables
• 00:22 together so that we can use them in visuals.
• 00:25 But before we can do that,
• 00:26 we should really talk about relationship cardinality.
• 00:29 How do we actually build relationships, and
• 00:31 what type of relationships are actually supported inside PowerBI?
• 00:35 The first type of relationship that's actually supported in PowerBI
• 00:38 is what we call the many-to-one relationship.
• 00:42 This is where there's two separate tables with a column that joins the two together.
• 00:47 One one side of that join, there's a column of unique values,
• 00:50 that's the one side of the relationship.
• 00:52 On the other side of the join is a column that has many,
• 00:55 many values in it where there can be duplicate values.
• 00:58 So that's why we call it many-to-one.
• 01:01 The second type of join that's available inside PoweBI is the exact opposite.
• 01:06 It's called the One-to-Many join.
• 01:09 Essentially, this is the exact same join,
• 01:11 it's just that we're looking at one column first instead of the other column first.
• 01:16 So really, that's the same thing.
• 01:19 The next type of join that's supported is called the one-to-one join.
• 01:24 This is a very special join where the columns that relate the two tables
• 01:28 both have unique values in them and unique values only.
• 01:32 So it's a very special join that's only used in certain cases, but
• 01:36 it is definitely one that is supported by PowerBI.
• 01:40 Now, there's one more join type that occurs in the real world, and
• 01:43 it's called the the many-to-many join.
• 01:46 This particular join type here is not supported.
• 01:48 And basically, the many-to-many is a join type where the columns that are trying to
• 01:53 join each table have multiple repeating values in them on both sides.
• 01:57 That causes some real challenges, and that's why the Microsoft team has decided
• 02:01 that they're not going to support this join type at this particular time.
• 02:05 So let's look at each join type.
• 02:08 We'll start with the many-to-one relationship or
• 02:10 the one-to-many relationship.
• 02:12 And this is what it actually looks like inside PowerBI.
• 02:14 We have two particular tables when we look at them in the relationship view.
• 02:18 These things actually have a line between the two of them.
• 02:21 And you'll notice that one side has a one on it.
• 02:23 The other side has a star on it.
• 02:25 And there's a directional arrow in the middle.
• 02:28 In this particular case, we have a link between the location name
• 02:31 field in the locations table, that's the one side, and
• 02:33 we have it linked to the location field on the budgets table.
• 02:37 That's the many side.
• 02:39 So what does this actually mean?
• 02:41 Well, when you take a look at the properties of the join,
• 02:43 you can see that it looks like this.
• 02:45 On the budget's table in location we have the same value, ethical development,
• 02:49 that's repeating many, many, many times.
• 02:51 And you can see in the date column there's lots of unique entries going on in that
• 02:54 particular area.
• 02:55 The big key is that we see the same value repeating many times within
• 02:59 the same column.
• 03:00 On the locations table, though, our location name only has one instance.
• 03:06 There's only three records in this particular table.
• 03:08 One of them for ethical development, another one for the squintz, and
• 03:11 another one for tax evader.
• 03:12 This is a classic definition of what we call a many-to-one relationship.
• 03:16 We see the same value in the budgets table that appears many times.
• 03:21 And duplicates are okay.
• 03:23 But in the locations table,
• 03:24 the one side we have unique values only, no duplicates whatsoever.
• 03:29 Now, a big recommendation I have for
• 03:31 you is when you create a join like this, the first thing that
• 03:34 you should do is should hide the location field in this particular case.
• 03:38 The field that's linked from the many side.
• 03:41 This is actually going to allow you to solve a particular issue before it ever
• 03:45 happens, which can cause a lot of problems.
• 03:47 And we'll do a little bit of a demo on this when we actually apply these steps to
• 03:51 our model.
• 03:53 The other thing that I would do is that,
• 03:55 occasionally you can see a cross filter show up.
• 03:57 This is where the arrow in the center of that join points in both directions.
• 04:01 This is a supported feature of PowerBI, but it's very confusing to a lot of users.
• 04:05 It used to be default, and Microsoft actually removed it as default, so
• 04:08 that tells you a lot right there.
• 04:10 So I would try and
• 04:11 always say let's have a single arrow that points from the one-to-many side.
• 04:15 Never have a double-headed arrow on a one-to-many joint.
• 04:21 The next join type that we want to explore in a little more detail is the one-to-one
• 04:25 relationship.
• 04:25 This one looks like this in PowerBI.
• 04:28 Notice in this case that the arrows are pointing both directions on the linkage
• 04:32 between the tables.
• 04:34 And you can also see that it is a one-to-one relationship based on the fact
• 04:37 that there is a one beside each table.
• 04:40 What does this actually mean?
• 04:41 Well, when you go and take a look at the actual table joins themselves,
• 04:45 you'll notice in the Hour column that we have a unique set of numbers.
• 04:49 0, 1, and 2.
• 04:50 There's no repeats there whatsoever.
• 04:52 Now, in the ShiftSort column, we do have repeats here.
• 04:55 But that's actually not necessary, they could be unique, as well,
• 04:57 it doesn't matter.
• 04:58 Because the key is that the Hour column is the one that's being joined to
• 05:02 the other table.
• 05:03 Notice also in the POSChitHour column, again, we have unique values.
• 05:08 So in both cases here, we have unique values only
• 05:12 between the two columns that are actually being joined in between these tables.
• 05:17 Now, there are a couple of interesting things about this.
• 05:19 Key points here is that, number one,
• 05:21 this particular join type will prevent duplicates.
• 05:24 If you try and bring a duplicate in, it's going to actually fail and
• 05:27 tell you that there is an error in your model.
• 05:29 So it won't let you update your data.
• 05:31 This join type must use the broke both cross filter.
• 05:35 That's where the double-headed arrow was in the center.
• 05:37 It cannot use an arrow that only goes in one direction, so
• 05:39 that's kind of a key point here.
• 05:41 The other thing is kind of interesting about this is that most of the time you
• 05:45 can actually rebuild this particular relationship as a many-to-one or
• 05:48 a one-to-many.
• 05:50 It's not a big deal if it always needs to be a one-to-one, but
• 05:52 sometime we can accidentally get this join when we have a one-to-one set up when we
• 05:56 originally created it, even though it needs to be a one-to-many.
• 05:59 So it's something to keep in mind.

Lesson notes are only available for subscribers.