Get & Transform

If you haven’t played with the “Get & Transform” commands in Excel 2016 for Windows, they’re  probably worth checking out. I don’t want to sound overly dramatic here, 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, and we need to prepare them 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.

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, as shown in Figure 1.

We click the “Edit” button to open the Query Editor, as shown in Figure 2.

The Query Editor is where we can perform our transformations. We need to split the Code column into two, based on the dash delimiter. So, we select the column and then click the Home > Split Column command icon, and identify the delimiter as a dash. We need to combine the Debit and Credit columns into a single Amount column, so, we click the Add Column > Custom Column command. We define the new column name as Amount, and the formula is equal to the Debit column minus the Credit column. We need to remove the unnecessary Debit  and Credit columns, so we right-click their column headers and select “Remove.”

Finally, we want to bring the transformed data into Excel, so we use the Home > Close & Load command. Excel places the resulting data into a table as shown in Figure 3.

Now, here is the really great part. The results table can be refreshed without having to manually perform these transformation steps again. So, next period, when an updated CSV file is saved to the same directory with the same name, all we need to do is right-click the results table and select “Refresh.” Excel will retrieve the data, apply the same series of transformations and update the results table accordingly. This sample example just barely scratches the surface, and you’ll want to dig deeper if you frequently export/import data. If this feature sounds interesting, feel free to check out the Excel University blog where there are many posts that illustrate various applications of the Get & Transform feature.

And remember, Excel rules!

Note: Power Query only retrieves data from external data sources. If you need to write data to external data sources, make sure to check out this blog to find out how!