1 00:00:00,330 --> 00:00:05,820 In the last chapter, I introduced Power Pivot by setting up some basic relationships and using some 2 00:00:05,820 --> 00:00:08,340 pivot tables to extract data related to them. 3 00:00:09,150 --> 00:00:15,810 We created some flexible some and count of calculations that summarized our data without any messy chain 4 00:00:15,810 --> 00:00:17,160 formulas or workups. 5 00:00:18,220 --> 00:00:23,740 As part of that chapter, I gave you a brief 30 second run through of the power pivot ribbon and we 6 00:00:23,740 --> 00:00:26,870 use it to market date table in this chapter. 7 00:00:27,040 --> 00:00:30,590 We'll get into the Excel data model, separate interface in more detail. 8 00:00:30,760 --> 00:00:32,590 So let's go take a quick tour of it. 9 00:00:33,310 --> 00:00:40,210 The data model can be accessed either through the data tab and the managed data model button or on the 10 00:00:40,210 --> 00:00:44,200 power pivot tab on the manage data model button here. 11 00:00:44,920 --> 00:00:48,640 Clicking on either one will open up the power pivot interface. 12 00:00:49,620 --> 00:00:55,560 The power of interface is similar to the power query interface, and that is its own separate window 13 00:00:55,560 --> 00:00:56,700 with its own ribbon's. 14 00:00:57,180 --> 00:01:02,730 However, it doesn't have as many features as power query does, and it doesn't have nearly as many 15 00:01:02,730 --> 00:01:04,950 commands as Excel does along its rivet. 16 00:01:05,640 --> 00:01:13,380 The home tab is primarily for accessing data from external data sources, for formatting data and for 17 00:01:13,380 --> 00:01:15,560 sorting data and viewing it differently. 18 00:01:16,860 --> 00:01:24,240 Here we have access to data for you, diagram, view and some hidden options, the data diagram, if 19 00:01:24,240 --> 00:01:29,490 you will, alternate between a diagram view of our data, which shows the relationships in the tables 20 00:01:29,910 --> 00:01:35,220 and a data view which shows us our tables and the values within the show. 21 00:01:35,220 --> 00:01:40,950 Hidden Button toggles whether or not you can see hidden tables, you can hide tables as necessary to 22 00:01:40,950 --> 00:01:43,800 limit how many data pieces show up. 23 00:01:43,800 --> 00:01:46,710 In your data view, we will not be hiding any data. 24 00:01:47,740 --> 00:01:56,050 The calculation area toggles on and off this extra little area below our data tables where we can visualize 25 00:01:56,050 --> 00:02:01,030 measures and other computations that we'll use throughout the rest of this course. 26 00:02:02,900 --> 00:02:09,590 On the design tab, we have a few more functional features, the first thing to note is this is where 27 00:02:09,590 --> 00:02:15,080 our mark date table option is, as well as another option for creating data tables that we'll look at 28 00:02:15,080 --> 00:02:15,580 shortly. 29 00:02:16,010 --> 00:02:20,030 We can create and manage our relationships from here just as we can and excel. 30 00:02:20,450 --> 00:02:27,110 And we have some commands for adding new columns, managing our columns, visibility with Free's and 31 00:02:27,110 --> 00:02:31,100 finding and inputting functions, as well as managing our calculation options. 32 00:02:31,910 --> 00:02:39,040 The advanced tab has more complex components, including perspectives and reporting properties. 33 00:02:39,560 --> 00:02:42,890 We won't be going into these features of power pivot in this course. 34 00:02:43,370 --> 00:02:47,810 In addition to the ribbon, the power window has a few other features. 35 00:02:48,230 --> 00:02:51,680 Notably, we have our column dropdown on the left. 36 00:02:52,160 --> 00:02:57,860 This is very similar to the dropdown field in Excel to the top left that allows you to pick the name 37 00:02:57,860 --> 00:02:59,240 ranges here. 38 00:02:59,240 --> 00:03:02,790 We can pick any column and jump to it within our data set. 39 00:03:03,080 --> 00:03:06,620 This could be helpful if you have a large dataset with many, many columns. 40 00:03:07,250 --> 00:03:09,080 Additionally, we have a formula. 41 00:03:09,080 --> 00:03:10,510 Borro here along the top. 42 00:03:10,910 --> 00:03:16,490 This works very much like Excel Formula Bar, where you can write in formulas instead of writing. 43 00:03:16,490 --> 00:03:19,340 Excel formulas will be writing tax formulas here. 44 00:03:20,030 --> 00:03:26,420 When we do this with an ADD column, it's a calculated column and we'll discuss calculated columns versus 45 00:03:26,420 --> 00:03:33,380 calculated measures in this chapter at the bottom of our power pivot screen, we have the series of 46 00:03:33,380 --> 00:03:35,810 tables that have been added to our data model. 47 00:03:36,200 --> 00:03:43,040 We can click on any one of these to jump to that data table and see what its data looks like below this 48 00:03:43,040 --> 00:03:44,410 list of tables. 49 00:03:44,660 --> 00:03:51,590 We also identify the record that we're on and the number of records, and we can iterate through each 50 00:03:51,590 --> 00:03:57,350 of our records one by one if we desire, or we can go to the very end or very beginning. 51 00:03:58,270 --> 00:04:03,200 This is most useful for informational purposes to see how much data is in the table. 52 00:04:03,520 --> 00:04:08,340 I can't think of any particular examples where I actually iterate through one by one. 53 00:04:08,890 --> 00:04:13,990 Finally, to the bottom right, we have our grid and our diagram patterns. 54 00:04:14,500 --> 00:04:20,220 These correspond to the data view and diagram a few buttons on our home ribbon. 55 00:04:20,800 --> 00:04:25,750 So if we click on the diagram for you, we get our diagram for you here. 56 00:04:26,170 --> 00:04:29,290 If we click on the grid view, we get our data view. 57 00:04:29,860 --> 00:04:35,980 And then, as with most other windows, you can stretch it and shrink it as necessary with your corner 58 00:04:35,980 --> 00:04:36,410 button. 59 00:04:36,850 --> 00:04:40,230 Aside from that, this covers the power pivot interface. 60 00:04:40,240 --> 00:04:46,690 So let's go ahead and close out of this and let's take a quick tour of the power pivot ribbon with an 61 00:04:46,690 --> 00:04:47,230 Excel. 62 00:04:47,770 --> 00:04:51,980 The power pivot ribbon in Excel has a few extra commands that will find useful. 63 00:04:52,600 --> 00:04:57,920 Most notably are the measures and KPI drop downs will be using these extensively. 64 00:04:58,750 --> 00:05:01,710 Additionally, there's the add to data bottom button. 65 00:05:02,290 --> 00:05:08,430 This button will add any specified table directly to the data model and is a fast way of taking data 66 00:05:08,440 --> 00:05:11,920 that's in Excel and making it accessible to your data model. 67 00:05:12,580 --> 00:05:18,340 To detect Button is a shortcut to allowing you to detect the relationships in your currently specified 68 00:05:18,340 --> 00:05:19,030 pivot table. 69 00:05:19,060 --> 00:05:25,060 So if I have a cell selected in a pivot table and I click on Detect, I get my relationships detection 70 00:05:25,060 --> 00:05:25,510 window. 71 00:05:26,200 --> 00:05:30,160 Finally, there's a settings button with some advanced settings that we won't be changing. 72 00:05:31,010 --> 00:05:34,060 That's a quick tour of our power pivot interface. 73 00:05:34,510 --> 00:05:40,760 So let's start getting into the power pivot features and doing some computations and creating some measures 74 00:05:40,790 --> 00:05:45,700 that we can take our pivot tables and make them far more flexible and far more powerful.