Excel Power Pivot Introduction - How to Use Power Pivot

so I am often asked what is powerpivot and how did you get that tab on your Excel ribbon well in this video we are going to look at what power pivot is and why would you use it so what is power pivot well power pivot is also known as the data model and it is found in Excel and also in power bi enabling us to model our data ready for further analysis and reporting power bi offers much more in the way of connectors and Dax and certainly in visualizations but in this video we're going to see how to use it in good old Excel where we are also capable of amazing things now power pivot enable us to work with large volumes of data we are certainly not limited to the million rows of Excel or even to the hundreds of thousands where Excel begins to slow and struggle we also get to use the powerful Dax language a very rich formula engine enabling us to perform powerful calculations that we may not be able to do in Excel and we can also work with multiple sources of data multiple tables which we can relate so classic Excel use of having data in different sheets or tables and using lookup formulas to bring them into one place one table we can then do your some hips and create your pivot tables etc power pivots are not like that we'll keep them separate and we will relate them so that we can then use those multiple tables in our pivot tables so let's look at the scenario we're going to run through in this video so we are going to bring in some data from a few different sources on screen at the moment on the Left we have our transactions we had for CSV files all saved into our folder and we're going to bring all four in and append them to each other using power query we then have these other tables we have a list of courses we have a list of venues and we also have a calendar table and the classic use of Excel would be to write a vlookup for an index match go in from the course column on the left to the table on the right and bringing in additional information now in this example there's only one other column called category but it could be loads of columns of information about the course we also have the same situation with venue here people would write a vlookup to bring in information about the venue button pin up the size of the transactions table on the left hand side it would get a lot wider as vlookups bringing extra columns and that transactions table is the big table now in this video it's only a little over 3,000 rows I think but it's could easily be hundreds of thousands in Excel really slowing it down in PowerPivot it could easily be more than a million and it's nowhere near as much of a problem and by creating relationships between the tables instead of writing formulas like vlookup is a much more efficient way of working we only have to mention the venue such as Dovercourt once an information about it can be related rather than bringing that information in to be mentioned hundreds of thousands or millions of times so this is going to be a simple model in this video just to get an understanding of what power pivot is and how it works we're not going to create some amazing dashboard at the end or whether we will be putting our data into a pivot table to do some basic analysis and show it working we just want to focus on how how appear that helps us to work with these volumes of data and how we relate and write some basic Dax now powerpivot is a calm adding so you won't see the tab on the ribbon if you're new to using it it needs to be enabled and we can do that by clicking on the file tab coming down to options into the add-ins category on the left then we'll select come add-ins from this manage list and click on go and here we have the checkbox to tick for the power pivot for exhale adding you tick that box click OK mine's already enabled so I'll click cancel and you'll have that tab on the ribbon then you have two main ways that you can access powerpivot we can do that from that tab so if I click on the tab on the ribbon I'll see the opportunity to manage my data model creating measures and adding data to the model adding tables to the model and these are things that we'll look at also if I click on the data tab in that ribbon there is a button to manage the data model in the data tools group there and that will open up the power pivot window where you can also create measures relate your tables bring data in from different sources and work with power pivot now let's begin to import our data from those different sources into power pivot now one way we can do that is I could go to the data tab and open up the data model I can open up this power pivot window and from within power pivot we have options on the Home tab here to import data from different databases from services and then from other sources where you'll get options for Excel files and from the web and various other feeds etc but that can be quite limited and you're heavily encouraged to use power query to import this data into the model and not to do it through power pivot although it is possible so if I close down this power pivot window and stick you move the data tab I'm going to get data from file and from folder so we will begin by importing those four CSV files with our transactional data this will ask us for the path if I click on browse and I'll never get to what I want which is power pivot intro and then the data folder and I'll click OK and this will take us to this window where it lists all the files from that folder we've some information about its of 6 tensions and the date that we lost access and modified towards the bottom we have a few buttons and I'm clicking transform data to take us into the power query editor where we could perform multiple transformations to shape what we want now of them various videos on power query on this YouTube channel so we're not going to get too involved with that here all I'm going to do is click the button to combine these files in the header of content there this is their opportunity to check through the files I'm just going to click OK and that's going to stack them up into one big transaction or list all for CSV these combined into one table I'm going to remove this first column because I do not need any of the information stored there I'm going to check the name of this query on the right and just capitalize that DD but I'll keep it called data and as ages that no other work here I'm to close and load closing loads whoo and this is going to be a connection only I'm adding it to the data model so I mentioned earlier that this is I believe a little over three thousand rows of data so not really anything troublesome but imagining that this is hundreds of thousands or millions of rows of data we do not need to put this into an Excel workbook it is stored in the model meaning we do not have the limitations that Excel produces if I click OK that query is loaded on the right hand side here in the queries and connections pane and it is 3214 rows I'm going to collapse some of these ugly-looking queries that were created so I just see my day two one and let's go and get the other tables that we need it's back to get data back to from file but this time they're all in that excel workbook I have three tables all in that same work I'm going to navigate to my desktop where it is for now power pivot intro folder and there is the courses file that I want import the Navigator window appears and I can see two worksheets three tables one of the tables was called courses but has been branded courses one because there's a sheet called courses as well I'm going to tick select multiple items and choose the three tables previews on the right-hand side better skip along transformed data so I could make modifications but in this video not going to be doing too much there in fact I think the only thing I am doing as I look at the calendar table which is something we'll be using a little bit of is come to the courses query M&F this course is table I'm going to rename it courses and of course is one I'll check the data types in the headers and I'll click on venues and check that but I kind of know it's all alright and I'm just going to close and load close and load to connection only data model click OK so that loads on the right hand side there and I have the transaction table a fact table and then three look-up tables one for the dates one for course is one for venues all loaded into the model so that we can now start to look at modeling that data so at the moment we have these four different queries or data sources loaded into the model and if I was to click on the insert tab and create a pivot table and it automatically picks up there I want to be using the workbooks data model so I'll do that and I'll just insert it to this worksheet and on the right hand side we have the fill disappear and you can see the four different tables with arrows next to them to expand them and that they're coming from this model so it looks a little bit different to what you might be used to if you're new to power pivot but you've used pivot tables before now let's see an example for what we're dealing with right now if I expand the courses table here and I'll bring some course information in neither course name and I'll put the in rows and that quite happily lists the different courses from that table and then maybe that open day to the transactional table and I'm just going to drag in the attendees and we get this so that's going to sum the attendees from that transactional table something I'm sure we're interested in doing how many attendees attended each of the different courses throughout all of this data these four years were for data and we can see a couple of things here first of all in the pivot table they've all got the exact number of attendees and the grand total is ridiculous it shows the same number as well so there's obviously a problem there also in the field list on the right I have a warning that I've got no relationships between those tables and that I probably need some and this is what outside of PowerPivot you typically be doing for vlookup bringing that information together into one place to be used here we need those relationships so let's go ahead and do that now I'm going to click on the power pivot tab and I'm going to click on this manage button at the start which is the exact same button that you see on the data tab and we used earlier in this video here we have the power pivot window and I can see there are four sheet tabs at the bottom of the screen so it looks very much like Excel but it certainly is not Excel we do not click in the cells that you can see here although it does look like a worksheet this is called your date of view we also have a diagram view so click on the diagram view at the top then we get this we see the four different queries or tables that we're dealing with you and there are no relationships between them at the moment now before I create those relationships I just want to switch back to date of view and at the bottom I'm going to navigate to the calendar sheet because there's a couple of things that I'd like to do one here and to explain this better just before I make some of these changes I'm going to click on this little Excel icon in the top left of the window to bring me back to the workbook and what I'm going to do is with this pivot table I'm going to do something a bit different I'm going to remove the course and a number of attendees and instead I want to bring in data from the calendar table I'm going to bring in the weekday no and as I put weekday name in rows look how he orders it it's orderly alphabetically and not by the week it's a Friday 1st Monday 2nd and if I remove weekday name and put in the month name we have the same issue it's alphabetical and not in sequence that months would be in a year so this is a problem that you get with power bi and also a power pivot but you do not get with Excel Excel have a custom list so it knows how to all of them power pivot doesn't so what we're going to do is come back into power pivot I will start with the month name I'm gonna select a month name column and in on the Home tab a button sort by column sort by column what color to sort is it month name yes what by the month number click ok that will now know how to order them it's going to use the number of the month 1 2 3 etc to know how to order the 9th January February March etc and I will do the same things a weekday name of select weekday name sort by column sort by column automatically picks up weekday names its selected by the weekday column click OK that is done I could explore that if I wanted by switching back to the workbook and if I drag in month name again this time is ordered correctly remove that dragging weekday name it sort of crappy let's go back to the model because the next thing I'd like to do here is on the design tab at the top we're going to mark this as a date table so when we're performing some time intelligence in our Dax manera analytics it knows that this is the column of dates to perform time intelligence you should have a query a table which lists all the dates from all of the years from your date this is important this is already created here it came from Excel worksheets there are more efficient ways of setting it up in power query or even in PowerPivot trying to skim some time off this video though here we're just going to mark it so it knows to use this one source and not create these date tables for use when needed what color we'll be using is a date it is click ok that's now be marked as the table to be used now let's come back to the Home tab switch to diagram view and establish those relationships now we can drag these tables around order them however you like typically you have the transaction one in the middle and the others going around it or at the bottom and the others going along the top and there are advanced users and stuff toolless i've ordered and resized in this way and to create relationships we're now going to drag between the fields you're always encouraged to drag from the fat table a transactional table to the look-up tables so to begin with if I drag the date field from data to the date field in calendar that would establish that relationship so this is a vlookup that's the lookup value to the first color of the table array same idea and it create this one to infinity icon or this many to one relationship it was dragged from the many to the one it's the most efficient way of working we then dragged from course to course to establish that relationship and then from venue to venue to establish that relationship as you hover over each line indicating a relationship it highlights in green the fields that being used which is very nice just to check that it has understood you and you've dragged them correctly and their relationships have been set up if I did click on the design tab at the top and then manage relationships but we can also create these in this way it's also nice that you can see a list of order relationships as opposed it's looking at a diagram and you can see the cardinality mentioned as many to one there and where it the filter direction and what tables are involved table 1 and table 2 so lots of interesting information that we're not necessarily going to dive into too much in this video but I wanted you to see that manage window ok relationships are done next step is to start creating some Dax calculations which we can then start to using our pivot tables or anything else we may want to use with the power of Excel so what is Dax and why is it so useful well Dax stands for data analysis expressions and it is the formula language behind power pivot it is extremely rich there are a lot of Dax calculations and it is evolving quite fast as a language and can take a little bit of time sometimes at the beginning for Excel users to become accustomed to it now one of the things with Dax is that the calculations can be reused a calculation is known as a measure you can create calculated columns but you can create these measures which is really where you want to be focusing and in this video we are just going to look at creating measures we're not going to do any calculated columns but a measure can be reused but only calculated once calculations in pivot tables or if in Excel normally you have a formula in every single sale of that column or in a pivot table you're doing the same calculation like a sum in multiple pivot tables now there are also a lot more Dax calculations as I say it's a very rich language and if you focus on pivot tables they can only do 11 some average standard deviation and 8 more now yes you can create calculated fields but that's never be great measures can be formatted in advance when you're using pivot tables you have to apply that formatting or when you write a phone and cell you have to form in it that cell every cell a measure can be formatted at the time of creation and every time you use it it will be formatted that way you don't need to tell it it knows in advance and these measures can also be referenced in other measures so imagine when you do a sum if in Excel and you have to tell it what column you want to sum where if you've got to measure that sums things you can reuse that measure in other calculated measures like some if equivalents as it would be so these are some of the advantages which make Dax so useful and so interesting for excel or power bi people to pursue so we are going to write five different Dax formulas and although we'll be keeping it simple these have been chosen because I think they're good examples of some of the potential and the advantages that Dax provides for us so I'm going to click on the button to bring myself back to the workbook and from the power pivot tab we're going to create a first measure we're going to click on the measures button and select new measure and this is your new measure window it begins with the table that's going to store the measure and I'm going to leave that as data I'm going to put all of them in that transactions table the measure name the first one is going to be total attendings where we are simply going to sum the attendees column from the data table I won't worry about description but then we'll type the formula now in this formula box you can hold down your ctrl button on the keyboard and scroll with your mouse wheel as a nice way of zooming in and just making the size larger therefore when you and if I type some so just like the some function from Excel you get us some a Dax function as well and if I press tab to bring that along it prompts me for the column name to sum and nice and simply that is going to be the data table attendees column so if I double click on that data attendees now for those of you who may have written Excel formulas on data that's in the table this would look pretty much the same really to how you might have done it before some of the other measures we create will look a little bit different but if you've got experience in it earlier in the area sorry that is good for you when you're beginning with Dax I'll close the bracket and press my button to check formula it says there's no errors that sounds good I'll come and say that the formatting will be a number it is a whole number they're kind of 2.3 people it's like a 2 or 3 and I will need a thousand separator we've had quite a few attendees and this is the advantage as I was saying about formatting the value upfront I won't have to tell it again but can uses calculation this simple sum in multiple pivot tables but only calculate at once it won't calculate at three different times gives him three different pivots I want to have to format it three different times it is faster and more efficient I'll click OK and that measure is created if I scroll down and this right hand side in the field list I can see it there with the little FX icon and let's go and create the second measure measures button new measure this is also in a data table and this one is total courses where we will write a formula that will find out how many courses in total that we have run now this one's going to be a count on up summing the attendees column I'm counting it to see how many there are for this function as our type counts I'm going for count rows notice some of the functions you might recognize like count and count a and count blank button there are others like count rows count X distinct counts which you do not get in Excel so immediately you've seen how rich the DAX languages count rows and it prompts me for a table the some probably four column this one prompts me for a table and the table is going to be data that's where the transactions are no point in me counts in the courses table that just tells me how many different courses we offer how many did we run that's the data table close off the bracket check the formula looks good number hole number thousand separator okay ii measure it's created now these have been added onto the worksheet as well here I can see I've actually got a pivot table because I did have a blank pivot table before I started creating these measures and because it was active they've started to add days into the pivot table straightaway now I'm going to click on a cell and the worksheet to come out of that pivot table at this point but nice that we can see fifteen thousand four hundred sixty-five attendees 3214 total courses it's always nice if you can check your decks regularly just to see that it's working you may have an idea or you may know for sure what the answer is this question and we can check out that works before we start using it in different formulas and visualizations etc now it's time for another measure new measure measure number three data table this one is going to be called attendees previous year so want to write a measure that will calculate how many attendees be received in the previous year to whatever the current year is and that company will be specified virus lie sir or the roulade was of a pivot table or something like this now for a formula a couple of good ones here which is part of the reason picking on these for my demonstration we're going to start with the most important function of all in Dax which is a function called calculate which can add or remove filters to an expression is extremely useful and important to know we're not going to talk about it what's much now that will be you know far more in depth than what does a video can offer but it prompts for an expression and one of the reasons I want to use this is to demonstrate that we can reuse a measure I am going to bring the total attendees measure into this measure and that's another advantage a use them the fact I can reuse a measure not only multiple pivot tables but also in other measures I can use it again and again and again and it's only calculated once so two attendees comma drops for a filter now another functions coming in here we're going to use a function called date add and here it is if I double click on this this returns a table specified by expressions and it allows us to shoot a number of intervals in the past or also in the future now the first thing it asks for is where the dates are there in the calendar table date field comma number of intervals - one one previous set a current one come up what is the interval it's a year we do have the option of using interval such as month and others close bracket for date add close bracket for calculate check the formula and then we specify numbers once again this is a Navajo number with a thousand separator and there's measure number three it's not been added to a pivot table because I clicked outside of it but it is added to the data table okay next couple of measures this one it's going to be called attendees death prayer yeah the difference to the previous year so we created that previous measure which found out how many attendees in a previous year and we can use that in pivot tables etc but I'm really I'm creating that with the idea that now what can create comparisons in other measures so all I'm going to do here is do the total attendees I'm going to use that measure and subtract that previous measure the attendees previous year what's the difference between how many and how many in the previous year check the formula and here we go with whole number use a thousand separator okay that pivot table selected so it's been added to it now what I'm going to do let's just remove some of these from that pivot table and let's check how somebody's working so far so if I was to drag the course filled into rows there's already cause it's like we saw earlier in the video but and maybe I'm going to drag in total attendees into values and there's a total for all of our courses much better than how we saw it earlier in the video it now clearly works we've got a total at a bottom fifteen thousand four six five you may even remember that number familiar but now we've got that feel to context being applied in the pivot table how many for a dashboard course how many for VBA how many for Excel level one and we can now see that now if I was going to bring in attendees for the previous year and drag that in here and then change course into year where's year four make and the table now this is nice because we can check how things are working before we start using it in other examples where it may not be clear where he's working or not so here we can clearly see that a total attendees 2016 was two thousand nine hundred and six and in their previous year it moves into 2017 is bringing back the same number so I know that that measure is working before I start using it in other examples where I may not be able to tell just looking at a number whether it's right or not but although this is not necessarily an amazing report right now it is double check in my Dax before I take it much further and talking about much further let's click outside of that pivot table and create a thionyl measure and we found the difference this time I want to know the percentage difference so attendees centage diff preview it's I'm calling it and the formula is going to use a function called divide once again this is a Dax function that you do not get in Excel or in your pivot tables it's known as you're safe divide because I can use this division to create a percentage but whereas Excel sometimes produce a div slash zero ever this gives us an alternate response to that so I'm doing the attendees difference previous year by attendees preview they are my numerator denominator come at zero as an alternate response to avoid the error close the bracket and for something different this time as well it's a number again that the format's will be a percentage I don't want any decimals and I don't need a thousand separator I'm clicking okay that measure is added as a fifth and final measure for this video set a data table on the right-hand side so we can now start to see that in action just in a little bit already just that little bit further in some pivot tables on our worksheet so I just wanted to create a few more pivot tables to fully demonstrate the reusing and the formatting behind our Dax measures so I've still got this pivot table on screen at the moment and I just want to do a couple of quick things to this I'm going to mouse click on the pivot and coming to the options and I want to turn off the auto fitting of column whips that can be really frustrating when we bring the slicer in in the moment and how we're just the whips automatically the next thing I want to do is change the pivot tables name so I'll come up to the analyze tab and I'll give it a decent name such as attendees buy it you and I thinking this pivot table I can now remove the attendees previous year measure and bring in something a little bit more useful such as the difference to the previous year so that now we can see that there are eight hundred forty two more attendees 2017 competitor 2016 3 9 for 18 compared to 2017 and as I mentioned earlier in the video you know don't expect an amazing dashboard in a few minutes I'm going to dedicate to this it's just some quick examples of n measures in action and the relationship between em multiple tables and we just resize that column okay so there's one let's take a copy of that pivot let's have another one about here and we'll give it a decent name this one is going to be about courses so attendees buy it course and for this one I'm going to remove the year bringing the course from the courses table and you can see i've got the two measures from the data table run so i've got information from a calendar table in the first pivot table of the two measures running i'm now get information from the courses table with the two measures running so you can see how strong those two relationships are now with this second one I may decide to bring in the percentage difference and notice how the formatting is already applied to it we mentioned have the formatting in the pivot table so in a format of the measure was applied when it was created so whenever I drag it into a measure and we can see mesh has been reused here as well in more than one pivot table the formatting is taken care of and we can reuse the same calculation without only apply it once now I wanted to bring in one more pivot table so I'm going to copy that one I'm going to bring in another one this is also going to have the course but it's going to be the number of course it's run it's a number of course is by course some quick naming here please don't judge me too much on the naming gonna get rid of total attendees and these two differences and just bringing their measure we used when we did two count rows it's now can see how many of each of those courses we ran over the whole of these four years now I want to go back to one of our pivot tables here so I'm going to come to the first pivot table and I'm going to bring in a slicer quickly and this is going to have the venue now I can't see the venue in the list of tables at the moment I see calendar I see courses I see data where's the venue and when you look to the top there is an old tap at the moment it's only showing the active tables all three of those have been used with monks tear-free pivot tables I choose all now I see venue I can bring in the venue field click OK and I've got a slicer let me quickly format it differently and if I use that it's currently filtering that first pivot table but I can come up to reports options and I can hook it into the others and now you can see the name of the pivot tape was quite handy and I'm going to bring this in for all of them then let's go and create another slicer now for this one I don't want the next slice are interacting with the years going to keep that set wrong and select the second slot sir so second PivotTable bring in a slicer and that's because this one is going to be about the year so don't it's reacting this makes green the only reacting with the years pivot let's make it work of attendees by course at n number of courses buckles so if I choose 2017 I can see that we ran six Excel formulas and functions courses only two best practice and tips courses and two bi introduction courses in 2018 oh can I see the data for that etc and this second slice is just function under that last two pivot tables the first one's controlling all free and although this is a crude example knocked up in just a few minutes to showcase all four tables in our model working together whether they be in a pivot table through measures or through slices and we can see the relationship between them and the filter context being applied by the years and the courses in our pivot tables so hopefully this has been a useful demonstration of how you can use PowerPivot to bring data in whatever technically is power query to do that model it and then produce some analysis in the backend I hope you found this video useful please check out some of our others on our YouTube channel and come check us out at computer gaga you

Channel: Computergaga

Views: 184,691

Rating: 4.8904381 out of 5

Keywords: Microsoft Excel, Power Pivot, Power Pivot Introduction, Excel PowerPivot, Excel tips and tricks, Computergaga, Alan Murray, Power Pivot Excel, DAX formulas, PivotTable, Excel PivotTables, Advanced Excel, Excel for Analysts, Excel 2016, Excel 2019, Office 365, Introduction to Power Pivot, Power Pivot Guide, Advanced PivotTables, Data Model Excel, Advanced Excel Tricks, Excel Training, Power Pivot Basics

Id: CjSm5sI3z8o

Length: 41min 42sec (2502 seconds)

Published: Mon Dec 23 2019