#### Transcripts

1. Introduction: Hello and welcome to this class on a beginner's guide to power. Pivot in this class, we're going to begin by looking at what is power pivot on. Why would you use it? We would then look at installing power pivots because you may not have a tab on your ribbon If you haven't used it before and you're using Excel out the box, if you will. So we look at how we can get it installed on. Then how to access it. Different ways of access in the power pivot window with an import from data into the model for guns. Use power query for that instead of power pivot. To be precise, because it is the most efficient way of working, we will get data from a bunch of files in a folder were also get some data from different tables. Next, workbooks, and then we will model that data. We're going to do some work in the calendar table to get it efficient, and then we're going to create the all important relationships between that fact tables and our dimension tables. So that air, if you works as we need it, Were there no rights? Um, Dax formulas. We have five different measures that we're going to create in this class. And these measures, I think, are going to be good examples for those of you who are new to Dax to get a feel for wireless languages so useful and annoy step into that realm. And then we're going to crack some pivot tables from that model. So start looking at using that measures, using their tables and doing some analysis, and then we're going to frozen slices into the mix as well. So but a time this class is finished, we will be using all four off the tables in a model that we're related that'll be involved , rules against use all five of their measures in different pivots. Hair was different slices to look how we can use it to interrogate our data set. So let's get into it in role now, and I will see you in the class
2. What is Power Pivot?: So what is power pivot? Well, pal Pivot is also known as the data model and it is found in Excel on Also in power Bi I enabling us to model our data, ready for further analysis and reporting power Bi I 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 except where we're also capable of amazing things now power pivot enable us to work with large volumes of data. We are certainly not limited to the 1,000,000 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 in a bonus to perform powerful calculations that we may not be able to do in Excel Onda. We can also work with multiple sources off data, multiple tables which we can relate So classic excel use off having data in different sheets or tables and using look up formulas to bring them into one place. One table we can then do your summits and credit pivot tables, etcetera power pivots not like that will keep them separate on. 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 from in this video. So we're going to bring in some data from a few different sources on screen at the moment. On the left, we have our transactions. We have four CS fee files, all saved into a folder on we're going to bring along for in on a Pendant to each other using Power Query. We didn't have these other tables. We have a list, of course, is we have a list of values we always have a calendar type, and the classic use Off Excel would be to write a V look up for an index match, going from the course column on the left, to the table on the rights on bringing in additional information now in this example is only one of a column called category, but it could be loads of columns of information about the course. We also have the same situation venue here. People would write a veal cut to bring in information about the venue bumping up the size of the transactions table on the left hand side, it would get a lot wider as villa cups bring extra columns and that transactions table is the big table. Now. In this video, it's only a little over free 1000 rows Off Inc but it's could easily be hundreds of thousands in Excel, really slowing it down. Empower Pivot. It could easily be more than a 1,000,000 on it's nowhere near as much of a problem on by creating relationships between the tables instead of writing formulas like V, look up is a much more efficient way of working. We only have to mention the venue such as Dover Court, once on information about it could 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 understanding off what power pivot is and how it works. We're not going to create some amazing dashboard at the end, whether we will be putting their data into a pivot table to do some basic analysis and show it working. We just want to focus on how power pivot helps us to work with these volumes of data on how we relate and riots and biting decks.
3. Install the Power Pivot Add-inn: now Power pivot is a calm added, 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 two options into the Adan's category on the left. Then we'll select Calm Adan's from this manage list and click on Go. And here we have the check box to tick for the power pivot for Exhale, adding you take that box click OK, mind already enabled. So cancel on. You will have that happen. The ribbon. Then you have two main ways that you can access power pivot. 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 on adding data to the model at in tables to the model. And these are things that will look at also, if a click on the data tab in the ribbon, there is a button to manage the data model in the data tours group. There on that will open up the power pivot window where you can also create measures, relate your tables at, bring data in from different sources and work with power pivot
4. Import Data into the Model: 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 pivots 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, etcetera. But that could be quite limited on your heavily encouraged to use power query to import this data into the model and not to do it fruit, how a pivot, although it is possible. So if I close down this power pivot window and stick him of the data tab, I'm going to get data from file and from FOTA. So we will begin by importing those four CS fee files without transaction or data. This will ask us for the path if I click on, browse and navigate to what I want at which is power pivots intro and then the data folder and I'll click OK on. This will take us to this window where it lists all the files from that folder with some information about it. Such extensions and the date that we lost access modified. So was the bottom. We have a few buttons on. I'm clicking Transformed Data to take us into the Power Query editor, where we could perform multiple transformations to shape what we want now off the 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 head 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 transactional list. All four CS fees 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 off this query on the rights and just capitalize that city. I don't keep it Cold Data and his age is that I know of a work here. I'm going to close and load closing low, too. On 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 free 1000 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 pop this into an Excel workbook. It is stored in the model, meaning we do not have the limitations that Excel produces. Click OK. That query is loaded on the right hand side here in the Queries and Connections Pain, and it is free 1002 114 Rose. I'm going to collapse somebody's ugly looking queries that were created. So I just see my day to 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 already in Excel Workbook. I have three tables or 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 course's file that I want import. The navigator window appears, and I can see to worksheets three tables. One of the tables was called courses but has been branded courses one because, as a sheet called courses as well, I'm going to tick, select multiple atoms and choose the three titles previews on the right hand side. Bennett 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 off, is coming to the courses. Query, um, left discourses table. I'm going to rename it courses and, of course, is one. I'll check the data types in the headers and OK, convention use and check that. But I kind of know it's a little writes, and I'm just going to close and load clothes and low, too. 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 her three look up tables, one for the dates, one for courses, one venues, all loaded into the model so that we can now start to look at modeling that data
5. Set Up the Data Model and Create Table Relationships: So at the moment we have these four different queries or data sources loaded into the model Andi. If I was to click on the insert tab and create a pivot table on the automatically picks up there, I want to be using the workbooks data model. So I'll do that. And I'll just inserted to this worksheet. I don't know, right hand side. We have the film disappear, and you can see the four different tables with arrows Nick to them to expand them on that they come in from this model, so it looks a little bit different, what you might be used to. If you're new to power, pivot that 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, uh, table here and I'll bring some course information in other course name, and I'll put them in rows, and I quite happily lists the different courses from that table and then maybe open data transactional table on. Don't just going to drag in the a 10 days, I would get this, so that's going to some. The attendees from that transaction table something onshore we're interested in doing how many attendees attended each of the different courses throughout Ward of the state of these four years were for data, obviously a couple of things here. First of all, in the pivot table. They've all got the exact number off a 10 days, and the grand total is ridiculous. It shows the same number as well, so there's obviously a problem also in the field list on the right. Ever worn in that? I've got no relationships between those tables, and I probably need some. And this is what, outside of power pivot, you typically be doing the V look up, bringing the 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. I don't get to click on this manage butternut start, which is the exact same button that you see on the data tap on we used earlier in this video. Here we have the power pivot window, and I can see there are four shaped 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 or there. It doesn't look like worksheets. This is called your date of you. We also have a diagram view for kicking the diagram view at the top. Then we get this. We see the four different queries or tables that we're dealing with here, 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 you and at the bottom Unger to navigate to the calendar sheet because there's a couple of things that would like to do in 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 on the number of a 10 days, and instead of what's bringing data from the calendar type, I'm going to bring in the week day night and as I put weight day naming Rose. Look how it orders it. It's orderly alphabetically. And no boy, the week It's a Friday First Monday, second. And if I remove weekday name and put in the month name, we have the same issue. It's alphabetical and no in sequence that months would be in a year. So this is a problem they get with power bi I 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. Oh, start with the month name. I'm gonna slap the month name column and in on the home Tad A button sort by column sort by column. What can enjoy to sort Is it month Name? Yes. What? By the month number. Okay, that will now know how to order. It's going to use the number off the month 12 free etcetera to know how to order the name General, February March and I will do the same thing. Toe weekday Name off. Select Weekday nine Sort by column sort by column. Ortho Ski picks up with the names selected by the weak Die column. Click OK, that is done. I could explore that were wanted by switching back to the workbook. And if I drag in month name again this time it's ordered correctly. Remove that drinking weekday. Name it sort of crab. 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 is a date table. So when we're performing some time intelligence in our Dax Minera analytics, it knows that this is the column of bats to perform time intelligence. You should have a query, a table which lists all the dates from all of the years for your data. This is important. This is already created here. It came from Excel worksheets. There are more efficient ways of setting up in power query or have been in power pivot trying to skim some time off this video, though here which is going to market so that it knows to use this one source and not create these date tables for use when needed. What column will be using? Is it date? It is click OK that's now been marked as the table to be used. Now let's come back to the home tab. Switch to diagram view on established those relationships. Now we can drag these tables around order however you like. Typically, you have the transaction one in the middle one. Yeah, others going around it or at the bottom and the others going along the top. And they're all advanced Jesus stuff, twos of ordered and re size of this way. And to credit relationships, we're now going to drag between fields. You're always encouraged to drag from the fact table, the transactional table to look up tables. So to begin with, if I drag the date filled from data to the date field in calendar, that would establish that relationship. So if this is a V look up, that's to look up value to the first column of the table away. Say, my dear, I didn't create this one to Infinity icon or this many toe one relationship who was dragged from the many to the one that's the most official 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 be news, which is very nice. Just a check that he has understood you and you've dragged him correctly and their relationships have been set up. If I did click on the designs, have at the top and then manage relationships button, we can also create these this way. It's also nice that you can see a list of order relationships as opposed to looking at a diagram, and you can see the card in ality mentioned as many to wonder and where the filter direction on what tables were involved. Take one and table two. 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. Okay, relationships have done. Next step is to stop creating some Dax's calculations, which we can then start to use now pivot tables or anything else we may want to use with the power off except
6. Why Use DAX Formulas: So what is Dax and why is it so useful? Or Dax stands with data analysis expressions on it is the formula language behind power Pivot. It is extremely rich. There are lot off 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. That calculation is known as a major. You can create calculated columns but even create these measures, which is really where you want to be focusing on. In this video, we are just going to look at creative measures were not going to do any calculated columns , but a measure can be reused but only calculated once calculations in pivot tables or 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 folks and pivot tables, they can only do 11 Some average standard deviation on eight Mawr. No. Yes, you can create calculated fields that that's never bean, that great measures can be formatted in advance. When you're using pivot tables, you have to apply that formatting, or when you write a full moon sale, you have to formula that sell every cell. A measure can be formatted at the time of creation, and every time you use it, it will be four matter. 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 some where you have got a measure that sums things. You can reuse that measure in other calculated measures, like a summit equivalent, as it would be a So These are some of the advantages which might DAX at so useful and so interesting for excel or power by people to shoot
7. Write DAX Formulas - Create 5 DAX Measures: So we are going to write five different tax formulas on, although will be keeping it simple. These have been chosen because I think they're good examples off 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 keep from the measures button and slept. New measure on 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. That measure name the 1st 1 is going to be total. Attendees were simply going to some the attendees column from the data table. I won't worry about description, but then we'll talk the formula now in this formula box you can hold down your control button on the keyboard and scroll with your mouse will as a nice way of zoom in in just making the size of larger there for when you write. And if I type some so just like some function from Excel, you get a some Dax function as well. And if I press tab to bring that along, it prompts me for the column name to some and nice and simply that is going to be the data table Attendees column. So if a double click on that data attendees now, for those of you who may have written Excel formulas on date of its in the table, this would look pretty much the same really, to how you may have done it before. Some of the other measures we create will look a little bit different. But if you've got experience in there earlier in the area of sorry, that is good for you when you're beginning with tax, I'll close the bracket and press my button to check formula. It says There's no errors. That sounds good. Oh, come and say that the formatting will be a number. It is a whole number. The kind of two point free people is over two or three, and I will need 1000 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. Buchan uses calculation this simple some in multiple pivot tables, but only cap created once it won't calculate it free. Different times kids from three different pivots. One how to format it three different times. It is faster and more efficient. Ochlik okay on that measure is created. If I scroll down in this right hand side in the field list, I can see it there with the little FX icon and let's go. Great. The second major measures button New measure is also in a data table. On 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 is going to be a count on up. Summing the attendees column. I'm counting it to see how many there are and Phyllis function as our talk counts. I'm going for count rows no to some of the functions you might recognize, like count and count a and can't blank. Then there are others like count rows count X distinct count, which you do not get in excel so immediately. You're seeing how rich the Dax language is. Count rows and it promised me for a table. The some probably for column This one props before a table on the table is going to be data . That's where the transactions are. No point in me counting the courses table that just tells me how many different courses we offer. How many did we run? That's the title. Close off the bracket. Check the formula. Looks good. Number hole number, thousands separator. Okay, second measure is created. Now these have been added on to the worksheet as well. I can see have actually got a pivot table because I did have a blank pivot table before started creating these measures on because it was active. They've started toe. Add these into the pivot table straight away. Now I'm going to click on a cell on the worksheet to come out of that pivot table at this point, that nice that we can see 15,465 or 10 days free 1002 114 total courses. It's always nice if you can check your Dax regularly just to see that it's working. You may have an idea where you may know for sure what the answer is this question, you can check out that works before we start using it in different formulas and visualizations. Now it's time for another measure. New measure Measure number three Data table. This one is going to be called a 10 days previous year. So want to write a measure that will calculate how many attendees we received in the previous year to wherever the coming years that come you'll be specified Virus slice on the road, labels of a pivot table or something like this. Now for the formula. A couple of good ones here, which is part of the reason picking on these from a demonstration we're going to start with . The most important function of all index, which is a fungible 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 far more in depth than what is video can offer, but it promise for 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. That's never advantage or used the Falcon reuse a measure not only in multiple pivot tables but also in other measures. I can use it again and again again. It's only calculated once. Soto attendees coma. Trump Trophy, LTA Now, another functions coming in here began to use a function called date ad on Here it is by double click on this. This returns a table specified by expressions, and he allows us to shoot a number of intervals in the past or also in the future. Now the first thing it asks Forest where the dates are. They're in the calendar table. Date field, coma. Number of intervals minus 11 previous, it said a current one come up. What is the interval? It's a year. We do have the option of using intervals such as month on others. Closed bracket for dates. Add close bracket for calculate. Check the formula and then we specify numbers. Once gay, this is another whole number with a thousands 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 a 10 days. This press. Yeah, the difference to the previous year. So we created that previous measure which found out how many attendees in a previous year on. We can use that in pivot tables, etcetera. But I'm really on 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 10 days. I'm going to use that measure and subtract that previous measure. The attendees previous shit was the difference with how many and how many in the previous year. Check the formula. And here we go with whole number, use a thousands separator. Okay, that pivot table selected. So it's Bean added to it. Now what I'm going to do. Let's just remove some of these from the pivot table and it's check house somebody's work in so far. So if I waas to drag the course filled intervals, there's where courses like we saw earlier in the video. But it may be I'm going to drag in total attendees into values, and there's a total for all about course is much better than when how we saw it earlier in the video. It now clearly works. We've got a total a bottom 46 15,005. You may even remember that number familiar, but now we've got that field to context being applied in the pivot table. How many for the dashboard course Hanley for the B A. Having 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 is year from a Canada 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 is working or not. So here we can clearly see that the total attendees 2016 was 2906 and in the previous year we moved 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 number, whether it's right or not. But although this is not necessarily an amazing report, right now is double check in my Dax before I take it much further and talking that much further, it's click outside off that pivot table and create a final measure, and we found the difference. This time I want to know percentage difference. So attendees scented death Prev year, according it on the formula is going to use a function called Divide. Once again, this is a decks 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. Where is Excel sometimes? Producers def slash zero Ever. This gives us an alternate response to that, So I'm doing the attendees difference previous year by 10 days. Prevue. They are my numerator denominator. Common zero is an alternate response to avoid the era closer bracket and for something different this time as well. It's a number again that the formats will be a percentage. I don't want any decimals, and I don't need a thousands separator clicking. OK, that measure is added as 1/5 and final measure for the video to the 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.
8. Create PivotTables and Slicers using the Model: So I just wanted to create a few more pivot tables to Philly demonstrate that reusing on the formatting behind our Dax measures. So off still got this pivot table on screen at the moment, and I just wants to a couple off quick things to this. I'm going to right mouse click on the pivot on coming to the options, and I want to turn off the auto fitting of column rips. That could be really frustrating when we bring the slice ring 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 will come up to the analyzed tab, and I'll give it a decent name, such as a 10 days by a year on our thinking. This pivot table, I can now remove the attendees previous year measure on, bring in something a little bit more useful. Such us. The difference to the previous year said it. Now we can see that there are 842 more attendees. 2017 competitive 16 free 94 18 compared to 2017. And as I mentioned earlier in the video, you know don't expect on Amazing Dashboard in a few minutes from going to dedicate to this . It's just some quick examples of any measures in action on the relationship between and multiple tables, and it's resize that column. Okay, today's one. Let's take a copy off that pivot. Let's have another one about here. I will give it a decent name. This one is going to be about courses. So attendees, by course, Andi, 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 day to table running. So good information from a calendar table in the first pivot table of the two measures running now get information from the courses table with the two measures running so we can see how strong those two relationships are. Now, with this 2nd 1 on may decide to bring in the percentage difference and notice how the full matin is already applied to it. We mentioned how the formatting in the pivot table so the format of the measure was applied when it was created. So whenever I drag it into a measure and we can see measures being reused here is well in mawr than one pivot table for matins taken care off and we can reuse the same calculation. What? I was only applied once now wanted to bring in one mawr pivot tables. 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 wrong the number of courses by course. So quick name in here. Please don't judge me too much on the name in. Gonna get rid of Toto attendees on these two differences on just bringing their measure. We used when we did the count roads. It's not conceive how many off 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 on. I'm going to bring in a slice of quickly and this is going to have the venue now. I can't see the venue in the list of tables at the moment. See calendar RC courses. I see data. Where's the venue and when you look to the top, there is an old tack. At the moment, it's only Shoni active tables. All three of those have been used with monks. Jeffrey Pivot tables, but use old now I see venue. I could bring in the venue filled click OK, and a good slicer. Let me quickly formatted differently. And if I use that, is currently filtering that first pivot table. But I can come up to reports options, and I could hook 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. Let's go in, create another slicer now for this one. I don't want the next slice of interacting with the years going to keep that set wrong to select a second slicer. So your second pivot tape bring in a slicer, and that's because this one is going to be about the year, so I don't It's reacting this my screen when it reacting with the years pivot, let's make it work. Attendees by course, a 10 number courses by course. So I choose 2017. I concede that we ran six Excel formulas, um, functions, courses only two best practice and tips courses. And to be I introduction courses in 2018. Okay, NASA data for that, etcetera. And this second slice was just function under last to pivot tables, the first ones controlling all free. And although this a crude example knocked up in just a few minutes to showcase all four tables in a model working together, whether they be in a pivot table through measures or fruit slices have been see the relationship between them and the field to context being applied by the years on and the courses in our pivot tables. So hopefully this has been a useful demonstration off how you can use power pivots. Bring day, Torri, whatever. Technically power query to do that, model it and then produce some analysis in the back, it