1
00:00:00,390 --> 00:00:04,960
Before we start the course, why don't I demonstrate how to apply power, pivot to some data.

2
00:00:05,700 --> 00:00:10,740
This video will have some dense content, but don't worry, I will guide you through every step that's

3
00:00:10,740 --> 00:00:13,410
covered in this video at different points in the course.

4
00:00:13,860 --> 00:00:16,890
If you work with Excel, you're probably familiar with the old.

5
00:00:17,130 --> 00:00:21,120
I need a report that summarizes these three different data pieces request.

6
00:00:21,630 --> 00:00:27,450
And naturally, those three data sets just have to be structured a little bit differently, enough so

7
00:00:27,450 --> 00:00:31,000
that you need to use some FS to summarize the data across each source.

8
00:00:31,920 --> 00:00:33,480
So what have we been asked for?

9
00:00:35,020 --> 00:00:39,020
Well, our boss has asked for a summary of net profit by store, location by month.

10
00:00:39,460 --> 00:00:40,510
Sounds easy, right?

11
00:00:41,170 --> 00:00:46,300
I mean, this isn't hard, but it will take a lot of time, plus a really solid idea as to what you

12
00:00:46,300 --> 00:00:48,080
want to do before you actually do it.

13
00:00:48,730 --> 00:00:52,540
We need to split the month and year off of our dates for at least two of our tables.

14
00:00:52,870 --> 00:00:57,330
And then we'll need to create our summary table layout with this summary table.

15
00:00:57,430 --> 00:01:03,310
We're finally ready to write some really, really long formulas using some s, which is a fairly advanced

16
00:01:03,310 --> 00:01:04,270
function itself.

17
00:01:05,320 --> 00:01:08,630
Now, our data isn't that complex, so isn't there a better way?

18
00:01:09,440 --> 00:01:15,670
Well, as the course name implies, I will show you a better and easier way with power pivot with no

19
00:01:15,670 --> 00:01:18,250
need to understand these pesky Sonmez functions.

20
00:01:19,240 --> 00:01:20,450
So how can we do this?

21
00:01:21,190 --> 00:01:23,770
Step one is to structure our data and data tables.

22
00:01:23,890 --> 00:01:29,890
As I showed in the review session, this can be done by selecting the data processing control t pressing

23
00:01:29,890 --> 00:01:30,790
OK in the pop up.

24
00:01:30,790 --> 00:01:35,400
That appears once all of our data is in tables and don't forget to name them.

25
00:01:35,830 --> 00:01:37,510
I need some dimensional tables.

26
00:01:38,110 --> 00:01:43,300
The first dimensional table I'll create is the dates table with the list of appropriate dates plus fields

27
00:01:43,300 --> 00:01:45,220
describing the year, month and day.

28
00:01:45,550 --> 00:01:47,500
And of course I'll make it a data table.

29
00:01:48,040 --> 00:01:52,210
I'll explain what a dimensional table is early in the course and discuss why we do this.

30
00:01:53,080 --> 00:01:56,410
The other dimensional table will create will list are unique store names.

31
00:01:57,500 --> 00:02:02,300
To do this are simply paste to store answer from one of my other sheets into a blank workbook, go to

32
00:02:02,300 --> 00:02:05,720
the data tab with my ribbon and use to remove duplicates command.

33
00:02:06,780 --> 00:02:12,510
I'll make this a table and call it stores, and now I'm ready to find my power, private relationships.

34
00:02:13,640 --> 00:02:19,140
On the data tab, there is a button called relationships that I will click on to launch a new window.

35
00:02:19,790 --> 00:02:24,860
I'm going to go quickly in this demonstration, but will guide you through each of these steps and explain

36
00:02:24,860 --> 00:02:26,710
the concepts in full detail later.

37
00:02:27,590 --> 00:02:31,550
I needed to find six new relationships, two for each of our tables.

38
00:02:32,150 --> 00:02:37,610
For my first relationship, I'm going to connect the labor table with its month column to the calendar

39
00:02:37,610 --> 00:02:38,870
tables dates column.

40
00:02:39,830 --> 00:02:45,410
This tells Power Pivot that the month column from the labor table contains the same values as the calendar

41
00:02:45,410 --> 00:02:46,560
tables dates column.

42
00:02:47,550 --> 00:02:52,790
Further, it says that our calendar table can be used to look up specific information from the labor

43
00:02:52,790 --> 00:02:54,470
table to you.

44
00:02:54,480 --> 00:02:56,750
This might seem obvious, of course.

45
00:02:56,750 --> 00:03:02,720
The date in the calendar table and the date in the labor table are the same, but we're laying out the

46
00:03:02,720 --> 00:03:06,060
specific details in a way that power pivot can connect them together.

47
00:03:06,770 --> 00:03:11,720
I'll do this for both of the other tables with their date field and then I'm going to repeat the process

48
00:03:11,720 --> 00:03:16,370
for all three tables and connect them via the stores table and it's Starfield.

49
00:03:17,720 --> 00:03:23,870
These relationships all tell power how to connect the three tables together through two dimensional

50
00:03:23,870 --> 00:03:24,410
tables.

51
00:03:25,490 --> 00:03:31,130
Once we've defined these relationships, Power Pipit will work a bunch of magic in the background and

52
00:03:31,130 --> 00:03:33,650
all we need is a pivot table to take advantage of it.

53
00:03:34,550 --> 00:03:39,800
So on a clean sheet, I'm going to insert a new pivot table that uses this Workbox data model as the

54
00:03:39,800 --> 00:03:40,510
data source.

55
00:03:41,540 --> 00:03:47,870
When I do this, I get my usual pivot table fields window with a slight twist Apple store from the stores

56
00:03:47,870 --> 00:03:54,800
table and added a zero entry drop a month from the calendar table and add it to in the value section.

57
00:03:54,800 --> 00:03:59,810
I'll add the sales from the sales table, the purchase orders from the purchase orders table and the

58
00:03:59,810 --> 00:04:03,190
employee wages from the employee table to the.

59
00:04:03,590 --> 00:04:09,110
We now have a pivot table with all of the information from our complicated summit summary displayed

60
00:04:09,110 --> 00:04:10,510
at the beginning of this video.

61
00:04:11,060 --> 00:04:16,110
We created this in half the time with half the trouble, with twice as many features.

62
00:04:16,730 --> 00:04:19,670
So now are you ready and eager to learn how to use power?

63
00:04:19,670 --> 00:04:20,000
Pivot.
