With Get & Transform in Excel 2016, 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 it, share it, or use it for reports
Whenever you connect to data, transform it, or combine it with other data sources, a feature of Get & Transform called Query Editor records each step, and lets you modify each step in any way you need. Query Editor also lets you undo, redo, change the order, or modify any step… all so you can shape your view of the connected data just the way you want it.
With Get & Transform, you can create queries that are as simple or complex as you need. As you add steps to a query, Query Editor works behind the scenes to create a set of discrete instructions that carry out your commands. Those instructions are created in the M Language. Users who enjoy the power and flexibility of data scripting can create M Language to manually create (or tweak ) queries using the Advanced Editor. All of this power and flexibility, whether creating steps automatically or manually, is part of the Get & Transform collection of features in Excel 2016. Query Editor and the Advanced Editor are described in more detail later in this article.
You can begin a new query by selecting the New Query button from the Get & Transform ribbon in Excel 2016.
Note: This technology is also available for previous versions of Excel with the Power Query Add-In, which is available as a download, as well as in Power BI. To see Power Query in action in previous versions of Excel, take a look at Getting Started with Power Query.
You can use a query to connect to a single data source, such as an Access database, or you can connect to multiple files, databases, OData feeds, or Web sites scattered across the Internet. With Get & Transform, you can then bring all those sources together using your own unique combinations, and uncover insights you otherwise wouldn’t have seen.
When you select New Query from the Get & Transform ribbon section in the Data tab, the available data sources are presented in a menu. There are many data sources to choose from, including files like Excel workbooks or CSV files, databases such as Access, SQL Server, Oracle, and MySQL, Azure services such as HDInsight or Blob Storage, and all sorts of other sources such as the Web, SharePoint Lists, Hadoop Files, Facebook, Salesforce, and many others.
Note: Some data sources are only available on Professional and Professional Plus licenses.
When you connect to a data source, Get & Transform displays a Navigator window, which allows you to edit the data from the source. When you select Edit from the Navigator window, Get & Transform launches Query Editor, a dedicated window that facilitates and displays your data connections and the transformations you apply. The next section, Transform, provides more information about Query Editor.
Get & Transform lets you transform the data from your data sources 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.
Excel uses a dedicated window called Query Editor to facilitate and display data transformations. When you select New Query from the Get & Transform ribbon section of the Data tab, then select the data source (such as a workbook, or a database), the Navigator window appears so you can select which table (or tables) you want to use in your query. When you select a table, a preview of its data is shown in the right pane of the Navigator window.
If you select Load, the data source is brought into Excel directly. In this case, we select Edit to launch Query Editor.
Query Editor keeps track of everything you do with the data. 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, 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 the actions you define in Query Editor don't change the original source data. Instead, Excel 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 the workbook.
There are many, many transformations you can apply to data. You can also write your own transformations using the M Language (which is how Query Editor records steps in the background), using Query Editor’s Advanced Editor. You can open the Advanced Editor from Query Editor’s View 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.
When you’re done creating your query you can select Close & Load from the Home ribbon tab, and the query results are loaded into Excel and available in a new workbook tab.
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 Queries from the Get & Transform ribbon section of the Data tab.
The Workbook Queries pane shows all queries in the workbook.
But why stop there? With the Power BI 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.
You can also publish your workbook to Power BI, and create online reports that can be shared with your group, refreshed automatically, and refined. To publish a workbook to Power BI, select File > Publish > Publish to Power BI.
Note: Your workbook must be saved to OneDrive for Business to publish it to Power BI.
With your query complete, you can use it to create reports in Excel, 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 Get & Transform functionality to shape data just the way you want it.
Learn more about Share: