In the last chapter, I introduced Power Pivot by setting up some basic relationships and using some
pivot tables to extract data related to them.
We created some flexible some and count of calculations that summarized our data without any messy chain
formulas or workups.
As part of that chapter, I gave you a brief 30 second run through of the power pivot ribbon and we
use it to market date table in this chapter.
We'll get into the Excel data model, separate interface in more detail.
So let's go take a quick tour of it.
The data model can be accessed either through the data tab and the managed data model button or on the
power pivot tab on the manage data model button here.
Clicking on either one will open up the power pivot interface.
The power of interface is similar to the power query interface, and that is its own separate window
with its own ribbon's.
However, it doesn't have as many features as power query does, and it doesn't have nearly as many
commands as Excel does along its rivet.
The home tab is primarily for accessing data from external data sources, for formatting data and for
sorting data and viewing it differently.
Here we have access to data for you, diagram, view and some hidden options, the data diagram, if
you will, alternate between a diagram view of our data, which shows the relationships in the tables
and a data view which shows us our tables and the values within the show.
Hidden Button toggles whether or not you can see hidden tables, you can hide tables as necessary to
limit how many data pieces show up.
In your data view, we will not be hiding any data.
The calculation area toggles on and off this extra little area below our data tables where we can visualize
measures and other computations that we'll use throughout the rest of this course.
On the design tab, we have a few more functional features, the first thing to note is this is where
our mark date table option is, as well as another option for creating data tables that we'll look at
shortly.
We can create and manage our relationships from here just as we can and excel.
And we have some commands for adding new columns, managing our columns, visibility with Free's and
finding and inputting functions, as well as managing our calculation options.
The advanced tab has more complex components, including perspectives and reporting properties.
We won't be going into these features of power pivot in this course.
In addition to the ribbon, the power window has a few other features.
Notably, we have our column dropdown on the left.
This is very similar to the dropdown field in Excel to the top left that allows you to pick the name
ranges here.
We can pick any column and jump to it within our data set.
This could be helpful if you have a large dataset with many, many columns.
Additionally, we have a formula.
Borro here along the top.
This works very much like Excel Formula Bar, where you can write in formulas instead of writing.
Excel formulas will be writing tax formulas here.
When we do this with an ADD column, it's a calculated column and we'll discuss calculated columns versus
calculated measures in this chapter at the bottom of our power pivot screen, we have the series of
tables that have been added to our data model.
We can click on any one of these to jump to that data table and see what its data looks like below this
list of tables.
We also identify the record that we're on and the number of records, and we can iterate through each
of our records one by one if we desire, or we can go to the very end or very beginning.
This is most useful for informational purposes to see how much data is in the table.
I can't think of any particular examples where I actually iterate through one by one.
Finally, to the bottom right, we have our grid and our diagram patterns.
These correspond to the data view and diagram a few buttons on our home ribbon.
So if we click on the diagram for you, we get our diagram for you here.
If we click on the grid view, we get our data view.
And then, as with most other windows, you can stretch it and shrink it as necessary with your corner
button.
Aside from that, this covers the power pivot interface.
So let's go ahead and close out of this and let's take a quick tour of the power pivot ribbon with an
Excel.
The power pivot ribbon in Excel has a few extra commands that will find useful.
Most notably are the measures and KPI drop downs will be using these extensively.
Additionally, there's the add to data bottom button.
This button will add any specified table directly to the data model and is a fast way of taking data
that's in Excel and making it accessible to your data model.
To detect Button is a shortcut to allowing you to detect the relationships in your currently specified
pivot table.
So if I have a cell selected in a pivot table and I click on Detect, I get my relationships detection
window.
Finally, there's a settings button with some advanced settings that we won't be changing.
That's a quick tour of our power pivot interface.
So let's start getting into the power pivot features and doing some computations and creating some measures
that we can take our pivot tables and make them far more flexible and far more powerful.