Microsoft Excel is powerful and popular spreadsheet software that makes working with data simple. Over time, Microsoft has developed some pretty innovative ways to work with your data.
If you're looking for a powerful new way to manage data, Microsoft Excel for Windows now has powerful software built in to help: Microsoft Power Query is a new tool that gives you more control over your spreadsheets than ever before.
What Is Microsoft Power Query?
Power Query (known in Microsoft Excel 2016 as "Get & Transform") is a data transformation tool built right into Microsoft Excel. It allows you to import data from many different sources, make changes, and load the data right into your Excel workbooks.
If you're just starting out, it would be good to learn about the basics of Microsoft Excel.
When you import into Power Query it creates a connection to your data. Using this connection, you can work with your data in the editor and make all your changes before you save to a workbook.
Power Query will save your changes as steps in a file known as a "Query", which can be used any time. Think of it just like macros in VBA programming, if you're familiar.
Becoming comfortable with Power Query can take your Excel skills to the next level. If you regularly work with data, here are some great reasons to start using it.
1. Microsoft Power Query Is Built Into Excel
Power Query is simple. If you have Excel 2016 or later on your PC, you already have Power Query. You don't need to download any additional software or have any programming knowledge to begin using it.
To work with Power Query, simply open your workbook, select the Data tab in the ribbon, and click on Get Data to choose one of many different places to grab data from. You're now up and running in seconds with Power Query.
Note: For programmers, Microsoft did develop a new language for Power Query known as "M". Advanced users can code scripts using "M", which is a higher-level concept but provides extra power and value for expert users.
2. Easy to Use Editor
Power Query provides a simple interface for working with your data. Once you upload your data you can make changes inside of the editor, no programming required.
If you're already familiar with Excel, it's that much easier since the editor looks very similar to a window in Excel.
If you need to modify your data, you can do so right in the editor. Simple changes are made easy. Some examples you can do are:
- Add or remove columns in your table
- Filter your data by columns
- Combine or join two tables together
- Sort your data using Excel filters
- Rename columns
- Remove errors from your table
Once you're done with changes, simply export the results right onto your Excel workbook. It's very easy to work with multiple sheets, without having to open every single one and make changes.
3. Makes Repetitive Tasks Simple
A common chore when working with Excel workbooks is having to make the same set of changes to different worksheets.
For example, let's say you are given a report that shows sales numbers for the month of July and you need to make changes. You open your table, make your changes, and save. Simple, right? Then a month later, you are given a new report for the month of August.
With Excel, you would have to open the workbook and make all those changes again just to get the same result. Depending on the number of changes, this can be a lot of time! Power Query solves this problem.
When you connect to a workbook using Power Query and make changes, those changes are saved as "steps". Steps are just directions you give Excel to change data (i.e. "Delete this column" or "Sort this table by color").
All your steps are saved together, creating a neat little package. You can apply this set of steps to a new worksheet in one click, instead of making your changes all over again.
4. You Don't Need Programming Knowledge
Excel is programmable using Visual Basic for Applications (VBA), but that's a lot to learn. You can also use other programming languages with Excel, but that requires a lot of set up.
Not everyone's a programmer. Fortunately, you don't have to be one to use Power Query. The editor is graphical and easy to use. Any change you want to make is as simple as clicking on the interface. The editor looks very similar to Excel, so you'll feel right at home.
5. Collect Data From Different Sources
We've talked a lot about Excel workbooks, but Power Query can get your data from a lot of other places. If you want to work with data from a webpage, you can import just as easily. Power Query can also pull data from various sources such as:
- CSV Files
- Text Files
- SQL Databases
- XML Files
- Mircosoft Access Databases
There are many more sources you can use, but don't get overwhelmed. As you become more comfortable using Power Query you may have a use for some, or all, of these uses.
Why Not Just Use Microsoft Excel?
Excel is pretty powerful as is, no doubt about it. But if you're an expert user working with many different sheets your workflow can be a lot to manage.
It's important to think of Power Query not as a spreadsheet, but as a control panel through which you can work with data. The layout is similar to Excel to make it easier to work in. You can be very productive by combining the strengths of Excel and Power Query.
Empower Your Microsoft Excel Skills
There are a lot of great reasons to start using Power Query to manage your spreadsheets. If you master the basics of this program, you can create tables and work with data much easier.
Go further and build up your Microsoft Excel expertise with our articles on creating graphs from data in Microsoft Excel and analyzing data using Pivot Tables in Microsoft Excel.