Microsoft Power Query for Excel Help

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

Standard Power Query features

Value-added features with an Office 365 Power BI subscription

Easily discover, combine, and refine data for better analysis in Excel.

In addition to the features in the standalone edition, securely share and manage your data queries within the enterprise in Excel.

* 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

Getting started

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

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.

Tutorials

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.

Connect to SAP BusinessObjects BI Universes with Power Query

In this tutorial, you learn how to connect, navigate, and shape a data set from dimensions and measures in an SAP BusinessObjects BI Universe with Power Query for Excel.

Search data

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.

Remove duplicates

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

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 columns

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 columns

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 values

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.

Unpivot columns

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.

Merge queries

The Merge operation creates a new query from two existing queries.

Append 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]

Share queries

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

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.

Advanced queries

Create an advanced query

Create advanced queries using the Power Query formula language.

Power Query formula categories

Reference

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.

Glossary

Power Query specifications and limits

Troubleshooting

Internationalization

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.

Crypto Mobility

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

 
 
Applies to:
Excel 2013, Excel 2010