This is the introductory blog on the basics and overview of the Power Query feature in Excel. In this blog, we would unlock this amazing excel tool. We would learn about what is power query tool in excel, its advantages and features, and many more things that a power query beginner should know.
So let us now begin with this introductory and overview lesson on Excel Power Query.
What is Power Query Tool in Excel?
Power Query in Excel is a very powerful tool provided by Microsoft in Excel Application. This feature is inbuilt and fully integrated into Windows Microsoft Excel versions 2016, 2019, and Office 365 with both the versions (32-bit as well as 64-bit). It is pertinent to note that the power query is yet not available in Mac OS.
You can find this feature in the ‘Data‘ tab under the group ‘Get & Transform Data‘ in Excel, as highlighted below.
The above image is from Excel Office 365. The look and design of these power query tool may differ in other excel versions (Excel 2016 and 2019).
Unfortunately, the excel power query tool is not pre-installed in the older versions of Excel (prior to Excel 2016). Surprisingly, Microsoft has provided an external add-in for power query for the older versions. You can download and install the power query add-in for Excel 2010 (Professional Plus) and Excel 2013 using the steps shown here. Once installed, excel would add a new tab named ‘Power Query‘ to the Excel ribbon as shown below.
Purpose of Power Query
When it comes to the excel power query and its feature, just imagine a chocolate-making machine. What happens in chocolate making factory are the following three processes:
- You add all the chocolate making ingredients into the machine.
- The machine performs pre-defined processes on these ingredients.
- Finally, it outputs delicious and yummy chocolates that are ready to eat.
The Power Query Feature is no different than the chocolate-making machine.
- Over here, you add huge data and files into the power query tool (Get)
- Then the Power Query feature performs pre-defined operations on the data (Transform)
- And as a result, excel would give the expected output (Extract)
Once these processes are defined inside the power query, they remain saved. You can reuse these pre-defined steps every time without the need to start from the beginning.
What Data Does Power Query Support?
With the Power Query you can import following file format to perform transformation on the imported data:
- Another Workbook or Excel Files, Text or CSV file format, XML files, JSON Format, PDFs
- SQL Server and MS Access Database
- From another folder in the PC
- Data from web pages and excel tables/ranges
Advantages of Using Power Query Excel Tool
As mentioned earlier in this blog, the excel power query tool is a very powerful tool provided by Microsoft to get and transform the data. Below listed are the examples of this feature.
- You do not need any prior knowledge about complex excel formulas and functions to learn power query feature in Excel. It is a very user-friendly feature, with no formulas and no VBA coding.
- No need of having a core understanding of complex codes and formulas to learn this excel feature. When you perform the power query transformation steps the first time, excel automatically creates the code behind it (known as M Codes), just like when you record a macro in excel. You simply need to then reuse this code by refreshing the power query, it’s that simple.
- The user interface of the power query editor window is friendly and self-explanatory in itself. The options and buttons available in the power query editor window to transform the data are very easy to use.
With this, we have completed the basics of Power Query excel feature. Your reviews and comments in the Comment box below are of a lot importance for us to improve the quality of content. Feel free to comment your thoughts here.