Did you ever need to make a pivot table that needed information
from multiple tables? So for example, you needed
to look up the customer code from the master data table so you could bring in
the customer description in your pivot table report? The old way to do this was
to use a lookup function, like VLOOKUP and add new
columns to your source data and bring in the information you need from the lookup tables. The better way of doing this
is to leave your tables as is and instead use relationships. Let's create a pivot table
based on multiple tables. (upbeat music) In this lecture, let's take
a look at a practical example of using the data model. What we want to do is to
create two types of report. Report number one is about
customers and their sales values sorted in descending order. Report number two is
quantity sold by article. Our data is sitting in different tables. Our main table, so our fact
table, is this table right here which is called TableSales. Now all the information here
is just about one company. Sales document, document date,
customer code, article code, quantity, and sales value. So notice these are just codes. We can find a description for
these in the Master Data tab, where we have two separate tables. One table is called MasterArticle. This has article code,
description and color. And the second table is
called MasterCustomer, customer code and customer name. Our end report needs to
show the descriptions and not the codes. This is where the data
model comes into play. Now we can load these to
power query and merge them, create one final query
and create a pivot table based on that one single table. But we don't have to if
we don't need to apply any extra transformations. And in this case we don't. Our data is structured in a perfect way, they're all sitting in Excel tables, we don't need to add any columns or do any transformations here. So we can directly create a relationship between these tables. We don't have to merge them. Where can we do that? From the Data tab right
here, under Data Tools, we have relationships here. This green symbol here,
this is the data model. When I click this it's going
to open the Power Pivot window which is our data model. And if it looks like this it means there is no current
data model in the spreadsheet. Now we're going to activate this the moment we create
relationships between our tables. So let's do that, click
the relationship icon here, and then go to new. Now we need to select our table and decide what is the common
column between the two tables. So our first table here is TableSales. So notice now it says
Worksheet Table: TableSales, let's go with that. The column that we want to
connect to, where there are two, one should be connected
to the article master and the other to the customer master. So let's start with article first. The related table is the
master data for article, which is called Worksheet
Table: MasterArticle. The related column is
article code as well. Now click on okay and the
first relationship was made. Let's create our second relationship. Go back to new, our table is TableSales, but now notice something changed. It says Data Model Table, it doesn't say worksheet table anymore because the moment we
created a relationship we added these tables to
the data model as well. And we're going to see that in a second. Select TableSales. Our second connection
is for the customer code which is going to be connected
to the MasterCustomer. And the related column
is customer code as well. Click on OK. Two relationships were set. You can at any point in
time go and edit them, deactivate or delete these relationships. We're done here so let's close this window and go back to our data model and notice now it's not empty anymore. We have three separate tabs. So each single table that we added was added to the data
model as a separate tab. And here we can see how
many records we have. One of 20 and here is one of 544. Now this here, this is the
data view in Power Pivot. You can add new columns here and create formulas that
apply to each single row. But here you can also
create aggregated formulas which are called measures, which are applied to columns
instead of a single row. These formulas are called DAX formulas. A good feature in the
Power Pivot window here is the diagram view. This actually shows the
relationships that we just created in the other window. This is the information
about our TableSales data, and here we can see the connection. So if I hover over the connection here we can see the field names that are connected with one another. And here we see the asterisk sign which stands for many and a one. So it's one to many. And
here as well, one to many. You can delete relationships from here or create new relationships
in this view as well. Okay, but let's say it, we don't need to make any changes here. I just wanted to show you
how the data model looks after we created these relationships. So let's just leave and go back to Excel. What we want to do is
insert our pivot table. Now, when you create an
traditional pivot table, you would either select a
table or select your dataset, go to Insert and then go with pivot table. But when you have created
relationships between tables, so basically when you have
a data model in place, all you have to do is go to where you want to have your pivot table in and then go to insert pivot table. By default, you get a selection for use this workbook's
data model, click on OK. Now here we get our three
tables all in one place. When I expand these, I see
the fields that I have. Now all I have to do is
just create my pivot table. I'm just going to bring
this closer to our dataset so we can see it better. To get this bigger let's
just change our view to fields selection and
areas selection side-by-side. And now let's define what we want. So for the first report, we
want customers and sales values. So I'm going to put a check
mark for sales value here. And the customer, well, we
don't want the customer code, we want the customer name. So that's from our MasterCustomer table, customer name it puts it in the rows. Right, so all we did is
create that connection and we can create a report
based on that connection. Now, the rest is similar
to what you would do when you're creating pivot tables. I'm just going to update the name here, change the design to
show in tabular format and update the number formatting to show a thousand separator
and no decimal places, and OK. Now notice that when you
use certain tables here, they become darker, so they're
in bold in the all view here. If you switched to active, you can see the tables
that are actually used for this pivot table. Let's create our second pivot table. I'm just going to copy the
first one, paste it right here. And then let's just update the fields for these second pivot tables. So instead of sales value,
I want to get the quantity. And instead of customer name, let's switch to all, go to MasterArticle, I want to get the article
description instead Right, so we need to sort
this in descending order. So actually, I forgot to sort this here, so let's write most click and sort this one first
from largest to smallest. And let's do that here as well.
Sort, largest to smallest. Let's also update the number formatting to show a thousand separator,
zero decimal places, and update the description right here. And we're done with our reports. Now, one thing I want to show
you is in the data model, if you don't want to
see certain fields here because you don't want
the users to use them or you want to avoid too
much clutter on this side, what you can do is hide it from view. If we go back to Data and
go back to our data model, let's go to Diagram View. We can select our fields
here, so our columns, right mouse click and
hide from client tools. So I don't want to see customer code, I also don't want to see the
article code, I can hide it. If you want to hide other things, you can just write most click and hide them from view as well and they disappear from this list. Okay, so basically what we did is we used Power Pivot
together with a data model to create these two reports. All we had to do was set up
a relationship between them. So this is how you can
create a single report that's actually based on multiple tables. Now, this later is a part of
my ultimate power query course. If you'd like to discover
how to use power query like an expert right from the start, check out my complete
course, link to it is below. As always before you leave, don't forget to hit that thumbs up and subscribe if you haven't already and I'm going to see you in the next week (upbeat music)
