FlashFill Meets Get & Transform

Do you love FlashFill? Do you love Get & Transform queries? If so, you’ll love the most recent Excel update pushed out by Microsoft: New Column by Example. It is essentially FlashFill inside PowerQuery. It is way cool. Check it out.

Objective

Let’s say we have some data. It doesn’t matter where it is, it could be in an Excel table, a csv file, or some external database. But, we have some data and it looks a bit like this:

We need to clean it up a bit. For example, we need to split the Name column into first and last, and, capitalize the first letter of each name. We also need to combine the City, State, and Zip columns into one, and, capitalize the first letter of the city column, while we need all upper case for the state column. Then, we need lower case for the email column.

In Excel, we could use FlashFill to accomplish this, or, a combination of concatenation, text-to-columns, and functions such as UPPER, LOWER, and PROPER. Or, in the Get & Transform Query Editor, we could apply a series of transformations manually to handle the splitting, combining, and assigning the desired case.

But, what if we wanted the ease of FlashFill with the power of Get & Transform queries. Well, Microsoft pushed this out in a recent update. It is called New Column by Example. Let’s walk through the details.

Please note: Excel updates are provided automatically depending on your license, and if you have a perpetual license this update may not be available. Also, the Get & Transform tools are built-in to Excel 2016 for Windows and may not be available in other versions, or, you may need to download and install the PowerQuery add-in from Microsoft.

Video

If you’d like to view the video version of the walkthrough, it is here. The detailed narrative is below the video.

Details

We’ll walk through these key steps:

• Pull data into the query editor
• Name column
• Address
• Email

Let’s take them one at a time.

Pull data into query editor

First, we need to get the data into the query editor. We do this by selecting any cell in the table and clicking Data > From Table/Range. The data is pulled into the query editor, as shown below.

With this done, let’s move to the name column.

Name column

Now, to split the Name column into two and capitalize the first letter of each name, we click Add Column > Column From Examples.

Just like FlashFill, we need to give it an example. So, we type the first name, in proper case, as shown below.

When we hit Enter, we get to see a preview of the transformation in the remaining cells, as shown below.

We click OK and the transformation is complete! We repeat this again to get the last name column.

Address

Combining the city, state, and zip columns to create a single address column is just as easy. Add Column > Column From Examples. We enter the first example, which would be the city, state, and zip column, with the desired separators, as shown with the resulting preview, below.

We click OK to store the transformation. As you might imagine, the email address is just as easy.

Email

We simply Add Column > Column From Examples. We enter the email address in lower case, click OK, and we are done.

If we wanted to clean things up a bit, we can delete any unnecessary columns, update the column labels, and click-and-drag to position the columns as desired. We can then click Close & Load to send the results back to Excel, as shown below.

Thanks Microsoft for pushing out this update which combines two of my favorites!

If you have any other fun Get & Transform tricks, please share by posting a comment below!

Sample Excel file for practice: ColumnByExample