Quick introduction to power pivot then we'll go into getting started we'll see how we can use linked Excel tables and power pivot and how we can actually import data from a variety of sources.
Finally we'll create a pivot table from our power pivot data so power pivot is a very powerful excel add-in and it really does help get around the limitations we have out of the box with Microsoft Excel.
Power pivot was first released in 2012 for Excel 2010 and today during today's demo I will be using Excel 2013 the first thing we're gonna do is really talk a little bit about what power pivot.
Can do for you first off power pivot helps you get around the limitations of Excel as far as the size of a worksheet a regular Excel worksheet it's just over a million.
Writing a vlookup or making a pivot table can take a lot of time so power pivot makes it much quicker we can solve our functions quicker and we can also make large pivot tables without that.
Time s constraint that we have with their regular pivot Excel we're also going to see how we can actually build relationships from different tables within Excel so no more going to the IT.
Department and asking them to run a query on your data bases you can do that yourself using this powerful added right so first thing I'm gonna do is go and show you how we can add this adding.
You'll notice at the top of my ribbon here that my last tab is Acrobat what I'm gonna do is I'm going to activate power pivot through the file menu now for those of you working from a company.
Computer this may be something that you need to request from IT so if you don't have it available just send your IT department and email requesting the power pivot add-in but for those of you.
Posts Related:
That do have access you'll go to the
File menu and then into the options this will launch the Excel options where on the left hand side we have an Add Ins option down at the bottom we'll use the.Drop-down to choose com add-ins and then we'll select go you'll see that I have an option called Microsoft Office power pack for 2013 again depending on what version this would be different for use.
Though using 2016 I'm going to go ahead and place a checkmark next to that and say ok within a second I get a new tab at the top called power pivot and this is where I will actually bring in all of.
My data I'll add data models to the power pivot section and I'll be able to build pivot tables off of that power pivot data now this is a very simplified explanation of power pivot it gets much.
More complicated and I do encourage you guys to check out our e-learning videos on power pivot or sign up for one of the classes that we have there's also a lot of great videos on YouTube that go into.
Some more examples of using power pivot but now that I have this tab at the top I can start using power pivot to bring in lots of data now the first thing I want to do is I'm.
Going to open up a new workbook using a great keyboard shortcut control in that always creates a new worksheet and here I wanted to show you how quickly power pivot can import millions of rows of.
Data I'm going to select the power pivot tab and I'm going to choose manage one of the big advantages of power pivot is that you can bring data in from a number of different sources you can import and.
Combine sources from sequel from Oracle databases and from a number of other locations such as information on the web CSV or txt files and even data that lives already in Excel when I open up.
The power pivot manage button it gives
Me this smaller window and you can see it popped up right over the top of my screen from this power pivot window I'm going to use the get external data now.This is a step that you'd be using if your data did not already exist in an Excel worksheet maybe it came from a database or from a server or from other sources such as Oracle Teradata Sybase.
You can see we have a number of different options and ways to import Excel data or data in general into PowerPivot now I'm going to go ahead and pull in adapt information from an Access.
Database and this particular file in Access has millions and millions of rows now when you open get external data and you choose to import data from access it brings you to the table import wizard.
Here I can use the Browse button to browse to my computer wherever that data happens to live now this is an example file created by Microsoft called contoso sales I'm going to select it and say.
Open now some databases require that you log in with your username and password this is not so I don't need to fill out any of this bottom section but that may be something that you need to do.
Depending on where you're pulling the data from I'm then going to go to the bottom and choose next where it's going to give me the option to import data by selecting the lists.
And tables that I want to import or by writing a query or uploading a query so if you have a query already written you can easily import it - this wizard but for me I'm gonna go.
Ahead and select a list of tables and views to choose from the wizard I can see that I've got a number of tables here and I'm going to just go ahead and select a few of them and I'm going to.
Say finish now if I wanted to I could go in and filter these not bringing in the entire table just a portion of it maybe filter out some of the rows or some of the columns but for this particular.
Example I'll just go ahead and import all of these different tables from access now as you can see the first few don't have that much data and as I get to the last table fact sales we're.
Already almost to a million rows and now we're over so you can see that it's importing this data very quickly we've already surpassed the limit for regular excel files of a million rows so.
Now I can see I've got about two point two eight million rows of data that I'm bringing in to power pivot I'm going to go ahead and close this window once I get that green checkmark saying it was a.
Success and I'll see that now my data actually lives in this power pivot window and those five tables that I imported came through including the one with over a million rows now I want to.
Show you one of the things that I love about power pivot and that's the fact that I can actually create or view the relationships that existed within that database so up along the ribbon I'm.
Going to select diagram view and this will actually show me those tables that I brought in and the relationships that exist now fact sales is that large two point two eight million row table and.
You can see that most of my relationships are built off this table I have my relationship with Jim product and I can see that that relationship is highlighted when I hover over the.
Connector here I have my dim date and the relation with the date key and dim channel with the channel key and then my dim product subcategory is related through that dim.
Product table so again you can create your own relationships so if some of my data live in access other data lived in a sequel database I can bring those all into the powerpivot window and simply.
Add my relationships by right-clicking and choosing create relationship now I'm going to do that on a simpler example right now in another worksheet these tables are kind of a little overwhelming.
So I'm going to do something a little bit simpler so we can focus on this amazing tool power pivot now here I have a very simple set of data in Excel now it already lives in Excel and this is.
One of those situations where many of my clients many of the students in any of the classes that I teach always want to know vlookups and the main reason for wanting to use the vlookup in a scenario.
Like this is that you want to be able to add information that lives on another worksheet so here I have a second tab in this workbook called managers and I can see that I've got my different.
Departments and the managers first and last name for that department now I could write a vlookup that pulls the information using that department as the key but one thing that power pivot does.
So well is it allows us to build pivot tables on multiple tables of data so we're in the past we would actually have to make all of our data exist on one worksheet power pivot allows us to build.
Relationships and then pivot on all of the data from a variety of tables and lists so I have my data in excel I am going to first off add this data to the data model and I'm going to do that from.
The power pivot tab choosing add to datum because I've selected inside of my data it recognizes this list and I can see that it's found at the range of cells.
And I'm going to make sure to let it know that this data set does have a header row so I'm going to place a checkmark next to that and say ok it then launches the power pivot window.
Where I can see that same information is brought into power pivot and it converted that list to a table making it dynamic in other words if any of my data changes in Excel it'll automatically.