Excel is one of the best data manipulation tools and the most used spreadsheet software. The Microsoft product is always being improved, and one of the most powerful features that have been added recently is Power Query.
This functionality was introduced in 2013 as an Add-in and was also made compatible with Excel 2010. In 2016, Microsoft integrated it to Office 2016 and Office 365 then renamed it Get & Transform. In 2019, the name was changed back to Power Query, and the tool is also available in Power BI.
Today, we’ll introduce you to this powerful tool and help you see how it can automate your data processes to save you time.
What is Power Query?
Power Query is a business intelligence tool that allows users to discover, connect, combine, and refine data sources for analysis. To use it, you can import data from many different sources, and the tool can then clean, transform, and reshape it as needed. You can check out 50 things you can do with Excel Power Query here.
Although it is an advanced tool, Power Query is far easier to learn than other advanced Excel features, and users won’t necessarily need to learn any code to use it.
Power Query core functionalities
Power Query manipulates data in 4 main steps.
Connect
You can connect Power Query to different data sources, either local or cloud. These can be:
- Files: CSV, Text, Excel, XML, or JSON.
- Databases: MS Access or Microsoft SQL Server.
- Varied data files: Salesforce, MS Exchange, Webpages, etc.
Multiple sources can also be brought together and analyzed. Each step you take will be recorded in the background, and you can modify these steps as you need to make sure your data is connected the way you’d like.
To import data, go to Ribbon > Data > Get Data. You can then choose between file, database, Azure, online services, and other sources.
Transform
Transform is a Power Query functionality that allows you to shape the data you have connected in a way that meets your analysis requirements. This can be changing a data type, removing a column, or merging tables, among other things. Note that this does not edit the original source.
To facilitate transformation, Power Query has a dedicated editor known as the Query Editor. After loading data, the editor will open. You can also open it manually and add data. To do this, go to Data> Get Data> Launch Power Query Editor.
Each transformation you do will also be recorded and labeled. When all these transformations are combined, they will constitute your query. Note that you can also create or modify queries using M Language, the language that Excel uses to create a query out of your data manipulation.
Combine
The combine is another Power Query data manipulation step that allows users to create a data model based on multiple data sources. This gives you a unique view of the data and makes it easy to do analysis.
Share
Since Power Query doesn’t change your original data, each query you create is automatically saved. You can view all your queries by doing to Ribbon, Workbook Queries, and then Show Pane. In later versions of Office, you can simply select Queries & Connections in the data tab.
Apart from saving, you can also share your queries with anyone in your organization. They will then be able to use it in their workbooks. You can also use your saved query for multiple data with different data.
Where do I start?
Getting started on Power Query is quite easy, and you won’t face much of a learning curve. If you are using Excel 2010 or 2013, you can download the Power Query Add-In from the official Microsoft website. The Add-in was created for the 2013 version, but it is backward compatible.
If you are using Excel 2016, 2019, or Office 365, you are ready to go. For the 2016 version, you will use it under the name Get & Transform, and it can be found under the Data tab.
Once everything is set, you can then start exploring Excel’s most powerful data tool!
