Hi Friends, we are starting with a new series of articles on a very important feature of MS Excel. Yes, that is Power Query. It was first introduced with Excel 2010 version and started gaining attention of users due to its capabilities and features. So, what is Power Query, let’s start from this question.
Power Query is a business intelligence tool available in Excel and Power BI that allows you to import data from many different sources and then transform and analyze the data.
The user interface is also very intuitive and does not require special skill to learn it. With the easy navigation, you can quickly import your data and start analyzing in few minutes only.
So what you can do with your data through Power Query. Well the answer is Almost Everything. It allows multiple ways to import the data then transform it using cleanup, filters, sort etc. and finally Publish or export the data.
Using Power Query, you can import data from various source such as Excel, Text, Access, SQL, Web, Facebook, Azure and many more.
Once you have imported the data, the next step is to clean and transform it. You can do it through the amazing features provided by Power Query such as changing data types, removing columns rows, blanks, find and replace, text to column, split column, sum, rounding, calculations, filters, sort, transpose etc.
If you have multiple data sets that you want to consolidate into one query, then you can use append or merger queries feature.
The next step is to summarize your data through Group By option.
The final step is to publish or export your data to Excel file. You can do this through “Close & Load” button and your data is ready to share.
There are majorly two advantages of Power Queries as mentioned below
Employee Database is an MS Access based tool to manage employee details. The tool supports upto 78 demographics for each employee such as Name, Location, Phone, Email, Address etc. The tool also comes with inbuilt attendance tracker to track daily attendance of employees. Over and above this, you can also design your own trackers and start using it.
WEEKDAY function applies to a Date and returns the output for Day of the week. The output of the function varies from 0 to 7
VBA Code To Delete All Shapes On A Excel sheet Here is a VBA code which deletes all the shapes from an Excel sheet. Code is simple but you have to be bit careful while…
Video: How to Hide Worksheet in Excel? Hide Sheet in Excel When I was creating an excel dashboard, there were multiple sheets which I used for calculation purpose and never wanted anybody to make any…
Merge Excel Files From last few months, we have been receiving frequent requests from users for a VBA tool which can consolidate Excel files from a folder. So here we come with one more free…
To ensure that your VBA project works smoothly without any error, you need to put lot of error handlers and validations. Here is one of them, below code can be used to check if the given path is valid or not. You just need to provide the path of the file to the function and it will check if the file exist at the location. If it returns True that means the file is available at the path, if it returns False that means it is invalid path or the file does not exist at the location.
Very clear explanations.. Thanks.. Need more videos on PQ…