Join Gini von Courter for an in-depth discussion in this video Creating relationships between tables, part of Power BI Features in Depth.
- View Offline
This is the relationships.slx workbook in the Exercise Files folder in Chapter 3, but this is also the last version that we had of the catalog request after we had added the All Dates information that came from the Months worksheet in the Excel workbook. So we have our two sheets here, in our data model, and I want to relate those two sheets. There are two different ways I can do this. Let me first show you how this works in Diagram view.
And I like to make these large enough that I don't need to scroll as long as I can easily do that. What you do is you start with the source field in the source table. And you drop it on what would be the lookup field in the related table. So I'm taking from the Request Date column over here to the Date column in All Dates. Now when I do this, you're going to see an error. And the error looks like this. It says that in that All Dates table, there is a blank value in there, and that's not allowed in a lookup table. So, wow, okay, I can get details, but it's not really going to help now. Wouldn't it be great if I could just go look at this table and fix it? I mean, I can find the blanks. That's easy enough. Let's go to All Dates. Click and scroll down here, and sure enough, blanks is a choice. So, wonder if there are a lot of em. Let's go look. Just one, hmm. But I can't get rid of it here. I have to get rid of it in the original data source. Or I have to filter it on the way in the door here. This isn't Power Query, I don't have the ability to shape the data here.
I take the data in, I can do calculations and I can create relationships. But I'm not allowed to do the kind of filtering that I could easily do in Power Query. So, you might have a choice at this point to say well, we could go back to Power Query, and we actually could. We could take this data into Power Query, shape the data and return it. But let me show you how to do this just with Power Pivot. I'm going to delete this table. It's easy enough to do. It says, are you sure you want to permanently delete this? If we'd added some calculated fields, those would all be gone too right now.
I have no investment in this. Let's just say yes, and out the door goes. Now, let's go back to this table again, it's an Excel file. You may recall that it is in the Reference Tables in Exercise Files. It's called All Dates, and I click Open, use first row as column headers, testing my connection, and clicking Next. Now, this is where I can choose to filter the data that I'm actually going to have returned. So I'm going to choose Preview in Filter, and just as if I were in Excel because I am, I'm going to turn those blanks off and say OK. I can tell there's a filter here on this particular column. I could place as many filters as I want in my data set. So if I have information coming in from 50 states and I only want it from all of the states west of the Rockies, I can take care of that by applying filters.
I'm going to click OK. Let's give it that friendly name again. All Dates. Notice that there are filters applied. I can click and it says the date is not null. Cool. And I'm going to click Finish. Says this connects to an external data source, is this okay with you? I'm going to say Yes. Now, if you were with me two movies ago, you know that originally we had 2,557 rows of data, and now we have 2,556, and I'm going to click Close. Now, let's go back to our Diagram view.
Stretch this out, and go drag from my source column request date to my lookup column in the related table, the date, and there is my relationship. Now notice that both of these tables have a nice little glow to them, like they've had a wonderful time together. And you might think that's kind of a silly feature. If you have 30 tables scattered around here, and you've got the zoom turned way down so that you can see all of them, you fit them all to the page, that glow picking out the tables that you've related is actually kind of nice.
And it will appear, if you click on the table it goes away, but if you click on the relationship it comes back. And notice that it also makes sure that you can tell exactly which columns are being used in this relationship. So that's great. I'm going to click on the relationship and delete it. And I'm going to permanently delete the relationship from the model. Let me show you the other way you can create relationships. You can go to the Design tab and click Create Relationship. And say, okay, in the source table, notice this is the table first, catalog request and it chose that because I have catalog request selected here.
I would like to use request date. Notice that these are not in the order that they appear in the table. They're in alpha order. And in the related table, All Dates, I want The Date. Create. There's my relationship. When it's selected it looks exactly the same way. Two ways then to create relationships, either using Diagram view and dragging and dropping from the source, to the lookup. From the source table to the related table.
Or, if you prefer, on the Design tab, choose Create Relationship and create the relationship in the dialog box. They both do exactly the same thing.
- Understanding data analysis and business intelligence
- Installing Office BI add-ins
- Searching for online data with Power Query
- Shaping data in the Query Editor
- Connecting to data sources
- Modeling data with Power Pivot
- Enhancing PivotTables and PivotCharts with PowerPoint
- Visualizing geospatial data with Power Map
- Creating and formatting Power View reports
- Sharing your data using Power BI for Office 365