You must eliminate two-way relationships when modeling. In this video, learn how to successfully set up the right types of data relationships.
- [Instructor] Creating an effective data model can be a major task. There's some situations where it's difficult to create a one to many relationship. This is because there is no unique value per row in one of your data sources. In these situations, it's necessary for you to create a compound key. And essentially what this means is that you are combining two fields together to create a unique value. What we're going to do in this lecture is actually create two very short and small data sources that don't have a unique value per row, then create a composite key to join them together. So the first step is let's go ahead and click enter data. And the first table we're going to create is our sales table. We can call this item and type in one, one, two, and two. So we don't have a unique value per row for item and next, we're going to type in our color. So we can say, this is blue, this is red, blue, and red. So I wanted to pause here to point out that there is no unique value for item or color per row, but if you look at them in combination, there is one row for the item number one that is blue, the item number one that is red and so on and so forth. And then we can go ahead and type in our sales. So let's say that this is 12, 10, 11, and nine. So we can load this. Next, let's create our feedback table. So we can type in enter data once again. And let's call this our ratings table. So you can type in item and we're going to do the same thing. So one, one, two, two, and then for the color we can do the same, which would be red, blue, red, and blue. So now we can type in our ratings. So we can say, this is a five star, we can say, this is a 4.2, we can say, this is a three, and we can say, this is a four. So now we have our two different tables that don't have a unique value per row for any given field. The next step is to actually open up the power query editor. So click the transform data button. And what we can do is before we add our column, we actually need to change our item from a number to a text. And do this for both of these tables. Yes, replace current. So now we can head over to add column and then select custom column. And what we can do is call this composite key. We can insert our item and then instead of add, type in the ampersand and now we can type in our color. So let's hit okay. And now we have that composite key. So there is a unique value per each row within this data source. So let's go ahead and do this within our sales table. So you can add, well, let's clean this up a little bit. We can move this column. So now let's head back to add column and click custom column. So let's enter in our item and our ampersand and our color. So let's rename this composite. So we head back to the home tab. We can close and apply this, and now the last step is just to head over to the model view and we can actually combine these data sources together. And you can see that they are now combined. So that was just a quick walk through on how you can actually create a composite key to combine data sources together that don't have that one to many setup.
This course was created by Madecraft. We are pleased to host this training in our library.