Power Query: Add Column from Examples

We’re looking at a very cool feature that is found in Power Query Editor for this tutorial… namely the ability to create a new column that is based on another column. This is more impressive than it sounds, trust me.

We’re going to use a simple dataset to demonstrate how this Add Column from Examples feature works. If you’d like to use the same data as this tutorial, here is the Excel data file you can connect to Power BI Desktop.

If you need help learning how to connect Power BI to an Excel Data file, check this post.


Video:


Post:

We have ourselves a blank report here with our data connected. If we look at the Table View of our data you can see it’s a small simple dataset, seemingly about the number of different fruit in different cities in the U.S.

Let’s suppose that we needed a column that grouped rows of data according to how large that value amount is in each row. We would need a column for that if we wanted to make a Bar Chart (for instance) that compared cities that had 20 or more fruit with those that have less than 20.

That’s something we could use Add Column from Examples for.

To use the Add Column from Examples tool, we need to first open up Power Query Editor, as that’s where it lives. To do that, we’re going to switch to our Report View, and click the Transform Data button in the Home ribbon.

Power Query Editor shows us our data in a table format. We can use Add Column from Examples on any of these columns, so let’s try it before we try our “20 or More” vs “Less than 20” objective.

Let’s say we wanted Peaches and Pears to be grouped together, and Plums and Passionfruit to be in another group.

First, we select that column, then right-click in the column header and select “Add Column from Examples”.

When we select that, we get a whole new Column showing up at the right. We can rename it to whatever we want.

Then, we can start telling Power Query Editor some rules about what we want in this column. In our first row, which is about Peaches, we can type in the Fruit Group “Group A” in this new column. We can do the same thing for one of our rows with Pears.

Power Query will try and guess what we want here, and because it sees “Group A” in a couple places, it starts assuming we want all the rows in this new column to say “Group A”:

However, we don’t… we want the Plums and Passionfruits in a Group B.

So let’s type “Group B” in one of the Plums rows and one of the Passionfruit rows, and now Power Query understands what we want where, filling in the rest of the rows.

Sometimes you have to give it more clues for it to catch on to what you want.

Click that green OK button above that new column and a new column is added to your data.

This can work with numbers too, so let’s try it with our Value field to group our values into “20 or More” vs “Less than 20”.

Select the Value column and right-click on the header, selecting Add Column from Examples:

We can name our new column, and then start giving Power Query some rules.

If you double-click in the first blank row in the column, you can see it gives us some pre-made numerical options:

We want some text values in our column, though, and that’s totally doable.

Our first row has a value of 12, so let’s put “Less than 20” in that row in our new column.

We’ll give it a few more examples of of values of Less than 20 or 20 or More:

Power Query can now use these examples to group our Values into new Value Groups.

Click OK, and this new column will be added to your data.

Now you can click “Close & Apply” at the far left of the Power Query Editor home ribbon and see these new fields in your Data Pane.

Now you can use these new columns to build a Bar Chart, or any visual.

It’s a great easy way to quickly add a column using some rules.

Take care,

Joe.