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.
