0:00
Hello everyone and welcome to Tutorials Point. My name is Pavan Lalwani and you're watching a series on advanced Excel Power Query
0:08
Power Query is a very powerful tool which is given by Microsoft. It is here from last 10 years I can say from 2010 onwards
0:16
Now why you should learn this power query? What are the advantages? If you learn this query, you'll be saving 50% of your daily routine time
0:23
If you work on Excel for 8 hours per day after having a command on this tool, I can assure you you'll be saving 4
0:29
hours per day on this tool. Okay. Let me explain you more detail how you'll be saving time
0:34
Now what is exactly a power query? Power query is a ETL tool which is mostly used for cleaning
0:39
the data source. It can be any data source, 20 plus data source I have. Now ETL stands for
0:45
extract, transform and load. Okay, you can extract data from same data source. Transform in power
0:51
query, load back to Excel. Okay. Now what exactly is a power query? It is a combination of
0:56
multiple software. I can say SQL, Excel and Vigel. BBA In Excel SQL what you do is you define some queries you extract data and you can take it there Same way you can keep on filtering the data in SQL what you do in the data source In Excel you use mostly functions text date number time or you use Vlooker function Everything can be used into Power Query
1:17
VBA, you do an automation process. Same thing can be done into this Power Query. And the best part about
1:22
Power Query, it is free add-in by Microsoft. For those users who are using 2016-2019 version
1:29
You see, that's a 2016 version, 2019 version. And also, if you're using some BI tool for ytics purpose, you can use this Power BI tool
1:38
There also you get this free add-in, that is Power Query. But don't be sad, if you're using 2010 version or 2013 version, what you can do is you can
1:46
separately download this add-in, which is Power Query Add-in, and you can install and use it, okay
1:51
for 2010 and 2013 users. Now, what can be done into this Power Query
1:56
Suppose I'll give an example. If I have 100 Excel tables in one file, one Excel file, 100 Excel tables, if I want to put it one below the other, normally what we do is we just copy paste the data or we write a code in VBA macro
2:10
But that is possible with the help of Power Query and it is done in just three button clicks Yes you heard it right in just three button clicks no using keyboard Purposely I written this button click So you can understand just using mouse click and you can get the answers
2:24
Not only Excel tables, I'm talking about all the data sources which Excel can play with
2:29
You can append 100 Excel files. You can append 100 Excel CSV files. You can say CSV files or SQL tables or different access tables also
2:38
Just using button clicks, three button clicks, eight button clicks, three button clicks and so on
2:42
So using this clicks, you can do that process. You see how simple it is. So you don't have to put a command on any language here
2:49
One more thing. If you want to calculate your age in Excel, normally you write a function
2:54
You write date-diff function and then you get the answers. Here, no using keyboard, first of all, no learning any function
3:00
Just using two button clicks. You can find your exact age in Power Query
3:04
Yes, that is possible. You find it interesting? Yes. There are 99 plus functions which can be done with the help of just button clicks
3:11
So there also you don't have to learn any function or keyboard options there
3:15
Just using button clicks, you can get the answers. There are approximately 700m functions which will be seeing in this particular video series
3:22
where you can reduce your task also Very difficult tasks can be made it easy Now the next thing is Vlookup Vlookup is the most search option in Excel in Google across the world People like to learn Vlookup
3:34
It's the most interesting topic. Now the thing is you need to have a command on Vlookup then you can use it
3:39
And you have to learn this Vlookup, look of value, some table and everything
3:43
But I'm assuring you without using your keyboard, without using any function in Power Query, just by using button clicks
3:50
you can get the Vlookup exact answer, VLook. exact answer, Vlookup, approximate answer
3:54
Yes, just using button clicks. So it is very simple and very easy
3:58
One example I can give you. If you want to do a V-lookup for three columns, normally what you do is you use a concatenate and then do a V-look-up
4:05
Nothing has to be done. Just button-click, get the answers. Now, as I said, 90% of the videos which I have recorded
4:11
you don't have to use any keyboard options, any functions. Just 10% wherever I want to give a file name, query name
4:19
or some change of the name, column name, only those places I have used the keyboard
4:23
So very less keyboard options, just using mouse flick, you can get the answers
4:27
Trust me, after going through this course, after learning power query, you can save at least 50% of your daily routine time in Excel
4:34
I hope you have understood the introduction to this power query. That's all for this video
