Up next in 10
Advanced Excel Power Query | Merge Queries from two Excel Files | Tutorialspoint
796 views
Oct 18, 2024
Advanced Excel Power Query | Merge Queries from two Excel Files | Tutorialspoint Advanced Excel Power Query Online Training. In this power query tutorial, you will learn about Merge Queries from two Excel Files in power query or V LOOKUP from different Excel files. Get Certified in Advanced Excel (Power Query):https://bit.ly/3LdiidF Use coupon "YOUTUBE12" to get ‘’FLAT 12%’’ OFF at Checkout. Quality Learning to UPSKILL yourself only at TutorialsPoint. Explore & Learn the top trending courses curated by the best trainers in the Industry @tutorialspoint: Your go-to Learning Solution. Power Query is a powerful tool given by Microsoft. Power Query combines different software(SQL, Excel, VBA). With our Trainer, You learn: - Introduction to Advanced Excel Power Query - Excel Installation - Advanced Excel Power Query Text Functions - Date Function- Calculate Age in 2 button clicks - Merge Queries from two Excel Files - Append 100 Excel Tables - 99+ Excel functions can be replaced by button clicks - M Function for Date - 700 M functions to reduce tasks - Merge Queries - No VLOOKUP. - How to Clean Financial Data with Power Query. Upskill your career by training on the best-TRENDING Courses in the Market.
View Video Transcript
0:00
Hello everyone and welcome back
0:02
In the previous video we have seen how to do a V lookup or how to merge queries from the same Excel file
0:09
But in this video I'll go a little bit advanced. I'll show you how to do a V lookup from different Excel files
0:15
Or I can say in terms of, in language of power query, I can say merge queries or merge tables from two different Excel files
0:22
Now, let us see an example. On the right, if you see I have one table, that's a lookup table
0:27
If you see sales rep and region. And on the right, if you see, I have a sales table where all the transactions are being occurred and the data has been fit here
0:35
Now, what I want is I want to combine these two tables or I want to merge these two tables so that I can get the answer as region-wise sales
0:44
You see, that's the sales on this right-hand side and the region is given in this column here. Now, I want to get this output in a completely fresh new Excel file
0:52
How do I do that? Let us see that. Before going to that topic here, if you see the name of this Excel table, if I keep my cursor
0:59
go to design and you can see name of this Excel table is lookup table
1:03
and name of this Excel table if you see that's a sales table so these are two
1:07
tables into different files I'll create a new file and try to bring this both
1:11
tables into that power query into new Excel file so how to do that first of all
1:16
I'll create a new file file menu bar new and I'll click on blank workbook so this is
1:22
a completely blank workbook now I'll just click on this data new query from file and
1:29
from workbook. Earlier in the previous video we have just picked up the data from a table
1:33
within the workbook. Now this has changed. I'm trying to pull a data from different workbooks
1:38
So click on from workbook and pick up that one file, the first file. The first file I have that is sales Excel file. So I'll just select that sales Excel file
1:51
and click on import. Now once I click on import it will ask me what do you want to fetch
1:56
Do you want to fetch sheets or tables? So right now if you see the blue color icon that is a table and the below one is a sheet icon definitely I pick up this sales table and then I just click on load as a connection I not load only I just click on this load drop down and there are two options load and load two
2:13
If I select load option, it will directly bring as a table. Unnecessary file size will increase
2:19
So better I'll select the second option, load two. Then it will give me some choices
2:23
The first choice it will say bring as it is the entire table and second one only create connection
2:28
So connection is just creating a connection with that. data or table. Now I can just click on load. So what will happen is nothing will happen
2:36
here but on the right side if you see there's a queries that's a sales table connection
2:40
only. Now this table has been converted into query. Okay. So I'll repeat the same process
2:45
for the lookup table. Data new query from workbook and I have the file in some folder
2:57
look up file and I'll click on import. Now the same thing it will ask me do you want to
3:01
import a table or a sheet. Remember the blue color heading that is a table and the below one
3:05
is a sheet. Definitely I'll pick up this lookup table and the same thing. I'll just click on
3:10
this load and load to. Now what happens if you click on edit? If you click on edit, it will open
3:15
power query editor window where you can format the text size, data type, anything you can change
3:20
there. But right now this is a very clean data. I don't have to format anything. I'll just
3:25
click on this button and load to and select the option, only create connection. And click on
3:31
load. So on the right-hand side, if you see, these are two different queries which I have from two
3:35
different files. Now, the last thing, I just want to merge these two different queries. So data tab
3:41
new query, and I have the option as combined queries, and you see merge, append, or launch
3:46
a new query editor. So definitely I'll select the option as merge because instead of using
3:51
V-lookup, we use merge queries in Power Query. Now, from the top, always try to select a
3:56
transactional table where multiple data occurs. From the below, try to pick up a
4:01
lookup table where there are unique values. So in this lookup table I have unique values
4:05
Here there are multiple values repeated Now if you see the okay button is disabled in order to create a relationship between them I have to select the common column that is the sales rep and sales rep You see okay button is enabled
4:18
At the bottom, something is given here. The selection has matched 542 out of the first 1873 rows
4:25
That means matching is properly done here. Now, I obviously want the entire sales table and only matching records from this sales rep table or lookup table here
4:34
So you see, there are many options, join kind. So definitely if I select the option, there are 5, 6 options which are given
4:40
I'll go for the first one which says left outer. That means all records from the first table and only matching
4:46
Because there might be some sales rep who have not done the sales. So definitely I don't want them
4:50
I just want the matching records. So I'll select the option and click on OK
4:55
Observe here, there are four columns. The fifth column will be brought here. Fifth column would be brought here once I click on okay
5:01
So this lookup table will come here when I click on okay. And now if you see, there are four columns
5:06
and the fifth column, the entire table has been compressed and it is brought in the fifth place
5:11
So what I can do is I can select the options from here. Remember, using Vlookup, you only get us one column
5:16
But using this merge query, you can pick up as many column as you want
5:20
Right now, we have only two options. I don't want a prefix. We have done in the previous video
5:25
And out of this tool, I already have a sales rep. So I'll not pick up the sales rep column
5:29
I'll just pick up a region column. I'll click on OK. And you see, there's a region column which is inserted
5:35
So V-lookup is done properly just using button clicks, no using keyboard, no using formula
5:41
Now I think region column should be kept here before this place. And suppose you don't want some columns, they are unwanted columns
5:47
You can simply right-click on that column heading. Let's say I don't want it. Right-click on the column heading, you can click on Remove button
5:53
And whatever columns you need, you can just keep it that. And finally, this output I want in the form of a table
6:00
So I'll just click on Close and Load 2. There are two options. If I select the second option, close and load to
6:05
Definitely, I don't want as a creation or connection. I just want this as a table, entire table
6:11
So once I click on OK this will create a new table here with that output Now the beauty of this power query is anything changes in the previous two files that two files Excel files Definitely you can see the changes here Let us see that practically So I minimize this window or I just double click here Carefully observe Barron is having a Lincoln car make in East region So what I do is I change the region for Barron as West Let see what happens Okay you see that a Baron East region I type here as West
6:44
and I'll save this. Make sure you save this. And now I'll open the other file which is at the back end
6:52
Okay. So that's a file. You see Barron, Lincoln and East. What I'll do is I'll refresh this. It should change to West
7:00
When I click on refresh, you see it has changed to West. So anything changes in the source file
7:06
Definitely the target will change. Depends, you have to just refresh the data here
7:10
Now, one thing should be noted here. Suppose this file, if I change the location, that's a target file, if I change the location of this target file, not a problem
7:18
But you should not change the location of those two source file
7:23
If you try to change and keep it somewhere, it will not work. I'll tell you the reason here
7:27
Suppose this is the sales table. If I just double-click and open in the Power Query Editor, you see the first step is given as source
7:34
First step is given as source. Now in the formula bar, you see that's a formula bar
7:39
In case if you're not able to see the formula bar, you have to click on view. menu bar and there's a formula bar
7:44
In the formula bar carefully observe, it says Excel workbook, file contains, this is the path
7:48
of my source file. See users tutorial point.com, desktop, final output. Suppose if I send you this file, it will not work, definitely not because this path
7:57
you don't have. So what you need to do is if you are sending this file to someone else, or if you're changing
8:03
the path, make sure you click on this settings button and change the path in this location
8:08
only then it will work. Okay, so there are some things which you need to take care
8:12
I'll click on cancel and I'll click on close button and save
8:17
So I hope you have understood how to combine different tables or how to merge different queries or how to use Vlookup from two different Excel files
8:25
That's all for this video
#Business & Productivity Software