Excel is the primary data tool for many business users. However, we all know that data often comes in different forms, from different sources, and your reporting tools can be demanding as to what you can feed them. Reshaping data is often necessary for a clean database structure and creating reports. Power Query can do wonders when it comes to manipulating or cleaning up data tables. Let’s take a look at what this nifty add-in can do to make our lives easier.

What is Power Query?

Also known as Get & Transform, Power Query is a free add-in that is a part of Microsoft’s ‘Power Suite’. Power Query can be downloaded as an optional add-in for Excel 2010 and 2013. Starting with Excel 2016, it comes as a built-in feature. This tool allows you to search for data sources, create connections between them, and transform & shape that data. Examples of transforming and shaping data include,

Find & replace text
Split or merge columns
Sort & filter columns
Add static or calculated columns
Unpivot data for use in pivot tables

Power Query uses a formula language called M, which is a mashup query language designed to build data queries. In several ways, it is similar to F-Sharp. According to Microsoft, it "is a mostly pure, higher-order, dynamically typed, partially lazy, functional language."

Power Query add-in is featured as “Get & Transform” in Excel 2016 and later. You can find it under the Data tab of the ribbon in the Get & Transform section.

For Excel 2010 and 2013, you can download the add-in from here. Both 32-bit and 64-bit versions are supported. However, Power Query is not compatible with any Mac versions of Excel at the time of writing this.

Getting Started with Power Query

Connect to a data source

We must begin by pointing our data source. If you are using Excel 2016 or a newer version, go to the Data tab, and then click New Query. We can choose to pull data from our workbook, an external database, or workbook. Find the option that applies to your case.

If you are using Excel 2010-2013, click the Power Query tab to go to the same feature.

After locating the data source, you will be forwarded to the Navigator window where you can see a preview and choose which table you want to import (or tables, if you have more than one).

Double-click the table name, or click Edit after selecting the table(s).

Shape & transform

Once connected to a data source, Query Editor window will be filled with the selected data.

From here we can,

Start re-shaping the data and save our queries from the toolbar.
Track all updates from the APPLIED STEPS
Manage other queries from the expandable Queries

Please note that Power Query will never override your original data source. Instead, it will save each step, and then create a log under APPLIED STEPS. We can trace back our steps to modify, update, or change order of these steps. Right-click on an item on this list to see available options.

We can access the detailed query code by clicking the Advanced Editor button under the Home tab. We can even create custom queries from scratch, using the M Language.

We can access the Query Editor at any time by clicking the Launch Editor icon on the ribbon.

Loading the Table

Click Close & Load to send the queried data back to Excel as a data Table.

Once loaded, we’re going to see a new pane on the right side named Workbook Queries. This window will display existing queries and can also handle data operations such as copying, merging the data with other queries, or sharing it via Data Catalog.

Another way to load the table is to click Close & Load under the arrow next to the Close & Load icon.

To create an Excel Connection without a Table, or insert the data into a Data Model to work with your data using Power Pivot (which we covered here), right click Workbook Queries and select Load To…

Power Query can answer most data configuration operations in Excel. It’s the perfect tool for reshaping data, and can be a life-saver when it comes to analyzing data and reporting.