0:00
Hello everyone and welcome back
0:02
In this video we'll see another topic related to merge queries and which is very important, very useful
0:08
The name of the topic is how do I do a V lookup with three lookup columns or three common columns
0:14
Same way I can tell in power query language as how do I merge queries with three different or you can see as three common columns
0:21
I have a case study over here. If you see, I have a lookup table with product name, the region, product type and the discount
0:29
So here the values are very much unique. They are not repeated. That's the reason I have given the name as lookup table
0:34
On the other hand, if you see, I have this sales table, region, product group, product and the sales
0:40
This is a transactional table where the records have been repeated or they have been duplicated here
0:45
Now, the thing is, I want to find out, carefully observe in this table there are three columns which are common
0:50
Product, region, product type. Here also if you see, region, product group and product type here
0:56
So you can see the names are different. Product type is given here and the name is given as product group
1:01
No problem. We'll try to fix it. Now pause for a second and just understand how do you bring this discount column from this lookup table and bring it here
1:10
Okay. Definitely you'll say we'll go for concatenate and then we'll go for Vlooker function, which is very lengthy
1:15
You have to use your keyboard and the output will not be that fruitful. Here using this power query method, we'll do it with the help of button clicks
1:23
No using keyboard, no using function. It's very simple, very easy. Let us see that practically
1:28
So if you do by traditional method, expect 20 minutes minimum to get the answers
1:33
Using this hardly 5 minutes and the answer would be very best. So please see here, name of this table, if I keep my cursor, data tab and the name of this
1:41
table is in the design tab it table 3 And name of this table if I keep my cursor it is Table 4 Table 3 and Table 4 I want to merge bring this discount in this table here So what I need to do is first I need to convert this both tables into queries
1:56
How do I do that? Keep your cursor in the data. That's the table 3. Data tab from table
2:02
Okay, from table. Ignore this warning. Sometimes you'll get this compatibility issue
2:07
Just click on close button. Now, this is on the right side if you see, that's Table 3
2:11
Let me just rename it. This is optional. Let me rename it as lookup query
2:21
Lookup query. And if you see the data type is correct, discount is 1.2, that's a decimal number
2:27
I'll just click on close and load, close and load 2 and only create connection. Okay, I'll just click on only create connection
2:34
So on the right hand side if you see, that's a look up query. Nothing great I've done. Same way I'll repeat the process for this particular table 4. I'll just click on database
2:41
data tab and click on from table you see my cursor here from table it will come into
2:47
power query editor I'll just change the name as sales table or I'll rename it as
2:56
sales query okay sales query and I'll just click on close and load to and this
3:03
also I'll create as a connection that's it so close and load to only create
3:07
connection click on load now after doing this these are two different queries I
3:11
want to merge these two queries, bring this discount in this table. So go to Data tab and you can see there is an option Combined queries merge
3:19
Okay, combined queries merge. So when I click on this merge option, it will give me two options there
3:25
So if I click on this drop down, these are the query names. These are not the table names. So queries can be merged Table cannot be merged here in Power query So I select the option as sales query Always try to pick up a transactional table on the top and from the bottom I select this lookup query Now this is the most interesting part How do you create a relationship There are three common columns in both the queries So I select this region Control Press control from a keyboard and click on product group and product Okay So you see this one two and three The sequence in which you select the upper table columns same
4:00
way you have to select at the bottom. So now I should select first region, control, click on
4:04
product type. The column names are different, no problem. I'll select product type and third
4:08
one I'll select product. So you see this one, two and three very small they are written. It
4:12
should match with this sequence. So I'll press control from my keyboard, region, product
4:17
type and product. You see how easy it is? Very simple. In Excel it is very difficult to use
4:25
So one is matching with this one that is region, product group is matching with the product type
4:30
column names can be different and the product column is matching with this product column. So finally, you see there are some matching records, 1464 records which are matching
4:38
And definitely I want left outer. That means all the records from this sales table and only matching from here
4:44
When I click on okay, this four columns would be named as lookup query and it will come as fifth column
4:49
All this lookup query will come as a fifth column. So when I click on okay, carefully observe
4:55
So these are the sales table. If you see sales, the fifth column is the lookup query, is been compresses
5:00
and brought it here. It is giving an option which column you want from here
5:04
So using V-Lookup you get only one column as an answer. But using this merge query, you can
5:09
get as many columns you want. So definitely what I'll do is I'll not select all the
5:13
columns because already I have all the columns here. So I'll just select only the
5:17
discount column that column and I deselect the first column that is the prefix column here So because this columns already I have I don want to duplicate them Now I just click on OK button and you can see that a discount column which has been
5:29
added here. Now, if I want to multiply this with the discount column
5:33
sales into discount, so I can get the total sales here. Select sales, control click
5:39
on the discount column. This is one additional optional method. If you don't
5:43
want, you can skip this. And then I can click on add column. standard, I want to multiply, so I'll select the option as multiply
5:51
Remember, in add column, there's a standard multiply, and you see, I'm getting this total sales
5:56
Though the name of the column is multiplication, I'll rename it as total sales. Okay, total sales
6:03
And whatever steps you're doing, all are getting recorded at the backend in the form of M language
6:08
Now, once you do this, you can just click on Home tab and then click on this button, close and load
6:14
Okay, directly it will take as a table, and you see, that's a close and load option
6:17
So you see what we have done is we have tried to match two different tables with what you call C's three different column headings
6:24
You see this one, three common columns. Though the column headings were different product type and product group, but it was very simple just using button clicks
6:31
You can get the answer. And the last and the same story, anything changes in the data source
6:36
It will change here. Let us cross verify. I have this West Freestyle Carlota which is 9516
6:42
West Freestyle Carlota. And here if you see West Freestyle Carlota, which is 0.136
6:47
the discount. Let us cross-verify in the new sheet what I've got the answer. You see West
6:52
Freestyle Carlotta 9516 which is correct and the discount is 0.13. If you see here
6:58
9516 and the discount is 0.13. So we have successfully merged these two tables into one
7:04
particular output. I hope you have understood how to merge this and that's all for this video
