Get & Transform is a powerful data analysis tool in Microsoft Excel that allows you to import, refine and work with data from a range of sources, including files saved to your computer and web pages on the internet. You can shape data in order to display the details relevant to your purposes and hide information that is not required, without making permanent and irreversible changes to the data.
Using Get & Transform
The process of using Get & Transform consists of three general steps: discover, combine and refine, and load and share.
- Discover – The first step in working with Get & Transform is finding and importing the required data. You can use data from sources on your computer including files (e.g. Excel workbooks and text files) and databases (e.g. Access, SQL and Oracle databases); private data that is stored on servers or in the cloud, such as files stored in SharePoint, Azure and Salesforce; and publicly available data from websites such as Wikipedia and Gapminder. You can even use data from your organisation’s Facebook page.
Once you have located the data you require, you can import it using the relevant option on the New Query menu on the Data tab in Excel. You can choose to select only the data you need from the data source before importing it instead of having to import it all. - Combine and Refine – Once you have imported the required data, you can begin to work with it in the Query Editor to display the details that you need. The Query Editor is a separate window that allows you to modify how data is displayed without making permanent changes to the data itself. For example, if you remove columns in the Query Editor and display the data in Excel, it will appear as though the removed columns never existed; however, you can then return to the Query Editor and undo the column removal to display the columns again. Performing the same task in Excel would involve either hiding the columns, making the data confusing to work with, or permanently deleting the columns which results in the loss of data that you may need later on.
You can also use the Query Editor to merge queries – that is, if you have imported more than one set of data into Excel to work with, you can combine them in the Query Editor. This is useful for comparing data side-by-side; for example, for displaying sales data against advertising and marketing information. To merge queries, there must be a column that is identical (or close to identical) in both or all of the queries to be merged. This is usually a column that provides information on how to read the data – for example, if you were comparing sales and advertising data, the shared column might provide a time frame such as years or month names.
- Load and Share – The final step is to Load the data you have refined to Excel and share it with others. Once you have made the necessary changes to the data set in the Query Editor, you can load it to the Excel workbook into which it was originally imported and proceed to work with it in other ways (e.g. make aesthetic changes such as formatting the text, create charts and so on). You do not have to wait until you are completely finished with shaping and refining data to load it to Excel – you can open the Query Editor and make modifications any time you like.
When you are finished working on your workbook, you can share it in the same ways in which you would share an ordinary Excel workbook – by email, cloud sharing, presenting and so on.
Why Use Get & Transform?
Get & Transform is most useful when working with large data sets. When you import a large amount of data, it is unlikely that you will need all of it for your final report or presentation – especially when a data set includes hundreds or thousands of rows and/or columns. Get & Transform is an effective way of working with large amounts of data without compromising its integrity. It is also very handy for pulling together data from different files or sources to combine it and view comparisons or correlations.
For recent updates to the Get & Transform Add-ins refer to this article.
