PowerPivot Data Analyst 1 – What is PowerPivot?


MrExcel netcast is sponsored by Easy-XL. PowerPivot for the Data Analyst Chapter One
– What is PowerPivot? Oh, hey. Welcome back to the MrExcel netcast. I’m Bill Jelen. We’re starting off here on a series of podcasts
in support of the book PowerPivot for the Data Analyst: Microsoft Excel 2010. These podcasts are going to go along with
that book and talk about PowerPivot. One video from each chapter. And, first of all, let’s just get started
with the question of what is PowerPivot and why should I care? Now, this is a utility that came out from
Microsoft, not from the Excel team, but from the Microsoft Sequel Server Analysis Services
team. In my opinion, this is the best piece of software
to come out of Microsoft in 20 years. It enables five amazing things in Excel 2010
and the great news is, the client version, the version that works here with Excel, is
free. You just go to and download
this, provided you have Excel 2010. Alright, five things that PowerPivot does
for us that, I think, just are amazing. First thing: You’re no longer limited to
a million rows. Here I have a dataset that I loaded up just
from a CSV file. This dataset has a whopping 7.2 million rows. Very fast to work with this. I can sort, I can filter, I can create pivot
tables from this data and it all works very, very quickly because the data is stored in
memory. So that’s the first thing we have. Second thing: I have sheet one here with the
7.2 million rows. And, typical of the IT department, they gave
me store ID, but they did not give me store name, region, and all that other stuff I need. So somewhere on my computer I had this nice
little lookup file. It converts store ID, gives me the store name,
the mall developer, the region, when it was opened, and selling square feet. Now, if you’re used to Excel, you can go,
“Hey, this is simple. This is VLOOKUPs, right?” VLOOKUPs will join these two tables together,
but think about it. 7.2 million VLOOKUPs just to get store name,
another 7 million to get region, another 7 million to get mall developer. You’re looking at many, many VLOOKUPs that
will take forever to calculate. The great news about PowerPivot is you don’t
have to do VLOOKUPs anymore. Just define a relationship. This store column is related to this store
column and PowerPivot is going to do all of the joins for you. Alright, so that’s the second thing. Third: Let’s go back to Excel. We’ll take a look at the pivot table that
was created here. The amazing thing: here’s our list of fields. These are all the fields in sheet 1. So let’s say that I want to see revenue. Simple enough. We’ve always been able to do that although
this is now revenue from 7 million rows. As I scroll down further in the PowerPivot
field list, here are all the fields on sheet 2. So if I want to take a look at that revenue
cut by, for example, region from sheet 2, bam, that’s done. So they’re now joining sheet 1 and sheet
2. I didn’t do any VLOOKUPs. Now this is all happening in memory. Very, very amazing way to go. Fourth, and I’m not going to show you in
this video, is the new DAX function language. DAX, well A) the functions in DAX are exactly
like the functions in Excel, so you’ll be very comfortable with those. Left, right, mid, month, year, minutes, day,
things like that. Great, but there’s 60 new functions and
those new functions are some of the amazing ones. There’s time intelligence functions, like
show me all the dates that are month-to-date from this date. Or show me all the dates in a parallel period
one year ago. Also functions to handle fiscal years. If your fiscal year doesn’t end on December
31st, finally Microsoft has provided something for you. Alright. And then, finally, the last thing, the last
cool feature is what I call Asynchronist Reporting. This is where we want to show, for example,
actuals for all the months that have gone by and budget for the future months. Regular pivot tables can’t do that. In Excel 2010, they added a feature called
Name Sets that does that, but, unfortunately, Name Sets don’t work with data that is just
coming from regular Excel. It only works with data that is coming from
OLAP. Here’s the beautiful thing. When you take that regular Excel data, run
it through PowerPivot, bring it back to Excel, all of a sudden your dataset is now an OLAP
dataset, which means that you can use Name Sets to create the Asynchronist Reports. Excellent, excellent feature. Alright, so as we go through the book, each
chapter… There’s a lot of topics in each chapter. I’ve just chosen one to put here in the
videos, in the blog and out on YouTube. So you’ll see one feature from each chapter,
learn a lot about PowerPivot as we go through. Okay, I’m going to thank you for stopping
by. We’ll see you next time for another netcast
from MrExcel.

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

17 thoughts on “PowerPivot Data Analyst 1 – What is PowerPivot?”

  1. ExcelIsFun says:

    Totally awesome!

  2. Greg Kramer says:

    best piece of software to come out of microsoft in 20 years…quite a statement!

  3. pinak26 says:

    Hello bill,
    I followed the instructions as mentioned in your book pg 18 fig 2.2..but I am getting blank dates in date column for 50% of the data. Can you please help me understand why???

  4. Maelene Belo Ring says:

    Hi Bill!

    What is the difference between PowerPivot and Microsoft Access?


  5. PowerPivotPro says:

    @Melchizeedek Just wanted to say that I agree with Bill's points completely. I'd also add that Access can serve two complementary roles with PowerPivot. One is data shaping – transformations etc. PowerPivot offers nothing for that. The other is as a data collection/entry tool, via forms. Either way, the role of Access would be to feed source data into PowerPivot. I'm thinking about doing a blog post soon on "Access vs. PowerPivot." Watch powerpivotpro (it's .com) over the next week.

  6. Lute Scrat says:


  7. Gökhan ŞAHİN says:

    That's cool man. thanx

  8. Sabharish Lakshmipathy says:

    hi bill . I want to know how to group dates in a power pivot table 

  9. olipreston1 says:

    Really great series, thanks

  10. TheTeaserking says:

    u talk way to fast and u need to take your picture off the screen. Also u need to go more step by step and do more visual learnig.

  11. Tanner Tucker says:

    Excellent video Bill. Thank you. Love the pace and personal touch of your image.

  12. Edu Pega says:

    Nice Video

  13. Midsummer says:

    Thank you very much

  14. Phil Barley says:

    Thanks, Bill

  15. RAM KUMAR says:

    Excel is so easy on utube vid

  16. Alfa Won says:

    Thanks for the Intro

  17. Hackeen Konan says:

    I know Bill Jelen thru his books for a long time & he always amazes me on his Excel knowledge. Thanks Bill.

Leave a Reply

Your email address will not be published. Required fields are marked *