Microsoft Power Query for Excel Help
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.
Microsoft Power Query for Excel provides data discovery, data transformation and enrichment for the desktop to the cloud.
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
Microsoft Power Query for Excel provides an intuitive user interface for data discovery, data transformation and enrichment.
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.
Microsoft Power Query for Excel provides an intuitive user interface for data discovery, data transformation and data enrichment.
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.
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.
In this tutorial, you learn how to retrieve and transform a table of data from a web page.
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
With the query editor, you can navigate, define, and perform data transform operations over a data source.
You can modify a formula other than those associated with a builder, or you can alter an existing formula.
With the Steps pane, you can add, edit, reorder, or delete query steps to change how your data is transformed.
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 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 based on 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. For more information about how to group rows, see the Combine data from multiple data sources tutorial.
Shape data in 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 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 an Index or Custom (you define the formula) column to your current query.
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.
Promotes a row to column headers.
Remove selected columns or Remove Other Columns from a query.
Rename a data source column. The new column name is used in the query.
Replace one value with another value in the selected columns.
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
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.
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 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 your shared queries to edit query definition or metadata.
Certify queries to let users know that they can trust the underlying data.
Replace your shared query.
Delete your shared query.
Manage your queries in an Excel workbook from a single place.
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.
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 shared query usage analytics while managing your shared queries in Power Query.
Create advanced queries using the Power Query formula language.
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.
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.
Formulas in Power Query are used to perform operations, such as data acquisition or transformation operations.
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 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.