Get & transform: more tips and tricks from the Excel expert.

• Author: Lenning, Jeff
• Position: Tech Falk

[ILLUSTRATION OMITTED]

If you haven't played with the Get & Transform commands in Excel 2016 for Window's, they're worth checking out. I don't want to sound overly dramatic, but this set of capabilities is a game changer. These tools provide new ways to approach tasks and enable us to do things that were previously time-consuming, impractical or required macros.

Formerly available as the Power Query add-in, the Get & Transform tools built in to Excel 2016 for Windows are incredibly powerful. At a high level, they enable us to retrieve data from a variety of sources and prepare it for use as needed. We can retrieve, or get, data stored in many types of places--for example in CSV and Excel files, databases and online services. We can prepare, or transform, the data by doing things like splitting and combining columns and adding new calculated columns.

Let's pretend for a moment that we need to get data out of one system and into another. The data could be stuff like journal entries, a trial balance, e-commerce transactions, banking activity, payroll data, fixed asset additions or depreciation amounts.

Once the data is exported from the source system, we need to prepare it for import into the target system. The preparation, or transformation, step is the one that typically takes the most time for us. Some common transformations include combining and splitting columns, including only selected columns, sorting, filtering, deriving calculated values and flattening data.

Fortunately, a Get & Transform query can automate this process. Oh, and here is something really cool: We can do this without needing Excel formulas or VBA macros ... Wow! We just point and click, my friend.

Walkthrough

Let's walk though a quick example to illustrate. Some journal entries have been exported from one system that we need to prepare for import into another system. The exported journal entries are stored in a CSV file, and before we can import the data into the target system, we need to clean it up. Note: if you'd like to work along with the steps below, take a moment and download the je.csv file from excel-university.com/gnt.

We can pull the data into a new blank workbook by selecting the Data > New Query > From File > From CSV command. In the resulting Import Data dialog, we browse to and select the desired file, and click "Import."

Excel then displays a preview of the data (Figure 1).

[FIGURE 1...