Introduction
The Power Query Editor is a very powerful tool with dozens of different features and functionalities.
With so many features and tools, it’s very easy to get lost when starting to learn Power Query.
One of the best ways to learn how to use Power Query is using the tool called “Column from Examples”.
Column from Examples lets the user show an example to Excel. And then, Power Query tries to complete the rest of the column by looking at the example. If that sounds familiar to you, this is because it can also be done in Excel using the Flash Fill tool.
How to use Column from Examples in Power Query
Let’s see the following example:
We have a list of sentences describing the color of each flower:
Can we extract from each sentence the type of flower, and it’s color?
With Power Query – it’s not a problem!
First, to edit the data in Power Query, we can load it after turning the range into a table (Click Insert – Table or use the CTRL+T Shortcut):
Next, let’s load the data as a query in Power Query, by clicking Data – From Table/Range:
Here’s how it looks after we load it to the Power Query Editor:
Now, to use the “Column from Examples” tool, make sure that the “Sentence” column is selected, then click the Add Column tab, and then – Column From Examples – From Selection:
Now, in the newly created column, let’s show Excel how the Flower type column should look like, by typing “Roses” in the first row:
Next, click Enter:
Excel correctly understood our example and auto-completed the rest of the column. Now, all we have to do is click OK to approve and create the new column!
We can repeat this for extracting the color as well:
And after clicking OK, we will get the following results:
That’s it, we did it!
Now, we can rename the columns by double-clicking the columns:
Here’s a quick recap of our steps:
Understanding how Column from Examples works:
When we use Column from Examples in Power Query, Excel tries to recreate our steps using one of the existing tools in Power Query.
We can look at what actually happened by looking at the steps on the right side of the Power Query Editor:
See the steps called “Inserted Text Before Delimiter” and “Inserted Text After Delimiter”?
These are the actual tools that were used for extracting the names of the flowers and their color.
These tools can be accessed from the Add Column tab, then by clicking Extract:
The Text Before Delimiter and Text After Delimiter tools enable us to extract a certain text from a string by using a certain delimiter.
We can see what happened in each of the steps by clicking the gear icon next to the name of the step:
For example, let’s click on the gear icon next to the “Inserted Text Before Delimiter” step:
We can see that the delimiter used is a space character. Basically, Excel looks for the first space character and extracts the data before it. That’s how we get the names of the flowers.
Now, let’s see how the colors were extracted, by looking at the “Inserted Text After Delimiter” step:
Okay, this looks a little bit more complex than our previous example 🙁
To make things simpler:
Excel performed two operations here:
- Using the Text After Delimiter tool to extract the color after a delimiter (The second space character)
- Applying the Proper function, which capitalizes the first letter in each of the newly created words in each row. Read more about the PROPER function here.
If we don’t mind having the color names in lowercase, we can just use the Text After Delimiter tool:
In order to extract the text after the second space delimiter, we asked Excel to skip the first one (by expanding the Advanced Options and typing “1” in “Number of delimiters to skip”).
In order to see the final result, we can close and load the query into our Excel workbook:
Cool, right? 🙂
Practice Column from Examples in Power Query
Now, let’s try to extract the First Name, Last Name, and Domain for the following email addresses.
Click here to download the Power Query Column from Examples exercise!
