1 00:00:05,030 --> 00:00:07,580 We have our sample data set here. 2 00:00:07,580 --> 00:00:12,220 It includes division and region information in one column. 3 00:00:12,260 --> 00:00:14,720 We have app and profit information. 4 00:00:14,720 --> 00:00:18,680 Notice also we have totals in the middle of our dataset. 5 00:00:18,740 --> 00:00:25,920 Now our aim is to create a pivot table report that has profit information by division. 6 00:00:25,970 --> 00:00:28,020 So just profit by division. 7 00:00:28,190 --> 00:00:30,320 We don't need region at all. 8 00:00:30,320 --> 00:00:35,480 If we just convert this data set into a table and insert a pivot table, we're going to have problems 9 00:00:35,480 --> 00:00:35,810 right? 10 00:00:35,810 --> 00:00:43,310 Because we have total values here hardcoded in the middle. And we just have the division and region information 11 00:00:43,550 --> 00:00:45,560 sitting inside one column. 12 00:00:45,560 --> 00:00:52,940 So what we're going to do is to use power query or get and transform to clean up this data set before 13 00:00:53,030 --> 00:00:54,950 we insert our pivot table. 14 00:00:54,950 --> 00:00:56,390 Let's get started. 15 00:00:56,390 --> 00:00:59,120 Number one: Convert the data below to a table. 16 00:00:59,120 --> 00:01:00,800 Call it RawDiv. 17 00:01:00,800 --> 00:01:03,930 Easy, right? Click anywhere inside the data set. 18 00:01:04,040 --> 00:01:06,720 Use the shortcut key Control+T. 19 00:01:06,800 --> 00:01:11,120 You can also go to the Insert tab and insert a table from there. 20 00:01:11,120 --> 00:01:11,940 I'm gonna go with 21 00:01:11,960 --> 00:01:12,660 Ok. 22 00:01:12,710 --> 00:01:19,690 First thing I'm going to do is remove the table style to go back to my original formatting and I'm going 23 00:01:19,690 --> 00:01:23,570 to call this RawDiv, press enter. 24 00:01:23,570 --> 00:01:25,330 Number one is done. 25 00:01:25,340 --> 00:01:32,330 Number two: We need to load to power query and clean up the data by: One is to remove these total fields 26 00:01:32,330 --> 00:01:32,930 here 27 00:01:32,930 --> 00:01:36,830 And two we want to split the first column into two columns. 28 00:01:36,920 --> 00:01:40,520 One for division and the other for region. 29 00:01:40,580 --> 00:01:43,220 Let's load first into power query. 30 00:01:43,220 --> 00:01:49,460 So go to the Data tab and under the get and transform data section, click on From table. 31 00:01:49,460 --> 00:01:54,440 This opens up the power query editor. So we had two tasks to do. 32 00:01:54,440 --> 00:01:56,790 One was to remove the total value. 33 00:01:56,990 --> 00:01:58,910 I'll start with this one first. 34 00:01:58,940 --> 00:02:03,050 Now notice that the second column is always empty 35 00:02:03,050 --> 00:02:04,960 whenever we have totals here. 36 00:02:05,090 --> 00:02:10,240 So one way for this data set is to remove the rows where app is empty 37 00:02:10,610 --> 00:02:15,920 But let's look at an option that we didn't look at before. And that's we're going to filter for this 38 00:02:15,920 --> 00:02:22,040 column but only include rows that don't have the word "total" in them. 39 00:02:22,040 --> 00:02:23,990 Click on this down button here. 40 00:02:24,050 --> 00:02:29,180 Go to text filters and let's go with does not contain. 41 00:02:29,180 --> 00:02:32,170 Right here, I'm going to type in total and click on 42 00:02:32,220 --> 00:02:32,770 Ok. 43 00:02:32,810 --> 00:02:38,450 My list is filtered to everything that doesn't include the word total in it. 44 00:02:38,450 --> 00:02:41,530 Now our second task was to split this column. 45 00:02:41,530 --> 00:02:48,020 So I'm going to highlight this and go with split column, by delimiter. Because the delimiter here is a 46 00:02:48,020 --> 00:02:49,160 dash sign. 47 00:02:49,250 --> 00:02:55,190 Excel automatically picked up the dash so it's giving me the custom option and it already pushed the 48 00:02:55,190 --> 00:02:57,050 dash sign in there. 49 00:02:57,050 --> 00:02:57,800 I'm going to go with 50 00:02:57,800 --> 00:02:58,420 Ok. 51 00:02:58,430 --> 00:03:00,700 But there is a problem here. 52 00:03:00,710 --> 00:03:06,370 Can you guess what it is? I still have a space included. 53 00:03:06,400 --> 00:03:14,290 If I click on this Australia and I highlight this, there is one space just before Australia. If I click 54 00:03:14,290 --> 00:03:19,670 on game here and highlight this, notice there is one space after game. 55 00:03:19,840 --> 00:03:25,330 So my delimiter wasn't just the dash sign it was "space Dash space". 56 00:03:25,330 --> 00:03:28,850 Let's make sure we remove these trailing spaces. 57 00:03:28,900 --> 00:03:35,770 I'm going to delete that last step and for this split column by delimiter, I'm going to go and edit 58 00:03:35,770 --> 00:03:36,190 it. 59 00:03:36,280 --> 00:03:39,000 I can remove it completely and redo it. 60 00:03:39,010 --> 00:03:43,230 Or, I can go to edit here and update my delimiter. 61 00:03:43,320 --> 00:03:48,730 All I need to do is add a space before and add a space after and then click on 62 00:03:48,750 --> 00:03:49,480 OK. 63 00:03:49,590 --> 00:03:53,700 Now let's just double check that this works, if I highlight Australia 64 00:03:53,700 --> 00:03:55,920 there is no space before it. 65 00:03:55,920 --> 00:03:59,840 If I highlight game, there is no space after this. 66 00:03:59,970 --> 00:04:02,610 Let's just double check the data types here. 67 00:04:02,640 --> 00:04:09,600 So this is text, this is text. But I want to update the column header so just double click it and then 68 00:04:09,600 --> 00:04:16,040 let's just call this one division. Press enter, double click the second one. Call it 69 00:04:16,050 --> 00:04:19,399 Region, press enter, app is fine. 70 00:04:19,410 --> 00:04:21,930 Data type is fine. 71 00:04:22,350 --> 00:04:25,220 For profit, I'm gonna change this to the currency data type. 72 00:04:26,210 --> 00:04:28,730 All the steps are recorded here. 73 00:04:28,770 --> 00:04:35,340 They can be changed and revised any time. Now we want to get this data in a pivot table. 74 00:04:35,340 --> 00:04:42,610 So I'm going to go with close and load to. And directly load this into a pivot table report. 75 00:04:42,630 --> 00:04:47,130 If you don't have Office 365, you have Excel 2016. 76 00:04:47,130 --> 00:04:49,740 You don't have the pivot table option here. 77 00:04:49,740 --> 00:04:54,070 Just load your data into a table and then create a pivot table off of that. 78 00:04:54,180 --> 00:04:55,330 But I have the option. 79 00:04:55,350 --> 00:05:03,370 I'm going to select it. Bring my pivot table in the existing worksheet and it says here in cell F10. 80 00:05:03,440 --> 00:05:05,810 I'll just select that and click on 81 00:05:05,820 --> 00:05:06,460 Ok. 82 00:05:06,570 --> 00:05:10,570 Now I get my pivot table options opening up here. 83 00:05:10,770 --> 00:05:13,940 I'm just gonna bring this closer to our report. 84 00:05:13,940 --> 00:05:20,140 All I need to do now is to drag division here and to drag profit right here. 85 00:05:20,280 --> 00:05:24,950 And I have my report for division by profit. 86 00:05:24,960 --> 00:05:28,520 Of course, I will update this to make it easier to read. 87 00:05:28,560 --> 00:05:37,110 I'm going to remove these field headers. Call this profit, space, enter. And update the number formatting 88 00:05:37,170 --> 00:05:45,630 for this. I'm also going to automatically sort this from largest to smallest. 89 00:05:45,780 --> 00:05:48,650 So the division with the highest profit is Game. 90 00:05:48,750 --> 00:05:50,500 We actually did both 91 00:05:50,510 --> 00:05:53,230 Number two and number three. 92 00:05:53,280 --> 00:06:01,680 Now before we celebrate, let's just test this by adding a new division to this and making sure our report 93 00:06:01,770 --> 00:06:03,270 updates accordingly. 94 00:06:03,270 --> 00:06:11,330 Right on the bottom I'm going to add the health division. Just for Asia and add in this profit. 95 00:06:11,370 --> 00:06:18,900 I'm also going to adjust some things. Remember in our original report up here utility was last, game 96 00:06:18,960 --> 00:06:20,040 was first. 97 00:06:20,130 --> 00:06:24,080 I'm going to go and change this. For Atmos utility, 98 00:06:24,090 --> 00:06:26,800 I'm going to add in a very large number. 99 00:06:26,800 --> 00:06:28,230 So let's just go with this. 100 00:06:28,230 --> 00:06:31,680 Press enter. Now this data set has been updated. 101 00:06:31,770 --> 00:06:36,590 Our pivot report needs to be refreshed for these to take effect. 102 00:06:36,600 --> 00:06:41,540 Right mouse click, click on refresh. And we have utilities switch to the top. 103 00:06:41,610 --> 00:06:48,270 And we have our health division in here as well. Ok so challenge was completed and tested.