1
00:00:00,450 --> 00:00:06,150
This lecture will give you a good overview about some of power queries very useful and simple to use

2
00:00:06,150 --> 00:00:07,110
features.

3
00:00:07,230 --> 00:00:12,780
After this lecture you should have a good feeling on what power query can do to transform your own

4
00:00:12,780 --> 00:00:17,280
data and if it makes sense for you to dig deeper in this topic.

5
00:00:17,280 --> 00:00:23,000
The aim of the power query section in the course is to show you the potential of power query.

6
00:00:23,100 --> 00:00:25,950
Let's take a look at an example right here.

7
00:00:25,980 --> 00:00:30,690
Our aim is to transform this data into a tabular dataset.

8
00:00:30,780 --> 00:00:33,890
This is currently like a report that we received.

9
00:00:33,900 --> 00:00:41,690
Imagine we receive this from an SAP add-in or an Oracle add-in and it comes in this format.

10
00:00:41,700 --> 00:00:45,440
There is nothing we can do about the way we receive this.

11
00:00:45,440 --> 00:00:52,200
Now we wouldn't be able to be flexible on our reporting. Because let's say we want to create a report

12
00:00:52,230 --> 00:00:56,170
that shows the divisions. Right now to be able to do that

13
00:00:56,280 --> 00:01:03,870
we have to sum up the values for division. And notice in the headers here, we actually have three of them.

14
00:01:03,870 --> 00:01:11,220
We have the date, we have the scenario whether it's actual, outlook, or budget, and the type of KPI we are

15
00:01:11,220 --> 00:01:14,790
reporting whether it's revenue, profit, or cash.

16
00:01:14,850 --> 00:01:18,430
Our apps are sitting right here and our divisions are sitting right here.

17
00:01:18,450 --> 00:01:24,870
So to be able to do different types of report on this using formulas will be very difficult. Because

18
00:01:24,870 --> 00:01:31,740
it's going to be very difficult to use SUMIFS on this if our productivity apps is missing some cells here.

19
00:01:31,740 --> 00:01:38,080
We also can't create a pivot table out of this dataset because it's not a tabular data set.

20
00:01:38,220 --> 00:01:39,670
Let me show you the end result.

21
00:01:39,670 --> 00:01:42,800
So you have a good idea on where we want to get to.

22
00:01:42,840 --> 00:01:44,790
That's the final table we want to have.

23
00:01:44,790 --> 00:01:49,080
So we have a separate column for date, a separate one for scenario.

24
00:01:49,080 --> 00:01:57,770
Here we have actual outlook and budget information. One for KPI, division, app, and the final value.

25
00:01:57,780 --> 00:02:05,110
So here we just have one column for value whereas here our values are all over the place.

26
00:02:05,160 --> 00:02:11,310
Once we have the data in a tabular format it's very easy to create reports out of that.

27
00:02:11,310 --> 00:02:17,710
We can create a pivot table out of it and we can also use formulas like SUMIFS on that

28
00:02:17,710 --> 00:02:18,420
data set.

29
00:02:18,420 --> 00:02:24,870
Now we've already learned some ways we can clean this data using standard Excel features. So we can fill

30
00:02:24,870 --> 00:02:30,450
up these empty cells here using a technique we already learned in the course. But it's going to be very

31
00:02:30,450 --> 00:02:36,900
difficult to do some of the steps. For example, splitting these into their own columns and transforming

32
00:02:36,930 --> 00:02:39,930
all these values into one column.

33
00:02:39,930 --> 00:02:41,880
Let's do that with power query.

34
00:02:41,880 --> 00:02:44,190
First off let's go to the Data tab.

35
00:02:44,190 --> 00:02:46,530
We want to take the data from here.

36
00:02:46,530 --> 00:02:51,380
So one option is to transform this data into an Excel table.

37
00:02:51,380 --> 00:02:57,570
Just press Control+T and say OK but then our data set will look like this.

38
00:02:57,600 --> 00:03:03,540
Now obviously we can take away the formatting but the table always needs columns here. And let's say this

39
00:03:03,540 --> 00:03:09,930
is not something we want to do to our dataset we actually want to keep the look like this.

40
00:03:09,960 --> 00:03:12,460
We don't want to add those additional columns.

41
00:03:12,480 --> 00:03:16,070
Another option is to use a range.

42
00:03:16,080 --> 00:03:18,890
And if I hover over this, let's read the instruction.

43
00:03:18,900 --> 00:03:23,370
It says we can use the selected Excel table or a named range.

44
00:03:23,670 --> 00:03:30,530
We basically just have to give the range of our data a name. And if were planning to have more information

45
00:03:30,540 --> 00:03:38,310
let's say we want to add in March, April, and so on. We can take in more columns into account.

46
00:03:38,310 --> 00:03:44,910
And it doesn't matter if we forget to take in a lot of extra columns, we can always update the named

47
00:03:44,910 --> 00:03:46,800
range later on as well.

48
00:03:46,830 --> 00:03:49,380
And our query will update automatically.

49
00:03:49,440 --> 00:03:55,940
In this example I'm just going to go until column AC, highlight this and give the range a name.

50
00:03:56,130 --> 00:04:00,160
I'm going to call it MessyData and press enter.

51
00:04:00,340 --> 00:04:00,650
Okay.

52
00:04:00,660 --> 00:04:05,370
Now that we've given it a name, we want power query to use this name.

53
00:04:05,850 --> 00:04:11,760
Notice if I'm just sitting in a cell and I click on From table range, what Excel tries to do is to

54
00:04:11,760 --> 00:04:13,520
transform this data to a table.

55
00:04:13,560 --> 00:04:14,590
But I don't want that.

56
00:04:14,610 --> 00:04:17,079
I want it to take in my named range.

57
00:04:17,130 --> 00:04:22,079
So I have to select my named range first before I click on that button.

58
00:04:22,260 --> 00:04:29,070
So that named range was called MessyData, select, and then click. Now Excel knows that we want to bring

59
00:04:29,070 --> 00:04:31,010
in that name MessyData.

60
00:04:31,170 --> 00:04:36,090
But here first thing I'm going to do is change this name. Because this is going to be the name of the

61
00:04:36,090 --> 00:04:41,490
final result and that's not going to be messy. It's going to be clean data.

62
00:04:41,490 --> 00:04:45,260
Next thing Excel tries to figure out what to do with these columns.

63
00:04:45,420 --> 00:04:47,500
We don't need that change type here.

64
00:04:47,580 --> 00:04:52,920
I'm going to get rid of it because later once we have our actual columns, we're going to define the type

65
00:04:53,040 --> 00:04:57,990
at a later stage. But it's no problem if you leave it in there as well. So let's take a look at some

66
00:04:57,990 --> 00:05:02,090
of the steps. Number one, let's fill up these empty ones.

67
00:05:02,090 --> 00:05:03,880
There's a great feature for this.

68
00:05:03,880 --> 00:05:07,730
You find it under transform and it's called "Fill".

69
00:05:07,750 --> 00:05:11,550
I'm just going to highlight the column I want to fill, click on fill.

70
00:05:11,560 --> 00:05:13,680
Do we want to fill down or up?

71
00:05:13,810 --> 00:05:19,360
We want to fill down. Because we want to copy these all the way down until the next name.

72
00:05:19,570 --> 00:05:23,650
Well, how do we get separate columns for each of these?

73
00:05:23,650 --> 00:05:32,170
Well, what I'm planning to do is to transpose this dataset. So that I actually bring these to their own

74
00:05:32,260 --> 00:05:33,450
columns here.

75
00:05:33,670 --> 00:05:39,700
But before I do that I want to merge these two columns because I just want to have one header.

76
00:05:39,700 --> 00:05:41,580
I'm going to highlight this column,

77
00:05:41,590 --> 00:05:48,670
hold down Control, highlight the other one, and click on "Merge Columns". What separator do I want

78
00:05:48,670 --> 00:05:51,100
in between? Now you can click on any of these.

79
00:05:51,130 --> 00:05:54,230
I'll just go with semicolon and click on OK.

80
00:05:54,310 --> 00:06:00,810
Now I have one merged column that has my division and then it has the app.

81
00:06:01,120 --> 00:06:04,090
What we're going to do now is to transpose this.

82
00:06:04,270 --> 00:06:06,120
Just click on transpose.

83
00:06:06,400 --> 00:06:13,440
It's going to put my merged column up here as its own header and it's going to give me these.

84
00:06:13,510 --> 00:06:17,990
So the date scenario and the KPI in separate columns.

85
00:06:17,990 --> 00:06:20,730
Now obviously this dataset is very long.

86
00:06:20,770 --> 00:06:22,080
It's not my final result.

87
00:06:22,120 --> 00:06:25,560
It's just an intermediate step.

88
00:06:25,600 --> 00:06:32,080
Next step, I can give these headers. But before I do that I need to promote this line to the header. And I can do

89
00:06:32,080 --> 00:06:36,070
that under the Home tab "Use First Row as Headers".

90
00:06:36,070 --> 00:06:38,470
Now I can name these the way I want.

91
00:06:38,500 --> 00:06:44,420
The first one is date, press enter. I can define the type for this. I don't need the time.

92
00:06:44,560 --> 00:06:49,840
I'm going to go and change this to just date. The second one: actual, budget.

93
00:06:49,840 --> 00:06:53,380
This is my scenario press enter, type is fine.

94
00:06:53,410 --> 00:06:54,430
The third one:

95
00:06:54,430 --> 00:06:55,920
That's my KPI

96
00:06:56,140 --> 00:06:58,100
and type is fine.

97
00:06:58,100 --> 00:07:06,320
Now here I have the division and my app. And now what I'm going to do is to bring these into the columns.

98
00:07:06,550 --> 00:07:12,730
There is a great feature under transform that's called Unpivot columns and that's exactly what it

99
00:07:12,730 --> 00:07:13,160
does.

100
00:07:13,180 --> 00:07:16,670
It brings your headers into the data set.

101
00:07:16,710 --> 00:07:23,740
Now I have the option to either highlight all of these and unpivot but because it's so many, I can do it

102
00:07:23,740 --> 00:07:30,550
the other way round. I can click the ones I want to keep and then go to unpivot columns and say "Unpivot

103
00:07:30,640 --> 00:07:34,460
Other Columns". Which means unpivot all of these.

104
00:07:34,480 --> 00:07:42,070
Now I get these in the columns. I have one single column for my value which is great. But these would

105
00:07:42,070 --> 00:07:46,560
be better to have them in separate columns. And just take a look at this.

106
00:07:46,600 --> 00:07:49,970
I can split the column by delimiter.

107
00:07:50,140 --> 00:07:51,220
What is the delimiter?

108
00:07:51,250 --> 00:07:53,680
It's semicolon, click on OK.

109
00:07:53,920 --> 00:07:55,090
Give these names.

110
00:07:55,120 --> 00:07:57,620
This is my division, press enter.

111
00:07:57,910 --> 00:08:01,350
This one is the app, type is fine for these.

112
00:08:01,450 --> 00:08:05,250
This one I'm going to change to currency and that's it.

113
00:08:05,260 --> 00:08:07,360
All the steps are recorded here.

114
00:08:07,360 --> 00:08:13,930
I can go back and take a look at each step if I want. My end result is this.

115
00:08:13,990 --> 00:08:21,780
Now I'm going to go and load it. So let's go back to home, close & load, close & load to. I could select

116
00:08:21,780 --> 00:08:26,040
a pivot table if I want that directly creates a pivot table out of this.

117
00:08:26,040 --> 00:08:28,330
But let's put this one in a table.

118
00:08:28,340 --> 00:08:32,710
We could decide to make a pivot table out of this as well at a later stage.

119
00:08:32,789 --> 00:08:34,340
So I'm going to go with ok

120
00:08:34,590 --> 00:08:41,169
and that's my proper dataset. 720 rows were loaded.

121
00:08:41,220 --> 00:08:44,900
Now let's take a look at what happens when we get March data.

122
00:08:45,060 --> 00:08:49,530
Let's go back to our messy data set and let's assume we get new information.

123
00:08:49,570 --> 00:08:57,210
I'm just going to copy this, paste it right here and just change this to the next month. And pull this

124
00:08:57,360 --> 00:08:58,530
across.

125
00:08:58,530 --> 00:09:05,850
Now all I have to do is go back to my report. Let's actually call this clean data and just check

126
00:09:05,850 --> 00:09:06,840
how this gets updated.

127
00:09:06,840 --> 00:09:15,300
Right now I have 720 rows loaded, one row for the header. Let's right mouse click and refresh.

128
00:09:15,300 --> 00:09:16,200
Take a look at this.

129
00:09:16,200 --> 00:09:20,260
It's loading, 1,080 rows were loaded.

130
00:09:20,270 --> 00:09:23,610
So that's my March data in there automatically.

131
00:09:23,610 --> 00:09:29,220
So we make a pivot table out of this. All we have to do is refresh the pivot table and we have the new

132
00:09:29,220 --> 00:09:36,900
months in there. All the transformation was applied to the data. Now one last thing,

133
00:09:36,910 --> 00:09:39,360
let's say you want to make a slight change to this.

134
00:09:39,430 --> 00:09:43,570
You are not so happy that all the values are in one column.

135
00:09:43,570 --> 00:09:48,230
You want to have a separate column for revenue, profit, and cash.

136
00:09:48,310 --> 00:09:49,720
Let's go and update our query.

137
00:09:49,720 --> 00:09:53,020
I'm just gonna double click to edit the query.

138
00:09:53,020 --> 00:09:59,150
I'm going to select the KPI column and hold down control and select the value column

139
00:09:59,380 --> 00:10:06,130
and now instead of unpivoting I'm actually going to pivot the column. Which is going to put this data

140
00:10:06,550 --> 00:10:09,210
into the headers.

141
00:10:09,210 --> 00:10:13,400
It tells me here use the names in column KPI to create new columns.

142
00:10:13,450 --> 00:10:14,260
That's fine.

143
00:10:14,410 --> 00:10:18,250
And it's going to take the values from the value column, which is great.

144
00:10:18,260 --> 00:10:19,390
Click on OK.

145
00:10:19,630 --> 00:10:22,870
I have a separate column for revenue, profit, and cash.

146
00:10:22,990 --> 00:10:24,590
All steps are recorded.

147
00:10:24,640 --> 00:10:26,410
Let's go and update our query.

148
00:10:26,440 --> 00:10:34,360
Just close and load, it's loading and I have my updated query. So that's how easy it is to use some

149
00:10:34,360 --> 00:10:38,440
of power query's simple features to transform your data sets.