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.
Important: Data Catalog Deprecation Notice
Data Catalog capabilities in Excel and Power Query stopped working on March 4th, 2019. Users were able to consume content already published to the Data Catalog until that date. However, starting on December 3rd, 2018, users were not be able to publish (share) new or updated queries into the Data Catalog. Starting on August 1st, 2018, we stopped onboarding new customers (tenants) to use this feature. We recommended downloading your shared queries so you could continue using them outside the Data Catalog beyond March 4th, 2019, from the My Data Catalog Queries task pane, using the Open option for each of the queries.
With Power Query, you can search for data sources, make connections, and then shape that data (for example remove a column, change a data type, or merge tables) in ways that meet your needs. Once you’ve shaped your data, you can share your findings or use your query to create reports.
Looking at those steps in order, they often occur like this:
Connect – make connections to data sitting in the cloud, in a service, or locally
Transform – shape the data to meet your needs; the original source remains unchanged
Combine - create a data model from multiple data sources, and get a unique view into the data
Share – once your query is complete, you can save, share or use it for reports
Power Query records each step you take, and lets you modify those steps in any way you need. It also lets you undo, redo, change the order, or modify any step… all so you can get your view into the connected data just the way you want it.
With Power Query, you can create queries that are as simple or complex as you need. And since Power Query uses the M Language to record and carry out its steps, you can create queries from scratch (or tweak them manually) to harness the power and flexibility of data scripting, all within Power Query.
You can access Power Query from the Power Query ribbon in Excel.
Important: If you are using Excel 2013 or earlier versions of Excel, 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.
You can use Power Query to connect to a single data source, such as an Excel workbook, or you can connect to multiple databases, feeds, or services scattered across the cloud. With Power Query, you can then bring all those sources together using your own unique combinations, and uncover insights you otherwise wouldn’t have seen.
You connect to data sources from the Power Query ribbon, in the Get External Data section. Data sources include data from the Web, File, Database, Azure, Other Sources, or even Tables in an Excel workbook.
The following quick video shows the multitude of data source types to which Power Query can connect. New data connections are being added all the time, so make sure you always have the most recent version of Power Query.
When you connect to a data source, a Preview pane will appear. Click Load if you want to work with the data in Excel right away. But if you want to apply transformations or shape the data beforehand, click Edit. Power Query will then launch the Query Editor: a dedicated window that facilitates and displays your data connections and transformations you apply. The next section, Transform, provides more information about the Query Editor.
Learn more about Connect:
Power Query lets you transform the data from your connections in ways that help you analyze it. Transforming data means modifying it in some way to meet your needs – for example, you could remove a column, change a data type, or merge tables – each of which is a data transformation. As you transform data, it collectively takes on the shape you need to further your analysis. The process of applying transformations to one or more sets of data is often called shaping data.
Power Query uses a dedicated window called the Query Editor to facilitate and display data transformations. You can open the Query Editor by selecting Launch Editor from the Power Query ribbon.
The Query Editor also opens whenever you connect to a data source, create a new query, or Load an existing query.
Power Query keeps track of everything you do with the data. The Query Editor records and labels each transformation, or step, you apply to the data. Whether the transformation is a data connection (a data source), a column removal, a merge, or a data type change, the Query Editor tracks each operation in the APPLIED STEPS section of the Query Settings pane.
The transformations you apply to your data connections collectively constitute your query.
It’s important (and helpful) to realize that Power Query doesn’t change the original source data. Instead, Power Query records each step you take when connecting or transforming the data, and once you’ve finished shaping the data, it takes a snapshot of the refined data set and brings it into Excel.
There are many, many transformations you can apply to data. You can also write your own transformations using the M Language (which is how Power Query records steps in the background), using Query Editor’s Advanced Editor. You can open the Advanced Editor from the Query Editor’s Transform ribbon, where you can modify the M Language steps associated with the existing query. You can also create queries from scratch using the Advanced Editor.
Learn more about Transform:
When you save an Excel workbook that contains a query, the query is automatically saved as well. You can view all queries in an Excel workbook by selecting Show Pane from the Workbook Queries section of the Power Query ribbon.
The Workbook Queries pane shows all queries in the workbook.
But why stop there? With Power Query and the Data Catalog, you can share your queries with anyone in your organization. Or create a query that you’ll use frequently, then use it in multiple workbooks and save yourself work. Instead of saving and emailing Excel workbooks (and trying to juggle which version is the original, what has changed, or whether its data is stale!), save a query to the Data Catalog and avoid the headache of countless untracked workbook versions filling inboxes. Just right-click on a query in the Workbook Queries pane, and a menu provides all sorts of options, including Send To Data Catalog.
Notice the other options in the right-click menu, too. You can Duplicate a query, which lets you change certain elements (or all elements) of a query without changing the original query; it’s like creating a query template that you can then modify to create customized datasets – like one dataset for retail, another for wholesale, and another for inventory, all of them based on the same data connections.
You can also Merge or Append queries, which lets you turn queries into reusable building blocks.
With Power Query, you can get creative with your data and your connections, as well as your transformations, then amplify your work by sharing it with others (or with yourself, when you’re on another device).
With the Data Catalog, you can easily see all your shared queries, too.
The My Data Catalog Queries pane opens, showing all the queries you’ve shared. And from there, you can choose to load a query, edit it, or otherwise use that query in the workbook you’re currently working on.
With your query complete, you can use it to create reports in Excel, in Power View or in Power BI. Take a look at the following section to learn more about reporting resources that let you take advantage of all the good work you did when using Power Query to shape data just the way you want it.
Learn more about Save, Share, Report:
General Data Protection Regulation (GDPR)
In May 2018, a European privacy law, the General Data Protection Regulation (GDPR), took effect. The GDPR imposed new rules on companies, government agencies, non-profits, and other organizations that offer goods and services to people in the European Union (EU), or that collect and analyze data tied to EU residents. The GDPR applies no matter where you are located.
To learn more about your privacy options regarding updating, deleting, or exporting personal data from Power Query workbooks, see: View and update a shared query (Power Query).
You can also copy query settings from Power Query's Edit Query Definition dialog. The mashup can be downloaded by double-clicking on the it in the My Data Catalog Queries task pane in Excel. All of the queries will then be loaded to the Excel workbook. If you want to see individual queries, open the Query Editor and browse through each step individually, or go to the View tab and open the Advanced Editor.