Locked lesson.
About this lesson
You cannot build a Many-To-Many join in Power Pivot, so what do you do when your data is in a Many-To-Many format? In this module we will explore how to work through a specific Many-To-Many case using a Composite Key.
Exercise files
Download this lesson’s related exercise files.
Solving Many-To-Many Joins with Composite Keys - Theory.docx66.7 KB Solving Many-To-Many Joins with Composite Keys - Theory - Solution.docx
66.8 KB
Quick reference
Solving Many-To-Many Joins with Composite Keys - Theory
An overview of creating composite keys to deal with Many to Many join issues.
When to use
When one of your tables has two (or more) columns with repeating values which, when joined, create a unique value.
Instructions
Creating a Composite Key column
For each table you wish to join:
- Edit your query in Power Query
- Select the columns you wish to merge together
- Go to Add Column --> Merge
- Choose to use a separator (such as a dash)
- Rename the column as desired
Using a Composite Key column
- Ensure you have a Composite Key column on both tables to be linked
- Ensure that the Composite Key column on one of the tables contains unique values
- Link the tables together using the Composite Key columns
- Hide the field on the Many side of the relationship (the side with the *)
Hints & tips
- To keep the original columns along with the new column, use the Merge operation in the Add Column tab instead of in the Transform tab
- Remember that the order in which the columns are selected is the order in which the data will appear in the new column
- It is recommended that the composite key field be hidden from all tables, as it is one that will never be used in the Pivot Table
- To ensure a column contains unique values in Power Pivot, use the Remove Duplicates feature on the column in Power Query
- This technique can work well on its own but may also be used in conjunction with bridge tables, covered in the section on Solving Many to Many Joins with Bridge Tables
- 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.