Solving Many-To-Many Joins with Composite Keys - Theory

Quick reference

• 00:04 The reality is that not every join you're gonna encounter in the real world is a one
• 00:09 to many.
• 00:10 You're going to encounter a many to many issue at some point that you need to deal
• 00:14 with and there's a couple of different ways.
• 00:16 This specific scenario we can solve using composite keys.
• 00:21 So what's a composite key all about?
• 00:23 Well, if you look at the Transactions table here, you'll notice that the same
• 00:26 account number is showing up many times down the account column.
• 00:30 You'll also notice in the Departments table that there's many repeating
• 00:33 department numbers, as well.
• 00:36 Likewise the Budgets table has the exact same problem.
• 00:39 And when you look at the Accounts table up top,
• 00:42 you can see the same account number continues on, but
• 00:45 you've only got an individual instance of the department number.
• 00:49 Now, the reality is though, that if you were to blow this table out even longer,
• 00:54 there would be another account, 73020.
• 00:56 And that would have a department number for the Pro Shop Wages of 110,
• 01:02 73020 150, 73020 250.
• 01:06 So you actually have multiple repeating department numbers as well.
• 01:10 But what is important to realize about this particular column is when
• 01:15 you actually go through and join these two things together,
• 01:19 73010-110, that becomes unique 73010-150.
• 01:24 That's what we call a composite key, where two fields need to be joined
• 01:28 together in order to come up with something unique.
• 01:32 That will happen on the Accounts table, and yet
• 01:35 when you actually look down at the Transaction and Budgets table
• 01:39 the 73010-110 combination is obviously going to exist multiple times.
• 01:45 So this creates a one to many relationship between these tables
• 01:50 once we can actually create the composite key.
• 01:53 When we've done that, then we can link them and
• 01:55 everything will work quite nicely.
• 01:58 So how do you go about actually doing this?
• 02:00 That's the question.
• 02:01 Well, for that we actually reach back to Power Query to merge columns.
• 02:07 And that's another beautiful thing about using Power Query in the first place,
• 02:10 is that should something not be right we can always go back and adjust it.
• 02:14 That's not necessarily the case if you connect Power Pivot
• 02:18 directly to a table without involving Power Query first.
• 02:22 So how do you do this anyway?
• 02:23 Well, you're gonna start by editing your queries.
• 02:27 Once you do that, you would go in and you would select the first column,
• 02:30 hold down CTRL, and select the second column of keys you actually wish to join.
• 02:35 The order here is critically important.
• 02:37 If you select department first and then account and then you actually make your
• 02:41 merge, you're gonna get something that goes in department account order.
• 02:44 If you then go to the other side of the relationship and choose account first and
• 02:49 department second, you'd get an account department order.
• 02:53 Department account and account department are completely separate and different, and
• 02:56 therefore you wouldn't be able to link them together.
• 02:58 So you want to make sure that that's consistent.
• 03:01 Once you have these columns selected you're going to go to
• 03:04 one of either two places.
• 03:05 There's a Transform tab and an Add Column tab inside Power Query, and
• 03:10 they both hold the Merge Columns command.
• 03:13 The difference is Add column will preserve the original account column, and
• 03:17 the original department column, and
• 03:19 add you a new column that merges these guys together.
• 03:21 Whereas the transform one will drop the original two columns and
• 03:25 just give you the result of the merge.
• 03:27 So it depends on which way you want to go here.
• 03:30 Once you've done that, you're gonna configure the columns.
• 03:33 So this is the interface you'll be looking at.
• 03:34 Once you actually trigger it, it's gonna come up.
• 03:36 It's gonna say what's the separator?
• 03:38 What would you like it to be called?
• 03:39 Would you like to be called merge?
• 03:40 You say, no, no, I wanna choose my own delimiter for this thing and
• 03:44 I wanna provide a new column name.
• 03:47 Now, I always recommend when you do this that use a dash to delimit between the two
• 03:51 columns and the reason for
• 03:53 that is because that way you get something that is account-department.
• 03:58 This becomes particularly important if you're dealing with things that
• 04:01 are numeric on both sides.
• 04:03 And I'll give you an example as to why.
• 04:05 Think about when you have department 1 through 11 and product 1 through 11.
• 04:09 Which one is 111?
• 04:12 Is it department 11, product 1, or department 1, product 11?
• 04:17 If you put the dash in between, there is never any doubt, can't get it wrong.
• 04:21 If you forget that dash,
• 04:23 you're gonna see some interesting things happen in your data at some point.
• 04:26 So don't risk it, it's not worth it.
• 04:30 Once you've got these things done you can actually separate and
• 04:33 create these columns you need.
• 04:34 At that point in time, guess what?
• 04:36 You can actually link them together.
• 04:37 So notice here we've created two columns called Lnk_AccDept.
• 04:41 There's an Accounts table in the middle that has that key.
• 04:44 On the Budgets and Transactions table, it now has that key,
• 04:47 instead of the account department columns.
• 04:49 They can now be linked together.
• 04:51 You'll also notice that in this visual,
• 04:53 we've actually hidden the Lnk_AccDept from all tables.
• 04:58 And the reason being is, not only on the dimension side, or
• 05:00 the fact table side have we hidden it, but we've also done it on the dimension,
• 05:04 because nobody is ever gonna wanna use that report.
• 05:06 It's an artificial key that's pretty ugly in order just to link these things
• 05:10 together.
• 05:11 So if you don't need to see in your model hide it from the client tools,
• 05:14 nobody needs to see it.
• 05:16 And you're good to go.

Lesson notes are only available for subscribers.