Shape data (Power Query)

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

With Power Query, you can shape data from multiple sources by editing the query steps to match your data analysis requirements.

Resources to learn about shaping data

These resources will help you learn how to use Power Query.

Introduction to Microsoft Power Query for Excel — With the Query Editor, you can navigate, define, and perform data-transform operations over a data source.

How-to : Import data from external data sources — With Microsoft Power Query for Excel, you can import data into Excel from a wide variety of data sources. You use the Query Editor to shape data by editing query steps.

Shape or transform a query — Shape data from multiple data sources by adding, removing or editing query steps to match your data analysis requirements.

Refresh a query — Refresh a query to import the latest data into a table without having to create the query again.

Combine data from multiple data sources — take data from different data sources and combine it.

Filter a table— Filter a table to reduce the size of query results by excluding rows or columns based on size, value, or condition.

Sort a table — Sort table rows in your query results ranked by a criteria, such as the alphabetical or numerical value of one or multiple columns, and by ascending or descending order.

Group rows in a table — Group the values from various rows into a single value—according to the values in one or more columns.

Expand a column containing an associated table — Expand a column containing an associated table to reveal the related data, then extract some or all column values from that table.

Aggregate data from a column — Aggregate data from any column containing an associated table to reveal the results of a group operation, including Sum, Count, Average, Min, and Max.

Insert a custom column into a table — Insert an Index or Custom column to your current query.

Edit query step settings — With the Steps pane, add, edit, reorder, or delete query steps to change how data is transformed.

Combine multiple queries — Combine multiple queries, by merging or appending them. The Merge and Append operations are performed on any query with a tabular shape, independent of the source of the data.

Merge columns — Merge values in two or more columns in a query.

Remove columns — Remove selected columns or Remove Other Columns from a query.

Remove rows with errors — Remove rows from a query that exhibits data errors.

Promote a row to column headers— Promote a row to be a column-heading row.

Split a column of text — Split a column of text into multiple columns, either by delimiter or by a number of characters.

Insert a query to the worksheet — Insert data from a query into an Excel worksheet. When you insert data from a query, you can choose to load a query to the Excel Data Model.