Before we start the course, why don't I demonstrate how to apply power, pivot to some data.
This video will have some dense content, but don't worry, I will guide you through every step that's
covered in this video at different points in the course.
If you work with Excel, you're probably familiar with the old.
I need a report that summarizes these three different data pieces request.
And naturally, those three data sets just have to be structured a little bit differently, enough so
that you need to use some FS to summarize the data across each source.
So what have we been asked for?
Well, our boss has asked for a summary of net profit by store, location by month.
Sounds easy, right?
I mean, this isn't hard, but it will take a lot of time, plus a really solid idea as to what you
want to do before you actually do it.
We need to split the month and year off of our dates for at least two of our tables.
And then we'll need to create our summary table layout with this summary table.
We're finally ready to write some really, really long formulas using some s, which is a fairly advanced
function itself.
Now, our data isn't that complex, so isn't there a better way?
Well, as the course name implies, I will show you a better and easier way with power pivot with no
need to understand these pesky Sonmez functions.
So how can we do this?
Step one is to structure our data and data tables.
As I showed in the review session, this can be done by selecting the data processing control t pressing
OK in the pop up.
That appears once all of our data is in tables and don't forget to name them.
I need some dimensional tables.
The first dimensional table I'll create is the dates table with the list of appropriate dates plus fields
describing the year, month and day.
And of course I'll make it a data table.
I'll explain what a dimensional table is early in the course and discuss why we do this.
The other dimensional table will create will list are unique store names.
To do this are simply paste to store answer from one of my other sheets into a blank workbook, go to
the data tab with my ribbon and use to remove duplicates command.
I'll make this a table and call it stores, and now I'm ready to find my power, private relationships.
On the data tab, there is a button called relationships that I will click on to launch a new window.
I'm going to go quickly in this demonstration, but will guide you through each of these steps and explain
the concepts in full detail later.
I needed to find six new relationships, two for each of our tables.
For my first relationship, I'm going to connect the labor table with its month column to the calendar
tables dates column.
This tells Power Pivot that the month column from the labor table contains the same values as the calendar
tables dates column.
Further, it says that our calendar table can be used to look up specific information from the labor
table to you.
This might seem obvious, of course.
The date in the calendar table and the date in the labor table are the same, but we're laying out the
specific details in a way that power pivot can connect them together.
I'll do this for both of the other tables with their date field and then I'm going to repeat the process
for all three tables and connect them via the stores table and it's Starfield.
These relationships all tell power how to connect the three tables together through two dimensional
tables.
Once we've defined these relationships, Power Pipit will work a bunch of magic in the background and
all we need is a pivot table to take advantage of it.
So on a clean sheet, I'm going to insert a new pivot table that uses this Workbox data model as the
data source.
When I do this, I get my usual pivot table fields window with a slight twist Apple store from the stores
table and added a zero entry drop a month from the calendar table and add it to in the value section.
I'll add the sales from the sales table, the purchase orders from the purchase orders table and the
employee wages from the employee table to the.
We now have a pivot table with all of the information from our complicated summit summary displayed
at the beginning of this video.
We created this in half the time with half the trouble, with twice as many features.
So now are you ready and eager to learn how to use power?
Pivot.