Locked lesson.
About this lesson
One potential source of m:m (many to many) join issues in Power BI can be solved by combining multiple columns into a "composite key" in order to relate tables together. This module will dig into this issue, as well as how we solve the problem.
Exercise files
Download this lesson’s related exercise files. You can download the source data files for the course from the resources section of your Lessons page.
Solving many to many Joins via Composite Keys.pbix95 KB Solving many to many Joins via Composite Keys - Completed.pbix
98.8 KB
Quick reference
Solving m:m Joins via Composite Keys
Creating composite keys in order to link tables via one to many relationships.
When to use
Use when you need to consolidate two (or more) columns in order to create a column that can be used to create one to many joins.
Instructions
An example of this type of relationship is where Account and Dept are in separate columns, but the account number shows up in multiple departments. To create a composite column:
- Edit your query
- Select the 2 columns (the primary column first)
- Go to Add Column --> Merge
- Choose to use a separator
- Rename the column
Hints & tips
- This technique can work well on its own, but may also require table de-aggregation as well (covered in the section on Solving m:m joins via De-Aggregation)
- 00:04 Now there's a couple of potential issues that are gonna come up when you're
- 00:08 actually building your own Power BI model.
- 00:10 Let's take this particular one, for instance.
- 00:12 We have three tables, we have transactions, budgets, and accounts.
- 00:15 The challenge we have here is that you'll notice that we have many repeating
- 00:19 account numbers.
- 00:20 We also have many repeating department numbers in both the transactions and
- 00:24 budgets Table.
- 00:25 And if we look in the Accounts table, we also have many repeating account numbers
- 00:29 with unique department numbers that are actually being assigned here.
- 00:32 So the key thing is that the actual key to link these two things is a composite key,
- 00:37 which is a join between the account and department.
- 00:41 The reason for this is because the same account could exist in many departments.
- 00:46 If we actually try and apply the same thing to the transactions and budgets
- 00:49 table, we can see that we could amalgamate these guys to come up with a one to many
- 00:53 relationship between our tables and this is exactly what we need to do.
- 00:57 So the good news is is that the PowerQuery tool inside Power BI desktop
- 01:02 can help us do that, we have the ability to merge columns.
- 01:06 So what we're going to do, is we're going to need to actually do that exactly,
- 01:10 the way we do it is we go into Edit Queries.
- 01:12 We're gonna select the first column, hold down the Ctrl key and
- 01:15 select the second column and the order you do this is critically important.
- 01:19 Because the first column you select will end up being the first column that's used
- 01:24 in the aggregated join.
- 01:28 We then go to either Transform, Merge Columns or Add, Merge Columns.
- 01:32 And the difference here is do you want the original columns to stay as well?
- 01:37 If you do Transform it discards the original columns and
- 01:40 only gives you the amalgamated version.
- 01:42 If you do Add Column, it keeps the original columns around,
- 01:46 either way it'll bring up the same user interface that looks like this.
- 01:50 We then configure the column itself, we go through and we provide a delimiter.
- 01:55 This is really important, because if you have things like departments that are 1
- 01:59 through 11 and products that are 1 through 11.
- 02:02 You don't wanna aggregate them without a delimiter,
- 02:04 or you could end up with product 111.
- 02:06 Which would be what is that department 1 product 11 or department 11 product 1.
- 02:11 So using a delimiter is always important, I personally prefer to use a custom
- 02:15 delimiter because the dash is not on the basic delimiter list and
- 02:18 that's personally my favorite.
- 02:20 The second thing that you can do is provide a new column name,
- 02:23 you'll notice here that I've provided one that's called Lnk_Account Department.
- 02:27 Lnk is my nomenclature or short-case for link, and AccDept is the shortened
- 02:32 name for the account department field that I'm going to be using.
- 02:37 Once we've done this, we've merged these columns together, we can actually then go
- 02:41 in and create relationships that end up in a nice one-too-many situation,
- 02:45 where we were in a many to many before has, this is how we solve it.
- 02:48 So let's go to take a quick look with an example.
- 02:54 In this file, we're actually bringing three individual tables in from
- 02:58 one Excel file, so let's go and take a quick look.
- 03:01 We're gonna go to Edit Queries, and you'll see that if you set a little bit of
- 03:04 a trick here under Rawfile, it connects to the Raw Excel file.
- 03:07 If you don't see this, just remember go to the Source gear icon here and
- 03:12 change the file path to the source data folder for all of your GoSkills examples.
- 03:17 These three tables then refer to in the source step, the raw file and
- 03:21 I'll show you how to create this in the next practical application video we do.
- 03:25 The key piece that I want you to recognize is that we have an Account's table,
- 03:29 we have a Transaction's table and
- 03:31 we have a Budget's table that are all pulling from the same file.
- 03:34 So you can update the file once and
- 03:36 have it pull through all three, now let's take a look at Account.
- 03:40 We can see that we have our issue with Account and Dept here.
- 03:43 So what I'd like to do is to add a new column to this, I wanna preserve
- 03:47 the Account and Dept, because I may one day want to filter on the Dept field.
- 03:51 So I'm gonna hold down Account, hold down my Ctrl key, grab Dept,
- 03:55 we're gonna go to Add Column> Merge Columns.
- 03:59 And in here, I'm going to use a Separator, Custom, I'm gonna type in a dash,
- 04:05 and I'm gonna give my new column a name and call it Lnk_ AccDept.
- 04:11 So Lnk is my preface again for link, AccDept is a descriptive name and
- 04:15 you can see that when I hit OK it inserts emerge column,
- 04:18 leaving the original two around, so that I can still use these in filters later on.
- 04:22 Now let's go ahead and look at transactions.
- 04:27 In transactions, I have Account and I have Department,
- 04:29 I'll hold down my Ctrl key to select them both.
- 04:32 The key thing though, is that I'm going to control my filtering for Accounts and
- 04:35 Departments from the Accounts query.
- 04:37 So I don't need to keep these two tables around if I need the one-to-many.
- 04:41 What I do need, is I need the column that now has the linkage between these two.
- 04:45 So in this case, instead of Add Column, I'm gonna go to Transform.
- 04:50 Transform has the same dialog for Merge Columns and
- 04:53 it works in exactly the same way.
- 04:55 So we'll go Custom, We'll go with -, we'll call it the same thing,
- 05:02 Lnk_AccDept, and we'll get rid of this little piece here and we'll say OK.
- 05:07 And notice the only difference is that it now keeps this one column it discards that
- 05:11 other two so it merges them together.
- 05:13 We can do the same thing to Budgets, we'll go grab this guy, but
- 05:17 this time we're gonna grab these first two column,
- 05:19 right click because there's a merge columns here too.
- 05:24 Which behavior does this one give me?
- 05:25 So we'll go to Custom, we'll put down a -,
- 05:29 Lnk_AccDept, And we'll get rid of the merged, we'll say PK,
- 05:34 and you'll notice that the behavior on the right click is the transform behavior.
- 05:39 With this done, we can now say Home, Close and Apply, It will apply and it even goes
- 05:46 and it creates connections in my model because my tables are named appropriately.
- 05:50 So I can see that it is now linked, the Lnk_AccDept,
- 05:54 and the Lnk_AccDept field together nicely.
- 05:57 So is this important?
- 05:58 Well absolutely, because what you'll see now is that I can go and
- 06:01 create a nice little clustered column chart from my accounts table.
- 06:05 I can put the account Name, I can put from my Transactions,
- 06:08 my Sales Amounts, from my Budgets.
- 06:11 I can put my Amount as well and I now have a nice little chart that's showing both
- 06:16 series appropriately with linkage form one particular table,
- 06:21 getting the one-to-many join.
- 06:22 So, this is a perfect way to actually make a composit key
- 06:25 to link your tables together.
Lesson notes are only available for subscribers.