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 it however you want. 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 manually create or change M Language queries using the Advanced Editor. Query Editor and the Advanced Editor are described in more detail later in this article.
You can begin a new query from the Data tab > Get Data > Select a data source. If you don't see the Get Data button, then select the New Query button from the Data tab. Here's an example of choosing from a database source.
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. You can then bring all those sources together using your own unique combinations, and uncover insights you might not otherwise have seen.
When you select Get Data from the Get & Transform section on the Data tab (or New Query if you don't see the Get Data button), you'll see 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 others.
Note: You can learn about what data sources are available in which Excel versions here: Where is Get & Transform (Power Query).
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 Query Editor to facilitate and display data transformations. When you select Data > Get Data, 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 directly into Excel as is. If you select Edit, that will 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 connection to a data souce, removing a column, 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 to know 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, using Query Editor’s Advanced Editor. You can open the Advanced Editor from Query Editor’s Home or View tabs, where you can modify the M Language steps associated with the existing query, or create your own.
When you’re done creating your query you can select Close & Load from the Home 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 Queries & Connections on the Data tab.
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, figuring out what has changed, or whether its data is stale, save a query to the Data Catalog. Just right-click on a query in the Queries & Connections 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 some 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: