Shape data (Power Query)
Important This article applies to Power Query, a data analysis feature available for Excel that lets you discover, combine and refine data. You may need to enable Power Query in Excel. You can also download and install the most recent version of Power Query for Excel, which automatically enables it. Power Query technology is also built into the Power BI Designer, which is a stand-alone report authoring and data transformation tool for Power BI.
With Microsoft Power Query for Excel, you can shape data from multiple data sources by editing query steps to match your data analysis requirements.
Resources to learn about shaping data
The following resources will help familiarize you with Power Query.
With the Query Editor, you can navigate, define, and perform data transform operations over a data source.
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 data from multiple data sources by adding, removing or editing query steps to match your data analysis requirements.
Refresh a query to import the latest data into a table without having to create the query again.
Combine data from multiple data sources.
Filter a table to reduce the size of your query results by excluding rows or columns based on size, value or condition.
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 the values in a number of rows into a single value by grouping the rows based upon the values in one or more columns.
Expand a column containing an associated table to reveal the related data. You can extract all column values or selective column values within the related table.
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 an Index or Custom (you define the formula) column to your current query.
With the Steps pane, you can add, edit, reorder, or delete query steps to change how your data is transformed.
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 data source that the data comes from.
Merge values in two or more columns in a query.
Remove selected columns or Remove Other Columns from a query.
Remove rows from a query with data errors.
Promote a row to column headers.
Split a column of text into multiple columns in two ways: by delimiter or by a number of characters.
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.