FlashFill Meets Get & Transform

FlashFill Meets GNT by Jeff Lenning

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:

Data Table by Jeff Lenning

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.

Query Editor by Jeff Lenning

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.

FlashFill by Jeff Lenning

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

Excel preview by Jeff Lenning

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.

Excel Transformation on Address by Jeff Lenning

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.

Excel Results Table by Jeff Lenning

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

 

 

 

This article was written by Jeff Lenning

7 comments:

  1. Joyce
    Reply

    We have Windows 13 at the office and not finding “Columns by example” feature

    1. Jeff Lenning Post author
      Reply

      Hi Joyce … happy to help!

      So, that capability was pushed out by Microsoft in a very recent update, and, depending on your Excel license (perpetual or O365 subscription), you may not get the update.

      There are two ways to buy Excel 2016, a perpetual license and with a subscription. The perpetual license is the traditional way we buy Excel, we buy it once and can use it forever. This license doesn’t get the new feature updates. The O365 subscription is monthly, and, with the recurring subscription, Microsoft pushes new features out periodically.

      With all of the different Excel versions and licenses available, it is harder than ever to teach Excel because all features and functions aren’t available in all versions and platforms.

      Anyhow, hope this helps provide the background as to how the licenses work these days…thanks!

      Thanks
      Jeff

  2. Jerry
    Reply

    Thanks, Jeff.

    But in your example, ny stayed ny, Ca stayed Ca, and Dc stayed Dc. In other words the FlashFill didn’t work on the state column. Any suggestions on how to make that part of the FlashFill automatic formatting, or do we have to go into Get and Transform formatting?

    Also, I wonder if there’s a way to easily transform ofarrell not to Ofarrell, but instead to O’Farrell.

    1. Jeff Lenning Post author
      Reply

      Hi Jerry!
      FlashFill tries to detect a pattern, and does so by comparing your example to the data. So, in this particular workbook, the first state NC is all caps, and so when you type NC as the first one, Excel’s FlashFill will assume you just want to copy it as it is. It does it’s best to analyze and guess. If you change the data from NC to nc, and then enter NC and FlashFill it down, you’ll get the expected results.
      Thanks,
      Jeff

  3. Joan Hauff
    Reply

    Jeff,

    Thank you for showing this very powerful Power Query function :).

    Joan

    1. Jeff Lenning Post author
      Reply

      Welcome … I love Power Query 🙂

  4. Evan Ames
    Reply

    I cannot relate the time I spent fixing spreadsheets for attorneys where First and last name needed to be separated, need city, state and zip for a merge or making all the font formatting the same. I am not an Excel ace so it would mean banging my head into the formulas. So, I get to impress an attorney, and go on with my wonderful life.

Leave a Reply

Your email address will not be published. Required fields are marked *