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.
Power Query provides data discovery, data transformation and enrichment for the desktop to the cloud.
Standard Power Query features
Easily discover, combine, and refine data for better analysis in Excel.
-
Filter, sort, and group data
* 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.
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.
Tutorials
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.
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.
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 a custom column into a table
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.
Promote a row to column headers
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.
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.
View and Manage Queries in a Workbook
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.
View Usage Analytics for Your Shared Queries
What is Power BI administration?
View shared query usage analytics while managing your shared queries in Power Query.
Advanced queries
Create advanced queries using the Power Query formula language.
Reference
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 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
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.
Crypto Mobility
Security Note: Power Query adheres to the Crypto Mobility guidelines, according to the Microsoft SDL Process, by encrypting local credentials using DPAPI.
No comments:
Post a Comment