If you are like many Excel users, you upgrade from one Excel version to another every few years without taking the time to fully explore the new enhancements. I mean, you are busy, I know. And you don’t have time to learn about the new stuff, I totally get it. But here’s the thing — you may be able to get your work done faster by using the new capabilities.
Jeff Lenning will present The ‘Get & Transform’ Tool in Excel: A Powerful Tool for Importing and Manipulating Data (September 6, morning and afternoon sessions), at Accountex USA 2017
Here are a few examples. When we upgraded to Excel 2007, we got two new amazing enhancements: Tables and SUMIFS. In Excel 2010, we got several PivotTable enhancements and Slicers. When we upgraded to Excel 2013, we got FlashFill. And, built-in to 2016 we have Get & Transform queries. Get & Transform queries provide us new ways to accomplish old tasks. Check it out.
What ‘Get & Transform’ Does
Formerly available as a separate add-in called PowerQuery, the Get & Transform commands built-in to Excel 2016 for Windows enable us to retrieve data and easily prepare it for use. So, if you ever find yourself exporting data from one system, cleaning it up, and then using it for reporting or to upload into another system, these commands can probably save you some time.
What are some examples of “cleaning” data? Here are a few common transformations: splitting or combining columns, removing unneeded columns or rows, adding calculated columns, retrieving matching values from lookup tables, changing text strings to upper or lower case, unpivoting summarized data, transposing data, and other similar tasks. These are the kinds of tasks that we have traditionally performed manually, with formulas or macros.
Now, these kinds of transformations can be done without macros or formulas. We just need to click a few buttons. Let’s walk through an example.
How ‘Get & Transform’ Works
Let’s say we exported some transactions from one system, and need to prepare it for our reporting process. Specifically, the transactions were exported to a CSV file, and we need to pull it into Excel… but, we also need to clean it up. We need to remove the first two rows of extraneous text, split the full account into primary and subaccount columns, capitalize the state code, and remove the last two rows.
In the old days, we could accomplish these cleaning tasks manually, with formulas, macros, or a combination. Now, we can do it by clicking a few buttons.
Note: the steps below are presented with the latest version of Excel 2016 for Windows; if you are using a different version of Excel these commands may have different navigation, names, or may not be available.
The first step is getting the data from the CSV file into our Excel file. To do this, we use the following command from the Get & Transform ribbon group on the Data ribbon tab: Get Data, From File, From Text/CSV. We browse to the desired CSV file and click Open. Excel provides a preview, as shown below.
If we wanted to pull the data into Excel as it is, we could just click the Load button… but, we want to perform some transformations so we click the Edit button instead. This opens the Query Editor, as shown below.
The Query Editor is where we tell Excel about the transformations we’d like to make. Let’s just take them one at a time.
Let’s start by removing the first two rows of extraneous text. We click Reduce Rows, Remove Rows, Remove Top Rows, and enter 2 into the resulting Remove Top Rows dialog as shown below.
With the first two rows gone, we can now tell Excel that the first data row should be used as the column headers, or, column labels. To do that, we click the Use First Row As Headers ribbon command.
Next, we want to remove the last two rows. So, we click Reduce Rows, Remove Rows, Remove Bottom Rows, and enter 2 into the resulting Remove Bottom Rows dialog as shown below.
Next, let’s capitalize the state code. We select the CustState column, and click the Transform, Format, UPPERCASE ribbon command.
Finally, we need to split the FullAccount column into primary and subaccount columns. We select the FullAccount column and select the Transform, Split Column, By Delimiter ribbon command. We select Colon in the resulting Split Column by Delimiter dialog as shown below.
We can rename the resulting columns to Primary and Sub just by double-clicking the column headers and entering the desired name.
With the transformations complete, we just click the Home, Close & Load ribbon command to send the values to a results table in our Excel workbook, as shown below.
And the best part is that once we’ve defined the sequence of transformation steps, we can simply replay them next period by right-clicking the results table and selecting Refresh! This enables us to get our recurring-use workbooks updated fast — really fast.
In addition to the example above, I have several additional tutorials that walk you through other transformations, so if you are interested, please feel free to check them out here.
Also, I’ll be talking more about these live at Accountex this year, so, I’d love to see you in my session!