- Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016.
The purpose of this blog post is to give you a starting point to how Get & Transform works in Excel. It will explore each of the steps involved in this process sequentially and include other resources you can peruse to aid your knowledge and understanding.
The Get & Transform function in Excel enables you to search for data sources, create connections before proceeding to shape that data (for example, delete a column, edit a data type, or merge tables) in ways that fulfil your requirements. After you’ve shaped your data, you can disseminate your findings or reference your query to produce reports.
Get & Transform Steps
Seeing those steps in order, they typically happen like this:
- Connect – make connections to data residing in the cloud, in a service, or locally.
- Transform – shape the data to match your needs; the initial source stays intact.
- Combine – design a data model from numerous data sources, and obtain a unique perspective into the data.
- Manage – when your query is complete, you can save it, copy it, or relay it for reports.
Anytime you connect to data, transform it, or combine it with different data sources, an aspect of Get & Transform, known as Query Editor, records every step. This enables you to amend it in whichever way you prefer. Query Editor even allows you to undo, redo, modify the order, or adjust any step… all so you can shape your view of the connected data in your desired way.
Using Get & Transform, you can formulate basic or complex queries to fulfil your requirements. While you insert steps to a query, Query Editor operates covertly to generate a range of discrete instructions that perform your commands. Those instructions originate in the M Language. Users who relish the control and versatility of data scripting can physically make or alter M Language queries using the Advanced Editor. Query Editor and the Advanced Editor are available in extra detail further in this post.
You can start a new query from the Data tab > Get Data > Select a data source. If you cannot see the Get Data button, then press the New Query button from the Data tab. Below is an example of picking from a database source.
Note: Power Query is also available for former versions of Excel as an add-in, alongside in Power BI. To witness Power Query in action in past versions of Excel, check out Getting Started with Power Query.
With a query, you can connect to one data source, like an Access database. Alternatively, you can connect to several files, databases, OData feeds, or websites. Subsequently, you can compile each of those sources into one integrated database by applying your personalised combinations and discover perspectives only revealed in this product.
Once you choose Get Data from the Get & Transform section on the Data tab (or New Query if the Get Data button is missing from your view), you’ll notice multiple data sources to pick from. These comprise files like Excel workbooks or Text/CSV files, databases including Access, SQL Server, Oracle, and MySQL, and Azure services like HDInsight or Blob Storage. Also, various kinds of other sources like the Web, SharePoint Lists, Hadoop Files, Facebook, Salesforce, and others.
Note: You can find out more about what data sources are available in which Excel versions here: Where is Get & Transform (Power Query).
Once you connect to a data source, Get & Transform presents a Navigator pane. This enables you to amend the data from its source. Once you choose Edit from the Navigator window, Get & Transform displays the Query Editor. This effectively is a tailored window that simplifies and showcases your data connections and the your implemented transformations. The next section, Transform, offers additional information about Query Editor.
Another functionality of Get & Transform is transform which allows you to transform the data from your data sources in meaningful ways to inform your data analysis. Transforming data means altering it in a particular way to achieve your goals – for example, you could delete a column, modify a data type, or merge tables . Each of these qualifies as a data transformation. While you transform data, it holistically adapts to the shape you require to enrich your analysis. The method of assigning transformations to one or multiple datasets is usually known as shaping data.
Excel relies upon a corresponding Query Editor to accelerate and highlight data transformations. Once you choose Data > Get Data, then click the data source, like a workbook, or a database. Following this, the Navigator window emerges allowing you to confirm which table (or tables) you aim to use in your query. After you pick a table, a preview relating to its data is reflected in the right pane of the Navigator window.
If you choose Load, the data source will materialise into Excel as well. If you press the Transform Data option, that will emerge Query Editor.
More about Query Editor
Query Editor has the following capabilities:
- Logs all your interactions with the data.
- Can track and entitle each transformation, or step, you import to the data.
- Monitors every action in the Applied Steps section of the Query Settings pane. This happens regardless of whether the transformation is a connection to a data source, erasing a column, a merge, or a data type modification.
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 transformations you can apply to data. You can also write your own transformations using the M Language with 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. The query results will then appear in Excel and available in a new workbook tab.
Learn more about Transform:
- Introduction to the Query Editor (Power Query)
- Add a query to an Excel worksheet (Power Query)
- Edit query step settings (Power Query)
- Shape data (Power Query)
After you save an Excel workbook including a query, the query is saved by default too. You can see every query in an Excel workbook by choosing Queries & Connections on the Data tab.
Any time you right-click directly over a query title in the Queries & Connections pane, you will presented with multiple options. For example, you can Duplicate a query, allowing you to make alterations to part or the entire aspects of a query excluding any effect to the initial query. It’s similar to designing a query template that you can subsequently adjust to form tailored datasets – such as one for retail, another for wholesale, and a different one for inventory, collectively, they are associated with the equivalent data connections.
You can even Merge or Append queries, enabling you to convert queries into recyclable stepping stones.
Additionally, you can publish your workbook to Power BI, and produce online reports that are shareable with your group, automatically updated, and revisable. Click File > Publish > Publish to Power BI to publish a workbook to Power BI.
Note: Before you publish your workobook to Power BI, it must be saved to OneDrive for Business.
Learn more about Managing your queries:
- Publish to Power BI from Excel 2016
- View and Manage Queries in a Workbook (Power Query)
- Combine multiple queries (Power Query)
- Merge queries (Power Query)