Power query overview and learning

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. For a summary of all Power Query help topics, see Power Query for Excel Help.

Note: Power Query in Excel for Windows uses the .NET framework, but it requires version 4.7.2 or later. You can download the latest .NET Framework from here. Select the recommended version and then download the runtime.

There are four phases to using Power Query.

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

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

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

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

The following sections explore each phase in more detail. 

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. For more information, see Refresh an external data connection in Excel.  

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.

Think of it this way. A vase starts as a lump of clay that one shapes into something practical and beautiful. Data is the same. It needs shaping into a table that is suitable for your needs and that enables attractive reports and dashboards.

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 labelling 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.

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.

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.

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.

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). 

For over ten years, Power Query has been supported on Excel for Windows. Now, Excel is broadening Power Query support on Excel for Mac and adding support to Excel for the Web. This means we are making Power Query available on three major platforms and demonstrates the popularity and functionality of Power Query among Excel customers. Watch for future announcements on the Microsoft 365 roadmap and What's new in Excel for Microsoft 365.

The integration of Get & Transform Data (now called Power Query), into Excel has gone through a number of changes over the years. 

Excel 2010 and 2013 for Windows

In Excel 2010 for Windows, we first introduced Power Query and it was available as a free add-in that could be downloaded from here: Download the Power Query add-in. Once enabled, Power Query functionality was available from the Power Query tab on the ribbon.

Microsoft 365

We updated Power Query to be the primary experience in Excel for importing and cleaning data. You can access the Power Query data import wizards and tools from the Get & Transform Data group on the Data tab of the Excel ribbon.

This experience included enhanced data import functionality, rearranged commands on the Data tab, a new Queries & Connection side pane, and the continuing ability to shape data in powerful ways by sorting, changing data types, splitting columns, aggregating the data, and so on.

This new experience also replaced the older, legacy data import wizards under the Data command in the Get External Data group. However, they can still be accessed from the  Excel Options dialog box (Select File > Options > Data > Show legacy data import wizards).

Excel 2016 and 2019 for Windows

We added the same Get & Transform Data experience based on the Power Query technology as that of Microsoft 365.

Excel for Microsoft 365 for Mac

In 2019 we started the journey to support Power Query in Excel for Mac. Since then, we added the ability to refresh Power Query queries from TXT, CSV, XLSX, JSON and XML files. We have also added the ability to refresh data from SQL server and from tables & ranges in the current workbook.

In October of 2019, we added the ability to refresh existing Power Query queries and to use VBA to create and edit new queries.

In January of 2021, we added support for the refresh of Power Query queries from OData and SharePoint sources.

For more information, see Use Power Query in Excel for Mac.

Note    There is no support for Power Query on Excel 2016 and Excel 2019 for Mac.

Data Catalog deprecation

With the Data Catalog, you could view your shared queries, and then select them to load, edit, or otherwise use in the current workbook. This feature was gradually deprecated:

• On August 1st, 2018, we stopped onboarding new customers to the Data Catalog.

• On December 3rd, 2018, users couldn't share new or updated queries in the Data Catalog.

• On March 4th, 2019, the Data Catalog stopped working. After this date, we recommended downloading your shared queries so you could continue using them outside the Data Catalog, by using the Open option from the My Data Catalog Queries task pane.

Power Query add-in deprecation

Early in the summer of 2019, we officially deprecated the Power Query add-in which is required for Excel 2010 and 2013 for Windows. As a courtesy, you may still use the add-in, but this may change at a later date.

Facebook data connector retired

Import and refresh of data from Facebook in Excel stopped working in April, 2020. Any Facebook connections created before that date no longer work. We recommend revising or removing any existing Power Query queries that use the Facebook connector as soon as possible to avoid unexpected results.