Solving m:m Joins via Composite Keys

Quick reference

• 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.