Microsoft Power Query for Excel provides data discovery, data transformation and enrichment for the desktop to the cloud.
Power Query is available in two editions *:
- Power Query 1.5
- Power Query 2.10. Features available only in version 2.10 are noted as [Power Query version 2.10].
* The specific General Availability (GA) version numbers are Power Query 1.5.3296.2082 and Power Query 2.10.3547.461.
More about Power Query for Excel
Microsoft Power Query for Excel provides an intuitive user interface for data discovery, data transformation and enrichment. With the Power BI for Office 365 edition, you can share and manage queries as well as search data within your organization. For more information about how to share queries, see Share Queries.
Introduction to Microsoft Power Query for Excel
Microsoft Power Query for Excel provides an intuitive user interface for data discovery, data transformation and data enrichment.
Import data from external data sources
With Power Query, you can import data into Excel from a wide variety of data sources. After you connect to a data source, you can shape data to match your data analysis requirements.
Shape data from multiple data sources by editing query steps to match your data analysis requirements.
Add a query to an Excel 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.
Introduction to the Data Management Experience in Power BI for Office 365 [Power Query version 2.1]
The Data Management experience in Power BI provides self-service capabilities to enable business users and data experts to easily connect to a wide variety of data sources, transform the data into the desired shape, and then securely share the data within the enterprise from within the familiar environment of Excel using Power Query.
Power Query 101
In this tutorial, you learn how to retrieve and transform a table of data from a web page.
Combine data from multiple data sources
In this tutorial, you import data from a local Excel file containing product information, and from an OData feed containing product order information. You perform transformation and aggregation steps, and combine data from both sources to produce a Total Sales per Product and Year report.
Search public data (currently United States data sources only)
Search public datasets to import data from a large collection of public data sources.
Using the Query Editor
Introduction to the Query Editor
With the query editor, you can navigate, define, and perform data transform operations over a data source.
Modify a formula
You can modify a formula other than those associated with a builder, or you can alter an existing formula.
Edit query step settings
With the Steps pane, you can add, edit, reorder, or delete query steps to change how your data is transformed.
Refresh a query
Refresh a query to import the latest data into a table without having to create the query again.
Filter, sort, and group data
Filter a table
Filter a table to reduce the size of your query results by excluding rows or columns based on size, value or condition.
Sort a table
Sort table rows in your query results based on 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 in a number of rows into a single value by grouping the rows based upon the values in one or more columns. For more information about how to group rows, see the Combine data from multiple data sources tutorial.
Shape data in a query
Shape or transform a query
Shape data from multiple data sources by adding, removing or editing query steps to match your data analysis requirements.
Removes all rows from the table where the values in the selected columns duplicate earlier values. The row with the first occurrence of a value set is not removed.
Remove rows with errors
Remove rows from a query with data errors.
Privacy levels specify an isolation level that defines the degree that one data source is isolated from other data sources.
Working with columns
Insert a custom column into a table
Insert an Index or Custom (you define the formula) column to your current query.
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.
Merge values in two or more columns into a single column in a query.
Promote a row to column headers
Promotes a row to column headers.
Remove selected columns or Remove Other Columns from a query.
Rename a column
Rename a data source column. The new column name is used in the query.
Replace one value with another value in the selected columns.
Split a column of text
A column of text can be split into multiple columns in two ways: by delimiter or by a number of characters.
Transforms selected columns into attribute-value pairs.
Combine data from related queries
Combine multiple queries
With Power Query, you can seamlessly 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.
The Merge operation creates a new query from two existing queries.
The Append operation creates a new query that contains all rows from a first query followed by all rows from a second query.
Drill down into a related table
In Power Query, a column containing a link to a related table has an Entry link or Table link. An Entry link navigates to a single related record. A Table link navigates to a related table.
Expand a column containing a related table
Expand a column containing an associated table to reveal the related data. You can extract all column values or specific column values within the related table.
Share queries [Power Query version 2.10]
After you have connected to the required data sources and improved (filtered, shaped, and transformed) the data to match your requirements, you can share the metadata of the resultant dataset as query in Power Query with all or specific set of users within the enterprise.
Manage shared queries
View and update shared queries
View and update your shared queries to edit query definition or metadata.
Certify queries to let users know that they can trust the underlying data.
Replace a Shared Query
Replace your shared query.
Delete a shared query
Delete your shared query.
View and Manage Queries in a Workbook
Manage your queries in an Excel workbook from a single place.
Find and use a shared query
Find and use a shared query
You can find and use a shared query to use the underlying data in the queries for data analysis and reporting.
Request access to internal data sources
For a shared query, you can request access to an internal data source so you can import data from a shared query.
View Usage Analytics for Your Shared Queries
View Usage Analytics for Your Shared Queries from Power Query
View shared query usage analytics while managing your shared queries in Power Query.
Create an advanced query
Create advanced queries using the Power Query formula language.
Power Query formula categories
Data source prerequisites
Microsoft Power Query for Excel supports a large number of data source providers. For each provider, Power Query supports a specific provider version and objects.
Data source settings
Microsoft Power Query for Excel saves a data source credential, or sign in identity, for each data source connection you have used and data source privacy levels.
Guide to Power Query Context Menus
Guide to the Power Query Ribbon Tabs
Learn about Power Query Formulas
Formulas in Power Query are used to perform operations, such as data acquisition or transformation operations.
Power Query formula categories
Accessibility Features in Microsoft Power Query for Excel
Power Query is dedicated to ensure that functionality is available to people with disabilities. In addition to incorporating accessibility features that are consistent with Microsoft Excel 2013, Power Query incorporates accessibility support for common scenarios and provides workarounds where possible.
Power Query specifications and limits
Power Query has various internationalization capabilities to visualize data for your locale. In the Windows operating systems, a locale is a set of user preference information related to the user's language, environment and/or cultural conventions.
Security Power Query adheres to the Crypto Mobility guidelines, according to the Microsoft SDL Process, by encrypting local credentials using DPAPI.
Microsoft Power Query Terms of Service