Welcome back to Artificial Intelligence in Excel! In Part 1, we’ll looked at a feature called Flash Fill. It works by recognizing patterns of data and filling out the remainder. In Part 2, we’ll look Power Query’s Column From Examples command. It’s similar to Flash Fill: it fills in the rows based on the input we provide. However, it has a great advantage over Flash Fill: it’s dynamic. Column from Examples command actually constructs a formula based on the initial data. If the input data changes, the filled in data changes too!

Let’s see how it works.

Column From Examples command

Let’s start with a simple base table of data.

We load the table to Power Query by selecting Data > From Table/Range.

In Power Query we select Column From Examples.

First name in capital letters

Let’s first try to get a column with first name in capital letters and last name as is. We select Column From Examples and fill out the first row. We get the suggestion for completing the other rows and we can see it matches the result we want.

We simply confirm with OK.

Before confirming, let’s just observe what Power Query did: it constructed a formula based on our input. This formula will be used to fill our the entire column. It’s correctly assumes it has to use the Name column in capital/upper letters, the Last Name column and combine the two.

If anything in the column Name of Last Name changes, it will also change in the new Merged column. This wouldn’t happen with Flash Fill. Just Brilliant!

Name of the Month

Let’s try something else Flash Fill can’t do: getting month name from date column

Select Column From Examples and type in October. We see the suggestions match the name of the month from Date of Birth column. It’s also clear from the constructed formula that it works as we wanted.

This way we could get the year, day and many more information based on other column. We can see the available answers if we just start filling out the first row. There are multiple types of information available.

Let’s use Week of Year.

Address number

Another example is address number. We again select Column From Examples and provide the first value, 251.

We see the last two rows include some text we don’t want. What we can do is simply fill out the third row, and Power Query now understands we’re only interested in the number.

Confirm the data with OK.

We finish by using Close & Load to load the finished table to Excel.

In conclusion, we have demonstrated how Column From Examples works. With that you now have another brilliant tool in your toolbox!

Watch the tutorial

Meanwhile, you can also watch the tutorial online on our new YouTube channel!

Please leave us a like, comment, and subscribe for more amazing Excel tricks!

Follow us on LinkedIn.

Check out our brand new R Academy!