What is Power Query? This is what Microsoft says: “Power Query is a data transformation and data preparation engine. Power Query comes with a graphical interface for getting data from sources and a Power Query Editor for applying transformations. Because the engine is available in many products and services, the destination where the data will be stored depends on where Power Query was used. Using Power Query, you can perform the extract, transform, and load (ETL) processing of data.” on their article called What is Power Query?
Power Query is available in all Excel versions since 2010. In Excel 2010 and 2013, Power Query is available as a free Add-in. You can download and install it. There will be a tab called Power Query. For Excel 2016 and onward you will find Power Query in the Data tab (menu). Power Query is the same tool in both Excel and Power BI, so you don’t need to learn both separately. That’s good news.
Below is a screenshot of what it looks like in the current version of Excel 365, as of autumn 2022 (the time of this post’s writing).
The Excel “Power” Workflow
When you need to get data from various sources, clean it and massage it, create relationships between the different sources, analyze and then present it, you can use Excel. The Excel Business Intelligence tools are available in Excel, provided that you have a compatible version of Excel. Below is the process.
- Raw Data
- Power Query
- Data Model
- Power Pivot & DAX
These tools are able to perform an extract, transform and load (ETL).