1 00:00:01,120 --> 00:00:04,880 Now let's take a look at how we can merge data in power query. 2 00:00:04,939 --> 00:00:10,220 So basically merge data from different tables into one report. 3 00:00:10,220 --> 00:00:15,290 The great thing with using power query is that you can merge data from different places. 4 00:00:15,290 --> 00:00:19,150 They don't all have to be Excel tables in the same workbook. 5 00:00:19,220 --> 00:00:22,790 You can have a text file like we had in the previous lecture. 6 00:00:22,910 --> 00:00:27,980 You can have one table sitting in the workbook and other data source sitting somewhere else. 7 00:00:28,100 --> 00:00:34,250 As long as you can create a connection to it with power query you can merge these data sources together. 8 00:00:34,280 --> 00:00:39,890 What we're going to do in this lecture is to build on what we did in the previous one. In the previous 9 00:00:39,890 --> 00:00:44,300 lecture we imported a text file that had this information. 10 00:00:44,300 --> 00:00:51,440 This is a query that's connected to our text file. And we can see that by going to data, queries and 11 00:00:51,440 --> 00:00:52,500 connections, 12 00:00:52,610 --> 00:00:53,690 that's our query. 13 00:00:53,690 --> 00:01:00,370 If we double click it, we open up power query and we can take a look at the steps that we applied here. 14 00:01:00,440 --> 00:01:08,090 Now what we're going to do is to merge this information together with this master data information. 15 00:01:08,090 --> 00:01:14,760 So let's say for the article code I actually want to use the description in my final pivot table. 16 00:01:14,780 --> 00:01:17,010 I don't have it in the source data. 17 00:01:17,390 --> 00:01:23,300 I just have the article code. I want to bring in the description in here as well. 18 00:01:23,310 --> 00:01:29,300 Now one way of doing this is to use the VLOOKUP function. After I imported the data here 19 00:01:29,360 --> 00:01:35,060 I can add a new column, use VLOOKUP and look up the data from the master data. 20 00:01:35,060 --> 00:01:42,110 Another way of doing this especially if you have a lot of data is to use power query. And the great thing 21 00:01:42,110 --> 00:01:46,310 is you can already add it as a part of your query steps. 22 00:01:46,370 --> 00:01:52,540 So every time you import this data you can already merge it and bring in the description. 23 00:01:52,550 --> 00:01:54,050 Let's go and set this up. 24 00:01:54,620 --> 00:01:58,960 First off, to be able to merge different queries together 25 00:01:59,150 --> 00:02:05,650 you actually have to create a query for each of your tables. So, for each of your data sets. 26 00:02:05,750 --> 00:02:09,949 In this case I have the information sitting in an Excel worksheet. 27 00:02:09,949 --> 00:02:17,180 What I need to do before I bring this to power query is to transform this into an Excel table. 28 00:02:17,210 --> 00:02:22,970 I'm just gonna press Control+T. Or you can go to insert, and insert a table, table has headers. 29 00:02:23,000 --> 00:02:26,420 Let's go with Ok. Let's give this a name. 30 00:02:26,430 --> 00:02:34,600 I call it ArticleMaster. Press enter and let's take away the table style to go back to our original style. 31 00:02:34,670 --> 00:02:41,440 Now that this is a table, I'm going to create a query on this. So that every time this updates as well 32 00:02:41,570 --> 00:02:49,200 my query is going to update. Go to the data tab and click on From table. I can see the information in 33 00:02:49,200 --> 00:02:51,700 power query, Article Master is fine. 34 00:02:51,740 --> 00:02:55,210 I'm not transforming it and getting a different result. 35 00:02:55,280 --> 00:02:57,430 Otherwise I would change this name. 36 00:02:57,500 --> 00:03:02,780 But in this case I don't need to. I just double check the data types applied to these. 37 00:03:02,780 --> 00:03:03,700 That's fine, 38 00:03:03,770 --> 00:03:05,480 and text is fine. So that's it. 39 00:03:05,510 --> 00:03:12,380 I basically don't have to do anything except I need to load it somewhere. But obviously I already have 40 00:03:12,380 --> 00:03:13,700 it in my worksheet. 41 00:03:13,700 --> 00:03:15,330 I don't want to load it there. 42 00:03:15,350 --> 00:03:22,660 So what I want to do is to go to close and load it to and only create a connection to this. 43 00:03:22,670 --> 00:03:26,320 Basically just keep this in memory and then click on ok. 44 00:03:26,350 --> 00:03:33,310 This means I have a query, every time this updates this information is going to get updated. 45 00:03:33,410 --> 00:03:37,460 My next step is to merge this query with this one. 46 00:03:37,460 --> 00:03:45,370 So I'm going to right mouset click on the first query and select merge. The dialogue box here shows me my 47 00:03:45,370 --> 00:03:51,480 first query which is this one and I want to merge it into Article Master. 48 00:03:51,550 --> 00:03:57,320 I have to select what is the connection between the two. The connection is the article code. 49 00:03:57,340 --> 00:04:02,220 Just select it and select the article code from the second table as well 50 00:04:02,410 --> 00:04:05,290 and then you can select the Join Kind. 51 00:04:05,290 --> 00:04:12,730 Now the default is left outer. Which means all from the first, matching from the second. Which is correct. 52 00:04:12,730 --> 00:04:15,410 This is basically the standard VLOOKUP. 53 00:04:15,430 --> 00:04:18,370 You have a lot more options here as well. 54 00:04:18,370 --> 00:04:25,090 So once you have time go and explore these options. Down here you can see that it found a match for 55 00:04:25,090 --> 00:04:30,240 136 of 136. So there was no information that's missing 56 00:04:30,490 --> 00:04:31,400 and then click on 57 00:04:31,450 --> 00:04:32,380 OK. 58 00:04:32,620 --> 00:04:41,110 We get power query opened up here. We get a new query called merge one. Let's changed the name. I'll call 59 00:04:41,110 --> 00:04:49,690 it CompleteSalesImport. For each of these lines we get this article master. And attached to it is a 60 00:04:49,690 --> 00:04:53,980 table. And if you happen to click on one of these 61 00:04:54,040 --> 00:05:01,900 notice that we see more information of this table. Basically, for each line of data it has attached 62 00:05:02,260 --> 00:05:03,630 this information. 63 00:05:03,630 --> 00:05:05,860 Now we don't want the whole information. 64 00:05:05,890 --> 00:05:09,010 All we want is the article description. 65 00:05:09,130 --> 00:05:15,460 I'm going to click on this arrow up here and from the filter just put a checkmark for description. 66 00:05:16,000 --> 00:05:21,290 And it wants to use the original column name as a prefix. But that's not something I want so I'm going 67 00:05:21,290 --> 00:05:23,250 to uncheck it and then click on ok. 68 00:05:23,250 --> 00:05:25,870 So I just see description. 69 00:05:26,410 --> 00:05:33,580 I can also change the title by double clicking. So I'm just going to add in article description and you can 70 00:05:33,580 --> 00:05:35,920 see that step recorded here. 71 00:05:36,190 --> 00:05:37,030 Now as a next step 72 00:05:37,030 --> 00:05:42,050 I'm just going to move this and put it right beside the article code. 73 00:05:42,070 --> 00:05:44,740 Notice we see reordered columns. 74 00:05:44,830 --> 00:05:50,950 Now I'm done with my query so I'm gonna go and close and load it to. 75 00:05:50,980 --> 00:05:54,880 Now here I can directly create a pivot table report. 76 00:05:54,880 --> 00:06:01,870 I could bring these in it's own table as well but I'm going to go with a pivot table report. 77 00:06:01,960 --> 00:06:05,170 Let's put it on a new worksheet and then click on 78 00:06:05,200 --> 00:06:06,140 OK. 79 00:06:06,220 --> 00:06:14,740 Now I can create a report that has information on articles so just the article description, the quantity 80 00:06:14,750 --> 00:06:19,150 sold, and the sales in USD. 81 00:06:19,360 --> 00:06:22,150 Amazing, right? As a last step 82 00:06:22,150 --> 00:06:28,390 Let's go and check how our query is going to update once our source data changes. 83 00:06:28,390 --> 00:06:33,760 Let's go back to our original text file here and on the bottom. 84 00:06:33,760 --> 00:06:39,700 I'm going to add two lines and use two article codes that don't exist in our list. 85 00:06:39,730 --> 00:06:42,840 I'm going to put 210 and 220. 86 00:06:42,910 --> 00:06:45,070 Close this and save. 87 00:06:45,070 --> 00:06:50,590 Okay so now what happens when I refresh this query? 88 00:06:50,590 --> 00:06:52,510 Click on refresh. 89 00:06:52,510 --> 00:06:57,740 I see that I have a blank row for the article description. 90 00:06:57,760 --> 00:07:02,740 It couldn't find the article code. To double check where this number comes from. 91 00:07:02,740 --> 00:07:07,730 I'm going to double click that's going to create a new sheet and show me the line details. 92 00:07:07,750 --> 00:07:10,810 This is the information I just updated. 93 00:07:10,810 --> 00:07:17,590 I added these two Article codes. So let's just remove this sheet, so it was 220 and 210. 94 00:07:17,620 --> 00:07:27,860 Let's go to our master data and add this information. So 210 is a silver watch and to 220 is a gold watch. 95 00:07:27,870 --> 00:07:35,560 Now let's go back to our pivot table and let's just refresh this. And we get our information right there. 96 00:07:35,560 --> 00:07:42,400 This means that any time we update our source information or our master data information, our merge 97 00:07:42,400 --> 00:07:47,740 query will apply the original steps and then merge these together.