What is Power Query?
Power Query is an ETL technology (Extract, Transform & Load) which can Extract Data from different sources, Transform the extracted Data into the required format and Load it into Excel or Power BI for further Analysis.
The different data sources can be ‘Text files’, ‘CSV files’, ‘Excel Tables’, ‘Web-pages’, ‘Databases’ etc.
Transforming data can be…
- Splitting or Combining Columns
- Removing Rows, Columns or Blanks
- Sorting or Filtering Values
- Adding Index as well as Calculated Columns
- Grouping or Summarizing Data
- UnPivoting Data
- Removing or Replacing specific values
Why should I use Power Query?
This is one of the easiest way to automate your work without any VBA skills.
Every step of transforming data using Power Query Editor will be recorded as a Query. So, whenever new data is added to the source data or for a change in the source data, we don’t need to repeat the steps in Power Query Editor. Just Refresh the Query from the right-click menu and the output will be updated.
Here is a simple example for the use of Power Query.
Suppose you have 25, 50 or more excel files in a folder that need to be combined to create a Pivot Table Report.
You don’t need to open each of those files and Copy Paste the data into a single file. Power Query can do that for you at an expense of few mouse clicks.
And once you have setup the Query you can Add/Delete more files. Just by refreshing the query the output table will get updated.
Is Power Query easy to Learn?
Even though Power Query is considered as an Advanced Excel tool, the User Interface of Power Query is quite user friendly and it’s easy to learn.
Moreover, both Excel and Power BI uses the same Power Query interface. Once, you are familiar with the Power Query interface of Excel, you can use Power BI too.
Does Excel comes with Power Query?
From Excel 2016 onward Power Query is bundled with Excel and is know as Get & Transform. You will find it in the Data of Excel ribbon.
For Excel 2010 and Excel 2013, Power Query comes as an Add-in and should be installed separately.
Download Power Query Add-in for Excel 2010 and 2013
Where can I find Power Query in Excel?
Power Query is known Get & Transform from Excel 2016 onward and can is placed in the Data tab of Excel ribbon.
Watch the Video on 12 Methods to Clean Data using Power Query