0:00
Hello everyone and welcome back
0:02
In this video we learn about the topic named as Merch Queries in Power Query
0:07
Now what exactly is a Merch query? If you know V-Look-Up and if you are very much expert into V-lookup or if you don't know V-lookup, no problem
0:14
You can stop using V-lookup and what you can get is you can get all the V-lookup answers in just few button clicks with the help of Power Query
0:22
No need of writing a function, no using keyboard, only using button clicks
0:26
Or else the other language if I say, it can be done as merging the button-click. two different tables using Power Query
0:32
Okay, let us see practically. So what I have is I have a sheet over here
0:35
and there are two tables given. The first table is the sales table where these are
0:39
the transactional data which I have, date, sales rep, car make and
0:44
the sales. Whereas the other table I have sales rep and region where
0:48
sales rep is having the unique values. There are no duplicate values. Now, my question is, I want to bring
0:53
this region column from this lookup table into this sales table or let's say
0:58
before sales. So what is the method? I have to use a Vlooker function in Excel
1:02
Now, without using Vlooker function, it is possible just by using button clicks with the help of power query
1:08
Let us see practically. So the name of this table, if I click on this design tab, and you can see the name of the table is sales table
1:15
Name of the table is given as lookup table. The name can be anything, but in our case, I have given a proper name
1:20
Now, I'll try to bring this board tables into a new file, and there I can just merge it
1:25
So now, I'll just click on file and create a new file
1:29
Okay and then I just click on this data tab new query from file and from workbook This merging can also be done in the same file but in the new file it is very easy simple method so i click on this from workbook and i can click on this
1:45
desktop and i have this folder and the file name is given as merge queries no we lookup so i'll just
1:51
click on this import button here now when i import it will give me the list of all the tables in that
1:56
particular 4.1 file so i can select the multiple tables here let's say look up table and i can also
2:02
select the option here as sales table. Lookup table and sales table
2:08
Now, after picking up both the tables, what I can do is I can click on this button, edit
2:12
So when you click on edit, a new power query window will open inside new Excel file
2:17
You can see that's a power query editor. And I have these two different tables here
2:22
When you bring the tables into power query, this becomes as query. They are no more tables
2:27
You can see that's a query settings and that's the name of the query here. okay so if I expand this that's a sales table now it is a query here now my thing is I want
2:35
to bring the region column from lookup table into my sales table okay into my sales table and
2:43
that also before sales I want how do I bring it so region column should be brought from
2:48
look up table here into the sales table so the method is in the home tab if you see there's
2:53
an option given as merge queries so click on merge queries it will ask you two questions
2:57
Do you want to merge in the existing table or in a new table
3:01
So what I'll do is I'll just click on merge queries as new query
3:05
So if I click on this, a third one will be created. Sometimes if you want to append or if you want to merge in the existing one, I would have
3:11
selected the first one here So I select as merge queries as new And now these are two tables So that the first table Always remember pick up the transactional table on the top That
3:22
the best practice. And from here, I'll select the second table, that is lookup table
3:27
Okay. Now, carefully observe here. The okay button here is disabled because we have not defined
3:32
the relationship between these two tables. There are some common columns within them, that's
3:37
a sales rep. So select the sales rep, and from here select the sales rep column
3:40
If you have multiple columns, you have to just press control click, control click, and you can select multiple columns
3:47
Now you see, I have this two tables, or you can see as two columns and two tables
3:51
Now, after selecting this, if you see, I have this, the selection has matched 542 out of the 1873 rows
3:59
So their matching is properly done. And from here, I can select the left outer join, right outer join, and so on
4:04
So there are many more functions here. We'll talk about this later on. but right now I want all the records from the first table and only the matching records from the second table
4:13
So I'll just select this option. By default, I'll keep it as it is, and I'll just click on okay. So carefully observe, this entire table would be compressed and this lookup table will be the fifth column
4:24
Okay, this entire table would be compressed. Name of the table would be lookup table and it would be fifth column
4:29
So I'll just click on okay and you can see that's the fifth column is lookup table
4:34
A new query has been created as merge one. Now I can just expand the table, I can just click on this double-headed arrow and I have these two columns
4:42
So sales rep and region. So first of all, I'll deselect the prefix, unwanted prefix I don't want
4:48
And here I can select the only option that is region and deselect sales rep Why deselect Because already I have the sales rep column here Unnecessary it would be duplicate column Now I can just click on this okay button
5:01
So once I click on okay, you can see I have this region column and what I can do is if I want
5:06
to shift it before this place or sales column I can just drag and drop it here. So if you
5:11
observe, I have not used any function. I have only used done this with the help of just
5:15
button clicks and a new query is created. So stop using VLookup. This is the more efficient method
5:20
and very much faster. Here you don't have to learn anything. No functions required. So after doing this
5:25
what I can do is I can just click on Home tab and there's an option given as close and load. So
5:30
after I click on Close and Load, the output of this table will be brought into a new sheet
5:35
If you see, I have all these three sheets. If you see, that's a sheet 1, empty. Sheet 2 is having
5:41
the sales table. Sheet 3 is having the lookup table and the 4th 1 sheet 4. This table is what I'm
5:48
looking for. Okay. Now what I'll do is. I'll delete the sheet 2 and sheet 3 which I don't require
6:02
And here this is the output table. And if you see that the region column and sales column
6:07
Now the beauty of using this function or this merge queries, any changes made in that particular
6:13
file, the original file, here you can see all the changes. So it's very fast, very easy
6:17
And this is an automated process. While doing this, if you think you have made a mistake, you can just simply double click on this merge one and you can just correct whatever mistakes have done
6:26
So it's very fast and easy method, right? So I hope you have understood how to work on this merge queries and you can stop using Vlookup
6:32
That's all for this video
