How to use the Power Query Editor in Excel

Posted on:

Using the Power Query Editor in Excel

With Power Query (known as Get & Transform in Excel), you can import or connect to external data, and then shape that data, for example remove a column, change a data type, or merge tables, in ways that meet your needs. Then, you can load your query into Excel to create charts and reports. Periodically, you can refresh the data to make it up to date. Power Query is available on three Excel applications, Excel for Windows, Excel for Mac and Excel for the Web.

Power Query Ribbons

Power Query Editor in Excel

The 4 Phases of Power Query

1. Connect

Make connections to data in the cloud, on a service, or locally.

You can use Power Query to import to a single data source, such as an Excel workbook, or to multiple databases, feeds, or services scattered across the cloud. Data sources include data from the Web, files, databases, Azure, or even Excel tables in the current workbook. With Power Query, you can then bring all those data sources together using your own unique transformations and combinations to uncover insights you otherwise wouldn’t have seen. Once imported, you can refresh the data to bring in additions, changes, and deletes from the external data source.

2. Transform

Shape data to meet your needs, while the original source remains unchanged.

Transforming data means modifying it in some way to meet your data analysis requirements. For example, you can remove a column, change a data type, or filter rows. Each of these operations is a data transformation. This process of applying transformations (and combining) to one or more sets of data is also called shaping data.

Power Query uses a dedicated window called the Power Query Editor to facilitate and display data transformations. You can open the Power Query Editor by selecting Launch Query Editor from the Get Data command in the Get & Transform Data group, but it also opens when you connect to a data source, create a new query, or load a query.

The Power Query Editor keeps track of everything you do with the data by recording and labeling each transformation, or step, that you apply to the data. Whether the transformation is a data connection, a column removal, a merge, or a data type change, you can view and modify each transformation in the APPLIED STEPS section of the Query Settings pane. There are many transformations you can make from the user interface. Each transformation is recorded as a step in the background. You can even modify and write your own steps using the Power Query M Language in the Advanced Editor.

All the transformations you apply to your data connections collectively constitute a query, which is a new representation of the original (and unchanged) data source. When you refresh a query, each step runs automatically. Queries replace the need to manually connect and shape data in Excel.

3. Combine

Integrate data from multiple sources to get a unique view into the data.

You can combine multiple queries in your Excel workbook by appending or merging them. The Append and Merge operations are performed on any query with a tabular shape and are independent of the data sources that the data comes from.

3.1 Append

An append operation creates a new query that contains all rows from a first query followed by all rows from a second query. You can perform two types of append operations:

  • Intermediate Append

Creates a new query for each append operation.

  • Inline Append

Appends data to your existing query until you reach a final result.

3.2 Merge

A merge operation creates a new query from two existing queries. This one query contains all columns from a primary table, with one column serving as a navigation link to a related table. The related table contains all rows that match each row from a common column value in the primary table. Furthermore, you can expand or add columns from a related table into a primary table.

4. Load

Complete your query and load it into a worksheet or Data Model and periodically refresh it.

There are two main ways to load queries into your workbook:

  • From the Power Query Editor, you can use the Close and Load commands in the Close group on the Home tab.
  • From the Excel Workbook Queries pane (Select Queries & Connections), you can right-click a query and select Load To.
    You can also fine-tune your load options by using the Query Options dialog box (Select File > Options and settings > Query Options) to select how you want to view your data and where you want to load the data, either in a worksheet or a Data Model (which is a relational data source of multiple tables that reside in a workbook).

Find out more about Microsoft Power Query

Link to Microsoft Power Query

 

Back