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
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.
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
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
We have Windows 13 at the office and not finding “Columns by example” feature
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
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.
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
Jeff,
Thank you for showing this very powerful Power Query function :).
Joan
Welcome … I love Power Query 🙂
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.
Hi Jeff,
Stumbled on your site and your info is awesome.
I even tested adding for a middle initial and as suffix and prefix. This tip helps fixing that State capitalization problem. When you do your example, put the example in the row you are fixing. I added the middle initial (A.) to Justin in row 3, so the example will go in the third row down. Worked for Sr. and Mr. and putting the example in row 2 fixed ny to NY along with the rest.
Again, awesome info.
Thanks
Love it – in other words it fills up as well as down 🙂
Hi,
As we see, we have to type value in each cell every time.
If value in cell would be too long, how can we combine 3 cell’s data ?
Thank you.