Why use Microsoft Power Query for Excel

Microsoft Excel is a powerful and popular spreadsheet software that makes working with data simple. Microsoft has developed some fairly creative ways to work with data.

If you're looking for a powerful new way to manage data, Microsoft Excel for Windows now has software built in to help you with that, which is Microsoft Power Query. This is a new tool that helps you take control of your spreadsheets more than ever.

1. Ways to hide data in Excel
2. How to create a data entry form in Excel
3. How to compare data on 2 Excel columns

What is Microsoft Power Query?

Power Query (known in Microsoft Excel 2016 as Get & Transform) is a data conversion tool built right into Microsoft Excel. It allows importing data from many different sources, making changes and uploading data right into Excel workbooks.

When you enter Power Query, it creates a connection to the data. Using this connection, you can work with the data in the editor and make changes before saving.

Power Query will save changes as steps in a file called Query so that it can be used at any time. You can think of it as a macro in VBA programming.

1. How to enable macros in Excel

If you regularly work with data, this is a great reason to use Power Query.

1. Microsoft Power Query is integrated in Excel

Power Query is very simple. If you use Excel 2016 or later on your computer, you already have Power Query. You do not need to download any additional software or have any programming knowledge to use this tool.

To work with Power Query, simply open the workbook, select the Data tab in the ribbon and click on Get Data to select the source to retrieve data. You are now ready to use Power Query.

Note: For programmers, Microsoft developed a new language for Power Query called M. Advanced users can write code scripts using the M language. This is a more advanced concept but provides Add strength and value to professional users.

2. Easy to use editor

Power Query provides a simple interface to work with data. When loading data you can make changes inside the editor, no programming required.

If you're familiar with Excel, using the editor is much easier because it looks very similar to the window in Excel.

If you need to edit data, you can do so right in the editor. Some examples you can do with data:

1. Add or delete columns in a table
2. Filter data by column
3. Combine the two tables together
4. Sort data using Excel filters
5. Rename the column
6. Delete errors from the table

When you're done making changes, just export the results right into your Excel workbook. It's easy to work with multiple sheets without opening each one and making changes.

3. Do repetitive tasks in a simple way

A common thing when working with Excel workbooks is to make the same changes to different worksheets.

Let's say you have a July sales report and need to make some changes. Open spreadsheet, change and save. After a month, you have an August report again, and you need to make the same change.

With Excel, users need to open the workbook and make all changes to get the same results. Depending on the amount of change, this process may take a long time. However Power Query can solve this problem.

When connecting a workbook using Power Query and making changes, these changes are saved as steps. The steps are just the instructions you give Excel to change the data (for example, delete this column or sort this table by color).

All steps will be saved together, creating a compact package. You can apply this group of steps to a new workbook with just one mouse click, instead of making changes from one table to another.

4. No programming knowledge required

Excel can be programmed with Visual Basic for Application (VBA), but there is a lot to learn. You can also use other programming languages ​​with Excel but require a lot of setup.

1. How to install VBA in Excel

Fortunately, you do not need programming knowledge to use Power Query. The editor has an easy to use graphical interface. You just need to click on the interface to make changes. It looks very similar to Excel.

5. Collect data from different sources

Power Query can retrieve data from various places. If you want to work with data from the web, you can enter it easily. Power Query can also retrieve data from various sources, such as:

1. CSV file
2. Text file
3. SQL database
4. XML file
5. Microsoft Access Databases

There are many other sources you can use, but don't do.

Why not just use Microsoft Excel?

Excel is quite powerful, no doubt about it. But if you use different sheets, you will have a lot of work to manage.

Power Query is not a spreadsheet but a dashboard for you to work with data. The layout is similar to Excel to help you work more easily. You can work very efficiently by combining the strengths of Excel and Power Query.

There are lots of great reasons to start using Power Query spreadsheet manager. If you master the basics of this program, you can create tables and work with data much easier.