mr. Selznick cast sponsored by easy to excel powerpoint for the data analysis
chapter one
what is powerpivot hey welcome back to the mr. excel NetCast I'm bill jelen
we're starting off here on a series of podcasts and supported 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
power pivot and why should I care
now this is a utility that came out from Microsoft not from the excel team but
from the Microsoft seek 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 just go to powerpivot calm
and download this provided you have excel 2010 all right five things the
powerpivot does force that I think just our amazing first thing no longer
limited to a million rows here i have a dataset that I loaded up not just from a
CSV file this data set has a whopping 7.2 million rows very fast to it work
with this I can sort i can filter and great 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 sheet1 here with a 7.2 million rows and
you know typical the IT department at any 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 converse store
ID that gives me the store named the mall developer the region when it was
opened and selling square feet now if you're used to excel you know hey this
is simple this is vlookups right vlookups will join these two tables
together but think about 7.2 million vlookups just to get store name another
seven million to get region and other seven million to get mall developer
you're looking at many many lookups will take forever to calculate the great who
is about power pivot is
you don't have to do a lookup same just define relationship this store column is
related to this store column and power pivot is going to do all the joints for
you
alright so that's the second thing third let's go back to excel 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
sheet1 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 seven million rows as I
scroll down further in the powerpivot field list here are all the fields on
sheet2 so if I want to take a look at revenue cut by for example region from
sheet to ban that's done so there now joining sheet1 sheet2 I didn't do
anything lookups now this is all happening in memory very very amazing
way to go forth and i'm not going to show you in this video is the new tax
function language how Dax while 88 functions index are exactly like the
functions in excel so you'll be very comfortable with those left right mid
month year minutes a day
yeah 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 the state or show me all the dates
in a parallel . one year ago also functions to handle fiscal years if your
fiscal year doesn't end on December 31st finally microsoft is provided something
for you and then finally the last thing the last cool feature is what I call a
synchronous reporting this is where we want to show for example actuals for all
the months that have gone by and budget for the future future months regular
pivottables can't do that and Excel 2010 they added a feature called name sets
that does that but unfortunately named says don't work with data that is just
coming from regular excel 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 data set is now in OLAP
data set which means that you can use name sets to create the asynchronous
reports
excellent excellent feature
alright so as we go through the book each chapter there's a lot of topics in
Chapter I've just chosen one to put here in the videos in the blog and out on
youtube so you'll see ya 11 feature from each chapter learn a lot about
powerpivot as we go through ok I want to stop by see you next time for another
NetCast mr excel