Create the data model and define relationships in Power Pivot

- [Instructor] Now that we uploaded the master data and the sales data into the data model, we're ready to create the relationships. Let's go to the data tab and take a look at our current queries, queries and connections, we see the list here. To create the relationships between our master data and the sales data, we can go to data tools and relationships. Select new, and you get to select the table on the one side and the related table and the columns on the other side. So this is one way to create the relationships. But if you already have your data uploaded to the data model, you can do it directly from the Power Pivot window from diagram view. And I personally prefer that view because it's so much easier to create the relationships that way. So I'm just going to close this and close this, let's go and bring up the Power Pivot window. We get to see all the data that we loaded to the data model on a separate tab. We can see exactly how many rows they have. So notice the sales employee, for example, has over 1,000 lines, our sales data is over 23,000 lines. Now in diagram view right here, we get to create our relationships. Now, this is our sales data, I'm just going to drag it and bring it to the middle here. All I have to do to create the relationships is to drag and drop. Let's start off with customer ID. Our customer ID is related to customer ID right here. So just click and drag and let go of the mouse and we have our first relationship. It's a one to many relationship. Next is salesperson, person ID, and this is related to the person ID right here. So let's select it and drag it and relate it to the other table. Now, Power Query figures out what is one and what is many. Our last relationship is for product item ID, and it's called the same thing in the master product table. So just drag and drop, and we have our relationship. And anytime you hover over the line here, you can see what is connected to which field. So now that we have our relationship set up, we can create pivot tables that take data from the sales data, but also show related data from the master data tables. So we could take a look at our sales value by customer name or sales value by employee name. Before we start with our pivot table, though, there is one thing we need to do, and that's to figure out how we can get the latest month automatically extracted from our dataset. Because in the future, every time we refresh our dashboard, we want to show the data for the latest month and compare that to the data from the previous month. So somehow we need to be able to flag these in our sales data.