Welcome to E-DAB
video number seven, Data Analysis and
Business Intelligence Made Easy With
Excel Power Tools. And you are not
going to believe it, we get to learn how
to do data modeling. Now, what is data modeling? That's like last video,
where we had some bad data and we had to convert it into
a single proper data set, or when we had
multiple text files and we needed to
convert that raw data into a single proper
data set that we could use for our reporting. But what if we had one,
two, three, different tables and we needed raw data
from each one of the tables to create our report,
dashboard, or analytics? Well, we'd have to decide
which tool we want to use, VLOOKUP spreadsheet
function, Power Query, or the amazing Power Pivot. Now, below this video, you
can download the zip folder. Right click to extract all. And here's all the files that
we'll use for this video, including an access
file where we import some data, our PDF of notes
with written and pictorial description of the
video, and the start file that we'll use to
do all of our examples. Now, here are the
topics for this video. And don't forget,
below each video-- now, this is Excel Basics
1, but below our video, if you look for that
Show More button, you click and there's a time
hyperlink table of contents. If you like what you
see, click that subscribe button and the bell icon. In example number one,
we'll use VLOOKUP, we'll create these formulas
adding extra columns to create one single proper data set. In our second example,
we'll have some raw data in a single table
in Excel, and we'll have two tables from
an access database. We'll have Power Query go
and get all of this data and through these steps in
Power Query we'll convert it to a proper data set, then
from the proper data set, we'll load it directly
to a pivot table cache, and we'll create our reports. And then, our last example
we'll have one, two, three tables in Excel and we'll use
this amazing button in the Data ribbon tab relationships. This button will create a
one-to-many relationship between our fact table and
our two dimension tables. We'll then be able
to pull fields from three different tables
into the same pivot table. Now, the fundamental problem
we have in this video is that we have
multiple tables that we need to use to make our
pivot table reports. And when we have
multiple tables, we have three different
potential solutions. VLOOKUP, Power Query,
and Power Pivot. Now, each one of these solutions
has a particular situation where it will be efficient
to use that solution. VLOOKUP, when you have about
50,000 rows of data or less, VLOOKUP is fine. When your tables are already
in the Excel spreadsheet, VLOOKUP is fine,
and when you want to use one table in the
pivot table field list, VLOOKUP is great. Now, we can also use Power
Query with a very specific merge feature. And we use this when the tables
are coming from external data sources because if we're going
to use Power Query to import it in the first place,
instead of adding VLOOKUP as extra columns, we'll use
the merge feature, which inside a Power Query adds
the same columns that VLOOKUP would. And if we're using
Merge, that means we want one table in the
pivot table field list. Now, later, we'll
see that Power Query can import multiple tables
without using the Merge feature. Now, our final option when
we have multiple tables is Power Pivot
and the data model with the relationship feature
and implicit measure feature. Now, we can use this option
when you have more than about 50,000 records and when you
want to have multiple tables in a pivot table field this. So this option will allow
us to pull all of the tables into the pivot table field
list, whereas with Power Query and VLOOKUP, we'll construct
a single table that we'll use in our pivot table field list. Now, Power Pivot
with relationship and implicit measure. This is the simplest and
easy way to use Power Pivot. In our next couple
of videos, we'll see how to use Power
Pivot and power BI desktop when we have big data, like
millions of rows of data. And we will actually
explicitly build our formulas called explicit measures. In this example, we're
just using Power Pivot because the relationship
feature can replace either merge or VLOOKUP. We're going to start our
examples in the Start file and we're going to start on
the sheet example 1, VLOOKUP. Now, on our first example,
the business situation is that Boomerang Incorporated
sells Boomerang products on four different websites,
Gel Boomerangs, Colorado Boomerangs, Amazon, and eBay. Our goal is to create
a total revenue report by category and website
and a similar report that shows the percent
revenue or grand total. The problem is we have
three tables with raw data. Solution number one is VLOOKUP. And we're going to use VLOOKUP
because this table, control down arrow, has
about 43,000 rows. So that's not too
much data for VLOOKUP. And our tables are already
in the Excel spreadsheet. Now, in order to make everything
dynamic, if we get new data we're first going to
convert this transaction table to an Excel table. Click in a single cell,
control T, and enter. Be sure to come up to
Design, Properties, and we're going to name this. I named it F Transactions
with a V at the end. So I know this table is
for our VLOOKUP example. Now we have our one, two,
three different Excel tables with their expandable
and contractable ranges. Now, this is our F
transaction table and we want to use this in
our pivot table field list. Guess what? This is an incomplete table. For example, there
are units, but how are we going to
calculate revenue, we're actually going
to have to pull the price for each one of these
products into the correct row, and then calculate revenue. We're also going to have to pull
product category and website name as extra columns into
our transaction table. Those two columns we'll use
as our conditions and criteria for the row and column
area of our pivot table. What this means is that
we have to do LOOKUP, for example, here's
the website ID and I can see many repeats,
CB69, CB69, many repeats. For each row I want the
correct website name. Well, that means in this
column, for each row, I'm going to have to
look up website ID, find a match in the first
column of the lookup table, go over to the second
column, and for CB69, of course that means
grabbing Colorado Boomerangs and bringing it back to
each one of the rows. Now, the perfect
spreadsheet function for looking something up
in a vertical lookup table is the VLOOKUP function. Yes, V means vertical. Now we're going to call
this column website name, and watch this. This is an Excel table. So as soon as I start typing
a new name to the right, when I hit Enter, that
column is immediately incorporated as a new column
into the table object. All right. So in the first row, we're
going to take equal sign, VL, I see in the dropdown
highlighted in blue the name of the function, so I hit Tab. Now, the VLOOKUP function
has four arguments. The first argument
we need to enter is called the lookup value. Now, just like we
would have to look at the item we're
trying to match in the first column
of the lookup table if we were doing
this by hand, VLOOKUP needs to do the same thing. So I'm going to give
it a website ID. Now, this is an Excel table,
so when I click on the cell, that's called table
formula nomenclature. Now, back in video
number three, we turned this feature off and
used straight cell references. But we want to look at how
table formula nomenclature works in this formula example. The at symbol
means when it looks in this row in the
website ID column, it will always get
the item for this row. When it copies the
formula down, it will get the next item, AM11. That's the lookup value,
comma, table, array argument, that's where we put
our lookup table. And this is an Excel table,
so this is a great trick. Point your cursor in the
upper left-hand corner, and when you see that diagonal
black arrow, you can click and it highlights
the entire table. Over here, we see the
table formula nomenclature or please give me
the entire table except for the field names. Now I'm going to type a
comma, column index number. Now, if we were doing
this manually, we would read the column, website
name after finding the match for CB69
for this first record, we would then go and
get Colorado Boomerang and bring it back. But VLOOKUP doesn't know how
to do that unless you give it the column index number. And you have to count on
your fingers, one, two, because website name column is
the second column in the lookup table when I put a two
into column index number. That instructs
VLOOKUP to always get whatever the item is
from the second column and bring it back to the cell. Now we have to type a comma
to get to the fourth argument and there's two different type
lookup, approximate match look up, that's for tax rates
and commissions, that's not what we're doing here. And I'm going to down
arrow exact match. That's what we're doing. We're trying to find
exactly the character, CB69 in the first column
of the lookup table. Now, you can use true or
the number equivalent one. That will tell VLOOKUP to do
approximate, or in our case, you can put false or a zero. Both of those tell VLOOKUP
please do exact match lookup. Close parentheses. I'm done with my formula. Now, what is VLOOKUP doing here? Actually, it knows first that
it's doing an exact match. So when we give it a
lookup value of CB69 and a table to look
up, VLOOKUP races through the first column
of the lookup table when it finds a match, then
because we put a two here, it knows to go one, two,
to the second column, get Colorado Boomerangs, and
bring it back to the cell. That's exact match. Now watch this. This is an Excel table, so
when I simply hit Enter, it fills the column with
the correct website name. Now I'm going to come up between
F and G and double click. Now, when I click
in the top cell, Control Down arrow,
and in the last cell I want to hit the F2 key. I'm just verifying that the
relative cell reference is working and that table is still
locked on our lookup table. That is working. Control Enter, Control Up arrow. Now that's the only
item we're looking up from the website table. From this table, we have to look
up price and product category. Now I hit some columns between
G and K, so let's select G to K, then right click, and unhide. Remember to change the
size of the J column. All right. Our second column, we're going
to add in our data modeling process is called product
category and Enter, equals VL, Tab. I'm going to Left arrow
over and this time we're getting product ID. That's the lookup value. Comma. The table array. I want the entire product table. I see that diagonal
arrow, click. It puts the table in. Comma. Now, for column index
number, we're getting price. So I have to count on
my fingers, one, two, three, four, it's
in the fifth column. So I have to give
you look up a five. That tells VLOOKUP, go get
whatever's in the fifth column. Now, comma, look up
range, 0 for exact match. Close parentheses. And when I hit
Enter, it fills Down Wow. I made an error there. Product category is not in
the fifth, it's in the fourth. No problem. In the top cell,
I hit F2, and I'm going to change
this one to four. I'm counting on my fingers,
one, two, three, four. So now, when I hit Enter,
it will repopulate. That is totally cool. Now we need to get price. So I type price,
Enter, equals VL Tab. I'm going to look up using my
left arrow going and getting product ID. There's my table
formula nomenclature for relative cell
reference, comma, the table, the whole product table, comma. Now, one, two,
three, four, five. Price is in the fifth column,
so for column index number, when I put a 5,
VLOOKUP will obey, will go get whatever's
in the fifth column. Comma, zero for exact match,
close parentheses, and enter. Now, the last column we need
for our data modeling is I need to multiply units and
price because our pivot table is going to be website
name, product category, and then add total revenue. Revenue and enter. Now, equals-- and
I'm simply going to Left arrow, and wait a
second, that table formula nomenclature for
relative cell reference is different than when I use it
inside my formulas over here. And here's why. There's only one square bracket
because the name of this column price is not repeated in
any other column header. If I arrow over to product
category or product categories here, and it's over
here, so there's a slightly different table
formula nomenclature when you're using a
relative cell reference and there's duplicate column
names in the Excel workbook, or your referencing a column
that doesn't have a duplicate anywhere else. Times Left arrow to get units. That formula will work. So when I hit Enter, now
I have my total revenue. Guess what? We have completed data modeling
using spreadsheet formulas by adding one, two, three,
four different columns. Now we have our complete
one transaction table. I want to create a pivot
table on a new sheet, so I'm going to click
in a single cell, insert pivot table, or we're
going to use our keyboard. Alt, N, V. It got
the table right. The default is new sheet. I can click OK with my
mouse, but since that button is highlighted, I can
simply use the Enter key. There is our pivot
table field list. I think the first thing I'm
going to do is come down here, double-click, and
we'll rename this. I called a cross tab
for revenue with a V because this is our
VLOOKUP solution. There is our column
that we added and we're totally allowed to
drag and drop down to rows. Instantly, I get a unique list,
product category down to rows. Instantly I get a unique list. I made a mistake. Website is supposed
to be over in columns, so I'm going to pivot
the pivot table. Now I get my cross tab, I
drag revenue down to values, right-click to add number
formatting, something like number, comma,
separator, zero decimals. Click OK. Up in the title I'm going to
come up to the formula bar and indicate the unit and enter. Now, our goal is to have
the cross tab for revenue, but I also want to express
each individual item inside the pivot table
down in the column totals and the row totals
as a percentage of the grand overall total. I do not want to have to repeat
all the steps to create this. So this is a great trick. Highlight the pivot
table, Control C, and at least three
rows down, Control V, everything's looking good
except for the calculation. So I come inside
the pivot table, right-click, summarize
values is how we change the aggregate function. Show values as, lots of
amazing calculations for us, and the one we want,
percent of grand total. I click. That's 7.33%. Represents an N
logical test where we're taking the total revenue
for Colorado Boomerangs. And the distance
category and comparing it to the grand overall total. Now, that's doing an analogical
test with two conditions, but of course, the row
totals out the end. That's the total of all the
beginner revenue compared to the grand overall total. Similarly right here, the total
revenue for Colorado boomerangs is compared to the grand
overall total, 30.17%. All right. That's our first
example with VLOOKUP. Let's go over to our second
example, Ex 2, Power Query. Now, for our Power
Query example, we have the same business
situation, goal, and problem. The solution, however,
will be Power Query. We will use Power Query to
import tables from an access database and Excel. We'll bring them into
the Power Query editor, then we'll use the merge feature
and the add column feature. Now, before we import
our data from access, I actually want to cheat and
go over to the Power Pivot Relationships tab. And here's the same
three tables we're using for all three examples. And we want to notice something
about the transaction table and our dimension or
lookup table website. In VLOOKUP example,
we noticed that there are many repeats of website
ID in the transaction table. Over here in the dimension
table or lookup table, the first column always
has a unique list of items. Notice there's only one
CB69, one GB43, but over here in the transaction
table, we're allowed to have many duplicates
of our website ID. That just means
we had many sales at this particular website. In data basing, in data
warehousing, and data analysis, there's a special name where
you have the same column in two tables and one table is a
lookup table with a unique list and the other table
has many duplicates. The special name is a
one-to-many relationship. Now, in our third example,
we'll use a feature inside of Excel and PowerPoint called
the relationship feature. But now we want to go look
at our Access database, which has the data we want to import
for our Power Query example. Now, we can double-click
our Access file to open it. Access is a database that
holds tables with raw data. If we double-click and
open up the Products table, we could see here's the
first column with product ID, this is our unique list in
our lookup table or dimension table. This is going to be the one
side of the relationship. If we double-click and
open up F transactions, here's the many side. We have many repeats
in the product column because we sold the
product many times. In database and also in Excel,
if we use the relationship feature, we can use something
called a relationship to substitute for VLOOKUP. Now, this button
over here, Access, is the same button
we'll see in Excel when we use relationships. I'm going to click and we
can see visually here there's a one-to-many relationship. When I double-click, you
can see the relationship is between product ID. And what this means
for us when we're importing this data from
Access over into Power Query is these two tables
can see each other. So when I import
transactions, if I want to do VLOOKUP to
get one of these columns, for example, the product
category in price, these two columns will already
be in the F transactions table because there's a relationship. So this is the first time
we've seen this relationship. And again, we'll see
it in Excel also. But this is a
substitute for what we did with the VLOOKUP function. One other thing here in
Access I want you to notice, that's an icon that shows
a key for a lookup table that has a first column
with a unique list. That's called a primary key. When we use this primary
key product ID over in our transaction table
right there, remember we have many duplicates. We can sell that
product many times. On the many side, it's
called a foreign key. Primary key, foreign key. And most tables in a
database have a primary key, even this F transaction
has an ID for each one of the transactions. Now we're going to close this
and go back over to Excel. Now, open the Data ribbon
tab, Get and Transform. We have to bring this Excel
table and those two tables from Access into the
Power Query editor and then do a bunch of
steps to merge those tables. So let's first go to
Get Data from Database. And we're going to get the
data from an Access database. Click, navigate to the access
database, double-click. Now, the navigator dialog box
looks into the Access database. Now, we only have two tables
there, sometimes databases have lots of different objects. And guess what? All we need to do is
import the F transactions because, remember, there's a
relationship between these two tables. And when I pull F
transactions into Power Query, it will pull all the
columns from D product also. Now, we want to
click Transform Data, sometimes this is
the Edit button. We do not want to load this. So I'm going to
click Transform Data. Here's our Power Query editor. Over here on the left,
we see we have one query, there is our table,
there is the name, and I'm going to
change this name. I'm going to put
an A at the end. So this will be the name of our
query, F transactions A. Now, it has two steps. Those steps are OK. The first thing we want
to do is we don't need ID. So point to ID,
right-click, Remove. I can see it added a step
in the applied steps list. Now, products. That little icon right
there means a record. And look at this, don't
click on the green value, click off to the
side in the white. And remember there's a
one-to-many relationship and the products
is the one side. So why did it pull? There it is. D product on the many side,
but it pulled the entire record from that lookup table. When we go down to
the next product ID, clicking off to the
side, there it is. What this means is that we can
choose from all of the columns which ones we want to import. We want to import product
category and price. The way we do that is we click
our double sideways arrow, the Expand button, click. I want to uncheck Use
Original Column Name. That would have D products as
a prefix and I don't want that. Now, I'm going to uncheck select
all, select Price and Product Category. By checking this, it's as
if we're doing a VLOOKUP. When I click OK, here in
the Power Query editor, we can see we have our product
category and our price. Now, this is two tables. We still need to add a column
from the website table. And then we need to
make a calculation to calculate revenue. But we have to go
back over to Excel and load that other table. So guess what? We're going to come up to Close
and Load, Close and Load too, and this is an example where we
don't want to load it anywhere except for a connection. We are not done with
transforming that table inside the Power Query editor. So when we load as
Create a Connection Only, it's just connecting
to that data source. All right. I'm going to click OK. Over in queries
and connection, I can see my
connection-only query. Now, I'm going to click in
one cell in my Excel table from the Excel sheet, Data
Ribbon tab, Get and Transform. My window is not expanded, so
we have to hover and there it, is From Table Range. I'm going to click. Now, on the left, I'm
to expand queries. We can see that we
have two queries here. We're going to leave this
name exactly the same. The steps are OK because
we have two columns and they're both text. Now I need to load this. Close and load too also as
only create a connection. Click OK. Now we're going
to manually merge, whereas when we pull
data from a database that had a relationship, it
just automatically offered us to do the merge. But now we have two queries
inside of Power Query. That means we're allowed
to manually merge. So I'm going to
double-click F transactions A. This opens up the
Power Query editor. I'm going to open up on the
left the queries, and now the F transactions, A, this
query is partially done. I need to add those
extra columns. So in the Home
ribbon tab combined, we come up to merge
queries dropdown and I want to merge queries. If I selected merge
queries as new, it would keep these two
queries as separate connection only, and then add a third one. But I'm actually going
to transform this one and later we'll load it. So I'm going to
say merge queries. Here's our F transaction A. From the dropdown,
we're going to select our second table, D website PQ. Now, how we simulate VLOOKUP
or the relationship we saw on Access is we select the
column, this is the many side. We can see there's
duplicates for CB69. Then we select in the
lookup table or dimension table the primary key, the first
column with the unique list. Notice there's
exactly one of each. By selecting these two
columns inside of Merge, we're simulating
relationships and VLOOKUP. Now there are lots of
different join kinds and there's a picture
in the PDF notes that describes
each one of these. The one we want is
left-outer, that means all from the first
and matching records from the second. Now, we happen to
have every single one of our items in the primary key
used in our transaction table. So when it says Matching
From the Second, it will just take all of these. The matching from
the second is in case we have something in the lookup
table that's not over here. Then that would not be
pulled as part of the merge. All right. Now we're going to do this. We're going to click OK. Now, if we come off to the
side, not where it says table, but off to the
side and click, we can see there is our one
record that's matched. Now this is a
different icon here, this pulled as part of
our merge the full table. But it just shows
the one record. I click on the next one, I can
see there's the next record. Now when we merge two tables,
we got a different table icon. This is a table. When we pulled a
single table that had a relationship
from a database, that was a single record. But in both cases, we get
a single matched record from the one side. But guess what? We're allowed to use
this expand button just like we did
when we imported data from our Access database. Uncheck Use Original, and
the only column we want is Website Name. Now when I click
OK, from our manual merge we pulled all the website
names into our one table. Now we have a few more
transformations we need to do. We need to multiply
units and price. Now, in Excel, we
created a formula. Watch this in Power Query. I'm going to select units,
then holding the Control key, I'm going to click on price. And there's an Add
Column ribbon tab. I click and from the
From Number group, I click the standard dropdown
and I say I want to multiply. When I click multiply here, it
adds a new column multiplying. I see my new step in
the applied steps. Now let's double click
this and we're going to call this revenue and enter. Now, I want to go through
each one of the columns making sure that we
have the correct name. And we do. Now we want to go
through each column and make sure we have
the right data type. For date, I'm going to
change it from date and time to just date text. This is units. I do not want decimal, I want to
change this to a whole number. Text. I'm going to change the price
from decimal to currency. The difference is decimal
number can have up to 15 digits, currency can have
up to four digits to the right of
the decimal point. Change it to currency. Same with revenue. I'm going to click,
change it to currency. Now we have all of our steps. We have fully transformed
from three different tables all of our raw data
into one single table. Now I want to load it
to a pivot table cache. Oh, but guess what? We already loaded this
as a connection only. So I'm going to go up to home,
click the close and load, which loads it to its
previous location, then I'm going to come over and
right-click, load too, so we're changing the load location. Pivot Table Report. Let's do it on a new worksheet. Click OK. Now I immediately want to come
down and name this Cross Tab Revenue PQ. Now we have our three tables
converted into a single table in our pivot table field list. I'm going to drag Product
Category down the rows, Website Name down to columns,
Revenue down to Values. Right-click Number Formatting. We'll do numbers, zero
decimals, click OK. I'll indicate the
unit and enter. Now I want to highlight
the Pivot Table, copy, go a few rows below,
Control V inside the values area. Right-click, Show values
as percent of grand total. And so there in
our second example, we used Power Query
to take three tables, transform it into a single
table, loaded to pivot table cache, and create our reports. Now let's go over to our
third amazing example. Example three, Power Pivot. Now we have the same business
situation goal and problem. But our solution is going
to involve Power Pivot. Now, actually, I don't see
my Power Pivot ribbon tab because the real solution
we're going to use here is something called the
relationship button. Now, we're using Office
365, so I will show you how to add your
Power Pivot ribbon tab after we use the
relationship button because, guess what? In all versions
since Excel 2013, we can use the
relationship button to bring multiple tables into
our pivot table field list. So I'm going to
show you how to use the relationships to
connect all these, use them for our reporting. And then, after
we're done, I'm going to open up the Power
Pivot ribbon tab and show you what
happens behind the scenes when you use the
relationship button. All right. In order to use the
relationship button, you have to have
all of your data sets converted to Excel tables. So we don't even have to
click on one of the data sets, we just go up and click
our Relationship button, the Manage Relationships dialog
box comes up, and we click New. Now we have to decide,
since we're trying-- just like we did
with merge, VLOOKUP, and like we saw on Access, we're
trying to connect product ID, this is the many
side and product ID, this is the one side. But it's asking for two tables,
Table and Related Table. So we have to decide
which tables to put where. Now, unfortunately, Microsoft
called this Related Table. If they were building
this dialog box from the point of
view of an Excel user, they would have called
this lookup table if they had built this dialog
box from the point of view of database people, they would
have called this Dimension Table. But Microsoft decided to
call this Related Table. So you just have
to remember what goes into the related table, the
lookup table, or the dimension table. That means the first table is
going to be our fact table. For us, it's going to
be our transaction. Now, this is the
relationship example, so I'm going to use F
transaction R. Then, from the related
table, since I want to connect it to
our products, I'm going to use the
first one, D product R. That D product R is our
lookup table or dimension table. So that's why we put
it in related table. Then we need the
two columns, they're both of the product IDs, so I
select product ID, product ID. Now, notice this is
foreign, this is primary. This is database terminology. Primary key means that first
column with a unique list. Foreign means this primary
key is a foreign over here. That's the many side, we
can have many duplicates. Now, that's it. When we create a
relationship like this, this is a one, from the
lookup table dimension table, to many, that's our
transaction table. One-to-many relationship. When I click OK,
there's a couple things that are going to happen. One is these tables will be
loaded to a behind the scenes database called the data model. So far in the class we have
our data in an Excel sheet, and then we make a pivot table. We loaded data to the
pivot table cache-- well, that's directly in
the pivot table cache, so we make a pivot table. But here, this is
a third option. When we use relationships,
it's not only going to take these two tables
and store in the data model, but it's also going to store
the relationship between the two tables in the behind
the scenes data model. So I'm going to click OK. And there it is. If we opened up our
pivot table field, this right now, we'd
see these two tables. Now we have one
more relationship between transactions
and website. So I click New. And when we click the dropdown,
I want you to notice something. The tables that before said
worksheet now say data model. That's because when we created
our first relationship, they were not in the data
model, but now they are. So I'm going to
click F transaction and our related table, well,
we have an added the website table to our data model yet. So this is the
worksheet table website R for relationship example. So I select. And then the two columns,
the primary key first. This is the website ID from
the first column of our lookup table, and then the
foreign key, this is in the transaction
table website ID. Now, notice that
still says worksheet. When I click OK, it
creates the relationship. If I click the Edit, we can
now see it says data model. All right. Now I'm going to click OK. We've created our
relationships, click Close. Now I want you to click in
a cell not inside the table, somewhere way outside
the table, and now we want to use our
keyboard to create a pivot table on a new sheet,
Alt and V, and look at that. By default, if you open up the
Create Pivot Table dialog box and your cell wasn't
in a table and you have something in
the data model, it defaults to the data model. New worksheet. Click OK. Now, when you do this,
you see all of the tables available from this
Excel workbook including the R ones, which are the
ones from our data model. Now I'm using the latest
version of Office 365. And they change the icons. In earlier versions, they
used to have a darker line at the top, which
visually indicated that these were from the data model. But we were careful
in our naming, so R, R, R, those are all
the ones from the data model. Now this is all. I want to move these data
model ones to the active. If I click on Active, there
are no tables showing. So I'm going to right click
each one and say, Show Inactive. Right-click, Show Inactive,
right-click, Show Inactive. Now we go over to active and
there are all of the tables. We can drag and drop different
columns from different tables and build our
pivot table report. Now, in our pivot
table example, we're not going to do a pivot
table cross tab for revenue, we're just going to
try and add the units. Actually, our next video
will be about a big data example in Power Pivot,
and I'll show you how to do the formulas
in that version. But here we go. Product Category down the rows. I get a unique list. Website Name to Columns,
there is a unique list. And now, when I drag
Units down to Values, it has no problem adding
the units from transaction and having these columns
from other tables. Now let's highlight the
pivot table, Control C, click a few rows below
Control V in the values area. We're going to change
the calculation to percent of grand total. Now, this is a
different set of reports because this is for
the sum of units. Now, think about this. All we did was use the
relationship feature right here. We didn't even open Power Pivot. Now, the thing is in Office
365, all of us have Power Pivot, but in some of the earlier
versions you actually couldn't get the Power
Pivot ribbon tab, but you were always able
to use relationships. And that means
that the data model is in every version of
Excel, but Microsoft was just trying to sell the
access to the ribbon tab in only some versions. All right. We have Office 365,
so now I'm going to show you how to show this. Now, the first
thing we have to do is go to File, down
to options, and we have to enable our Power Pivot. So we go down to Add-ins. And it's not Excel
Add-ins, it's COM Add-ins. Click Go. And you want to check
Power Pivot for Excel. Click OK. And now we see our
ribbon tab there. This allows us to go and
look at our data model, that button is
also over in data. Now if you tried to click this
in one of the other versions and you didn't have the right
version with Power Pivot, it wouldn't let you go look. But we have the right
version, so actually we can use the button in
the Data Ribbon tab or Power Pivot,
Data Model, Manage. So I click. Now, this opens up the Power
Pivot for Excel window. Down here we can see these tabs. These are a preview
of the tables that were added to the data
model because, actually, when you add tables
to the data model, they are not stored
like a regular Excel sheet with lots of duplicates. They're actually compressed
into a columnar database. Now, next video we'll
actually add some big data to the columnar database
and we will be surprised at how small the file size is. But there are the tables. Up here in the Home
ribbon tab, over in View, we're in data view. Let's go to diagram view. Click. Now, this is diagram view. Here are our tables. You can see a
one-to-many relationship, one-to-many relationship. Now, over in Access, they
used an infinity sign. Here they use an asterisk. But if you click on
the relationship, you could see this is
product ID to product ID, over here, website
ID to website ID. Let's go back over to data view. Now, to use the
relationship feature and build a data model
pivot table like we did, you actually don't have to
come here and do anything. But we wanted to come
behind the scenes and see exactly what was
going on in this data model. Now, let's go jump
back to Excel, this is a separate
window from Excel, so I'm going to
go back to Excel, I'm going to use the keyboard
Alt Tab to jump back over to Excel. This is the Excel
window, Alt Tab, this is the Power Pivot window. So just like Power
Query, Power Pivot opens up in a separate window. Now, Alt Tab back over to Excel. Now, something
interesting happened. When we dragged
units down to values, because this is not a
standard pivot table, this is a data
model pivot table. Now, in a standard pivot
table, we just drag fields down to the values and it
makes the calculations. But in a data model pivot table,
when I drag units down to here, it says sum of units. It actually added an official
formula over in the data model. So let's go back over to
our Power Pivot window. Alt Tab. Just like there are
tables and just like there are relationships stored
in this data model, there is a hidden formula
stored in this data model when you drag and drop
fields to the values area. Now, I don't know why they
don't have this as the default, but you actually have to come
to the advanced ribbon tab and click the Show
implicit Measure Button. Now, when I click this
button, there it is. If I expand the column and click
down here up in the formula bar, you can see that hidden
formula that's created. Now, first, the
word Measure, that's the word they use
for formulas that we drop into the pivot table. Implicit, that's the word they
use when Power Pivot creates it for you automatically
when you drag and drop a field into the values area. Explicit measure. That's what we'll
create next video. That means we manually
created the formula. Up here, this implicit measure,
Power Pivot created it for us. Now, there's a
bunch of drawbacks when you use implicit measures. So anytime you have big data,
we're not going to do this. Now, it's perfectly all
right and really convenient if you have a small data set to
simply drag and drop the fields and create an implicit measure. The drawbacks to the
implicit measures are that, well, the first
thing is, it's grayed out, we can't edit it, we
can't change the name, we can't add automatic
number formatting to it as we will learn next video. And also, sometimes, if you
drag and drop a lot of fields, it creates all sorts
of duplicate measures that you do not need. But when you have
a small data set and you're using
the relationship feature, no problem,
you can drag and drop and create implicit measures. Now, this is our third example
and we created a data model by adding the three tables
to the actual data model and our implicit measure
and our relationships. Now let's close the Power
Pivot for Excel window. So in this epic video,
we did data modeling where we used relationships
in the data model to bring all three tables into
our pivot table field list, then create our reports. With Power Query, we imported
datas from Access and Excel, did our data modeling
inside of Power Query, and got one table into the
pivot table field list, and then we created our reports. In our first example
our data modeling was done by adding extra columns
with our spreadsheet formulas. In particular, the
VLOOKUP function, and then we created from our
single table in our pivot table field lists our reports. All right. If you like that video, be sure
to click that thumbs up, leave a comment, and subscribe,
because there's always lots more videos to come
from Excel Is Fun, including next E-DAB number
eight, we'll talk about big data coming
from an SQL database, and we will use Power Pivot. All right we'll
see you next video.
