Solving Many-To-Many Joins with Bridge Tables - Practice

Locked lesson.

Upgrade

About this lesson

Armed with the technique on fixing our Many-To-Many join problem, it's time to apply it in our sample model.

Quick reference

Solving Many-To-Many Joins with Bridge Tables - Practice

Bridge tables allow us to create separate tables with unique keys in order to link tables via One to Many relationships.

When to use

When you are trying to create relationships between tables, but both tables have many repeating values in the columns you wish to use for the relationship.

Instructions

Create a Staging Query

  • Edit the Power Query that creates the table which has been loaded to Power Pivot
  • Make sure that the final step of the query is to set the data type for each column
  • Right click on the final step --> Extract Previous
  • Name the new query “Staging – “ and the original table name
  • Close and load the query as a connection only, but do not choose to load it to the data model

Create the new Dimension table:

  • Create a new query by referencing the newly created Staging query
  • Select the column(s) you wish to keep --> right click --> Remove Other Columns
  • Right click the column you wish to use as the “one” side of the relationship -> Remove Duplicates
  • Rename the table with a descriptive name
  • Load the new table to the Data Model

Edit the Original query

  • If you kept more than one column when creating your new Dimension table, remove the additional columns from the original table

Create the relationships:

  • Link the new table to each of the original tables (not the staging query tables)
  • Hide the relationship field on the Many side of the relationship

Hints & tips

  • If there could be values in each table to be linked that don’t appear in the other, it may be a good idea to create two staging queries, append them, and then remove duplicates again to create your dimension.  This way you will ensure you always have all entries in the bridge table
  • This technique can be combined with the Composite Key technique covered in the Solving Many-To-Many Joins with Composite Keys section
Login to download
  • 00:05 So let's go look at how to use our bridge tables to try and
  • 00:08 solve the rest of the relationships we need in this model.
  • 00:11 We're going to display the query viewer, and we're going to go and
  • 00:15 take a look at the Transactions table.
  • 00:17 So let's right click and Edit, and take a peek at what we have inside.
  • 00:20 And what you're going to see is that inside this table,
  • 00:24 we have our account department combinations.
  • 00:27 As well as our account names and classes and groups.
  • 00:30 Now there is a one-to-one relationship between the account department
  • 00:34 combination.
  • 00:35 And between the Account Name, Class, and the Group,
  • 00:39 which we need to break into a different table.
  • 00:42 The challenge we have is that right now,
  • 00:44 Budgets only has an account department combination.
  • 00:48 And Transactions has the rest of the information that we need.
  • 00:52 So what we're going to do is we're going to look at this, and
  • 00:55 we're going to say, hm, okay, this isn't such a bad thing.
  • 00:58 But maybe we can actually make something happen from this that will work out
  • 01:02 in our favor.
  • 01:03 So the first thing I'm going to do is I'm going to set up something that I
  • 01:08 can use to apply across the board on this.
  • 01:10 And what I want is, I want a step right at the end here, after Replaced Value,
  • 01:14 that actually is consistent all the way across.
  • 01:16 So final thing I'm going to do is, I'm just going to actually go in and
  • 01:20 set up something to declare my data types.
  • 01:22 And even though the data types have already been declared,
  • 01:25 I'm going to grab each of these individual columns.
  • 01:28 And I can actually format multiple columns at the same time by saying Change Type
  • 01:33 here to Text.
  • 01:35 And I'll force this guy, instead of Decimal, to be a Currency.
  • 01:38 This is a good practice anyway.
  • 01:40 Because the final step before you load to a data model table really should be to
  • 01:44 actually set your data types.
  • 01:46 What I'm going to do now is with that step in place,
  • 01:49 I'm going to right click on it and I'm going to say, Extract Previous.
  • 01:53 And this will break all of the previous steps into a new query while keeping this
  • 01:58 query's results intact.
  • 02:00 That's important because I already have stuff based off of the transactions table.
  • 02:05 So we'll go and say, Extract Previous, and it says, what would you like to call it?
  • 02:08 And I'm going to call this one, Staging-Transactions, and
  • 02:12 I'm going to move this guy into the Staging Queries group.
  • 02:15 And what you can see now is that Staging-Transactions has all of my
  • 02:19 original steps, including my Source.
  • 02:22 So if you needed to update that, again,
  • 02:25 you can click the gear icon here and everything will be fine.
  • 02:29 And then Transactions, you'll notice it also has a Source step, but
  • 02:33 that Source step says equals Staging-Transactions.
  • 02:37 Anything that happens in this query will flow through automatically into
  • 02:41 the Transactions query.
  • 02:42 Now I don't want to load this to the data model.
  • 02:45 So what I'm going to do is I'm going to say, Close & Load To,
  • 02:48 and I'm going to load this newly created query.
  • 02:51 As Only Create Connection, but I'm not going to load it to Power Pivot.
  • 02:55 And this will just create a pointer,
  • 02:57 you'll see the Transactions query is going to load and it's going to do its thing.
  • 03:02 And then you'll see that Staging-Transactions will change to
  • 03:05 something called Connection only.
  • 03:06 I can still see a preview of it, but it's not loaded unless it's
  • 03:11 called by the transactions or another query, so that looks cool.
  • 03:15 What I'm going to do now is I'm going to right click this guy here and
  • 03:19 I'm going to say Edit.
  • 03:21 And I'm going to take a look at what I actually have here, so
  • 03:23 let's open up the Queries pane.
  • 03:26 What I'm going to do now is, in the Staging-Transactions query,
  • 03:30 I'm going to grab Account and Department.
  • 03:32 And I'm going to go to Add Column, and I'm going to merge these columns.
  • 03:36 Now I'm using Add Column to do this, I'm going to merge them with a Custom and
  • 03:42 a dash and I'm going to call it Link_ActDepartment.
  • 03:46 I'm going to end that off, this is going to add a new column
  • 03:51 which flows through into the Transactions table as well.
  • 03:57 Now here's the thing, this is my Fact table.
  • 03:59 The only thing I need in the Transactions table is the Link Account Department to
  • 04:03 link to another table.
  • 04:05 The Date, because I haven't dealt with that yet, and the Amount.
  • 04:09 So I've held down my control key for all of those, and
  • 04:11 pay attention to the order I selected them.
  • 04:13 I can right click and say, Remove Other Columns.
  • 04:17 That will get rid of all those other columns, and I now have a nice,
  • 04:21 succinct Fact table.
  • 04:23 Now, that's pretty cool, let's go deal with Budgets,
  • 04:25 we'll make our Link Account Department there.
  • 04:27 So I'm going to grab my Account first, hold down Ctrl, and
  • 04:31 grab Department because I want to change the order of these guys.
  • 04:36 And this is going to be a transformation, so I'll just right click and
  • 04:39 say Merge Columns because I don't need to preserve the originals.
  • 04:43 And we'll do the same thing, Link_ActDepartment,
  • 04:50 there we go, those guys are set up now.
  • 04:55 So we have an Account Department link here, and an Account Department link here.
  • 04:59 So these guys are set up and ready to be merged to another table, but
  • 05:03 I don't have a table to merge them to yet.
  • 05:05 So what I'm going to do is I'm going to go back to Staging Queries,
  • 05:09 I'm going to right click, and I'm going to say Reference.
  • 05:13 This will create a new query,
  • 05:15 which is pointing to the results of the Staging-Transactions query.
  • 05:20 I'm going to call this one COA for chart of accounts, and in this table,
  • 05:25 I'm going to say, I'd like to keep the Link Account Department.
  • 05:30 I'd like to keep the Account and the Department, the Account Name, the Class,
  • 05:34 and the Group.
  • 05:35 But I don't need anything that's going to go on a Fact table, so
  • 05:39 I don't need to do any dates here or anything like that.
  • 05:42 So at this point I can right click and say, Remove Other Columns, and
  • 05:46 this gives me a nice long list of things.
  • 05:48 But unfortunately you can see there's multiple repeating values here,
  • 05:53 I need unique values.
  • 05:54 Right click, Remove Duplicates, and that will get rid of all the duplicate records.
  • 06:01 So that now I have a unique set of values here, with their appropriate
  • 06:06 items on the right, I'm going to move him in this area here.
  • 06:11 And now I can say Close & Load To, and I can load the new COA table to
  • 06:16 Only Create Connection, and add it to the data model.
  • 06:20 This will load in, Budgets refreshes, Transactions refreshes.
  • 06:24 Because of course, I changed the table structure under those by
  • 06:29 putting in a specific key with a Link Account Department.
  • 06:34 And here comes COA, it's loading as well and should give us a nice short
  • 06:39 table that's only got about 200 and some odd records, I believe.
  • 06:45 There we are.
  • 06:46 So now we're going to go into Manage Data Model and
  • 06:50 we'll create the relationships between our tables.
  • 06:55 So on my Diagram View, nothing's got relationships anymore.
  • 06:58 And at this point,
  • 06:59 I can actually make things a little bit more complex than they were.
  • 07:02 I don't need to link my budget department to the individual tables here,
  • 07:06 because they don't actually have any of these anymore.
  • 07:09 But it does have Link Account Department, Link Account Department.
  • 07:15 Of course, both sides, we'll right click and Hide the many.
  • 07:21 I don't really ever need anybody to see this, so I'll Hide it.
  • 07:26 Remember here, I've also gotten many, many repeating departments in this area here,
  • 07:31 but I've only got unique departments here.
  • 07:34 So I'll drag this guy across, and right click and Hide him as well.
  • 07:39 And now if I go back to my data model, and
  • 07:44 I go look at my Summary table.
  • 07:47 What you'll see is that now I can put the department name on rows,
  • 07:51 everything will work just fine.
  • 07:54 But I can also grab from my COA table, the Class,
  • 08:00 I can grab the Group, I can move these guys around.
  • 08:06 And everything is linking up perfectly well with no issues.
  • 08:11 Because I've been able to actually use a combination of more
  • 08:15 complex keys to join the tables.
  • 08:17 As well as bridge tables, in order to build out a nice little model.
  • 08:23 Even though I didn't necessarily have all of the components to make these tables.
  • 08:26 I've been able to actually put them together by using power query to
  • 08:29 reshape them.

Lesson notes are only available for subscribers.

Gift this course