0:00
Hello everyone and welcome back
0:01
In the previous video we have seen how to append three tables in one workbook which is kept in the one sheet itself
0:08
Now in this video I'll show you something more interesting, more bigger
0:12
So the thing is I'll show you how to append 100 Excel tables in same file immaterial of position
0:19
It can be on any sheet on any column but still we'll be able to add those 100 tables but just one button click and one function
0:27
Let us see that practically. So if you see, I have two sheets, sheet one and sheet two
0:32
And these are different tables. Okay, I have just increased a number of tables
0:36
You see, these are three tables. And if I keep on moving, these are some more tables
0:40
So if you try to count, there are many, many tables on this particular sheet. What I want is, I want to combine this into one particular table
0:47
You see, there are so many tables. I can keep on moving, so many tables. And also in the sheet two, there are other tables also
0:53
I want to bring it into one place. How do I do that? So same steps what I done in the previous view video I go to Data tab and I click on new query from other sources blank query So blank query will create a new query which is completely blank and I can just write a function in that particular query
1:11
So here if you see, that's the formula bar. If the formula bar is not visible, make sure you can go to view menu bar and there's a formula bar checkbox
1:19
You can select that checkbox. It would be visible. Now, I'll type here a function. This function is case sensitive again
1:27
Okay, the function name is Excel. Current Workbook and open, you can see open and close bracket, E capital, C capital, W capital
1:40
It's a case sensitive. Once you press Enter, you'll get list of all the tables into your particular file in all the sheets, okay, immaterial of the position
1:50
You can see all the tables have been added here. Now, if I want to expand this table, carefully observe, I have 30 rows
1:57
That means there are 30 tables. So if I click on this double arrow I be getting all the columns which are available in that table Okay these are all the columns available in those tables I can just deselect this checkbox because I don want the prefix here
2:11
Carefully observe, 30 rows are there. If I click on okay, and you can see all the tables have been appended, very fast, very simple, very easy
2:20
Now, after this, I can just change the data type of the column, or else I can just click on Home tab and click on Close and Load
2:27
Carefully observe 30 tables. I click on close and load, there will be the 31st table
2:32
You see, if I click on close and load, a new sheet would be created and all the data
2:36
would be upended. See, on the right hand side, there are 221 rows
2:41
30 tables earlier. This is the 31st table. So when I right click on this place, and if I click on refresh, and if I click on refresh
2:50
see, the number of records will be changing here. 341. Again, if I refresh, it will be some other number
2:56
It should not happen. It should not happen. not made any changes So you see 461 So what I need to do is I need to go back and edit the query See the name of the query is given as query 1 and the name of the table is if you see that a query 1 So I go at the
3:11
backend and I'll just remove or I can filter this query 1. Double click on this query 1 and here
3:17
you see that's the output. I can just click on the first step that is the source where I've
3:21
written the function Excel. Current Workbook. So from this drop down what I need to do is I need
3:27
to deselect this query 1 because that's the output. It will append
3:31
itself if you do not deselect. And I'll just click on okay. You see 31 rows. Previously it was
3:36
30 rows. So if I click on okay, now I have 30 rows. So my job is to just click on home tab
3:42
close and load. Okay. And finally if you see, I'll get the same number of records. That is
3:46
120. Again, if I right click refresh. Okay, you can see the same number of records would be
3:54
visible. So this is very easy, very simple and you can use it into your day-to-day life if you want
3:59
to append multiple tables, right? So I hope you have understood how to append those tables just by one formula and one button
4:05
click in Power Quarry. That's all for this video
