Power BI Column From Examples

One of the most intriguing features added in April 2017 is the Column From Examples functionality. You can use this feature to have Power BI craft a data transformation for you based examples of what you want the content of the new column to look like.

Creating a Sample Model

To demonstrate the Column From Examples feature, I had Power BI pull in a table providing information about United States National Parks. This table is found on the web page at:

https://en.wikipedia.org/wiki/List_of_areas_in_the_United_States_National_Park_System

I started up Power BI Desktop and selected Get Data from the start up dialog box. I chose Web from the list of possible data sources in the Get Data dialog box and clicked Connect. Next, in the From Web dialog box, I entered the URL shown above and clicked OK. Finally, I checked “Table 0” and clicked Edit. This opened the Query Editor and loaded the data from this HTML table as shown in Figure 1.

Figure 1

Replacing Text

The “Name” column contains the words “National Park” with each park name. For this example, we would like a version of each park name which uses the abbreviation “N.P.” rather than “National Park.” We could come up with an approach to replace this text or we can let Power BI do it for me using Column by Examples.

Here’s how:

1. On the Add Column tab of the ribbon, click Column From Examples.
   

   Figure 2

   This opens the Add Column From Examples workspace. The left side of this workspace shows the existing columns in the table. The right-most column allows us to enter the examples showing how the data should look in the new column.

   

   Figure 3

2. Select the top cell under Column1. This is the row for Acadia National Park.
3. Type “Acadia N.P.” and press ENTER. This is the example of what we want the content of the new column to look like in the Acadia National Park row.
   

   Figure 4

   As you can see in Figure 4, nothing much happens when we press ENTER. This means Power BI does not have enough information from a single example to determine what value it should create in the new column for all the other rows. Another example is needed.

4. When I hit ENTER, my cursor moved to the next row. This is the row for National Park of American Samoa. Type “N.P. of American Samoa” and press ENTER.
   

   Figure 5

   As seen in Figure 5, this second example provides enough information for Power BI to determine what to do. It creates a transform, shown at the top of the workspace, and applies it to all of the rows. We see the suggested values in all the remaining rows of the new column. The suggested values are shown in a lighter font to differentiate them from the example values we supplied. Notice that the name of the new column was also changed to something that describes the transform being applied. We’ll change that name to what we want to call this column in a moment. If the suggested values for one or more of the rows are not exactly what we are looking for, we can continue to provide additional examples in additional rows. For this exercise, we provided examples in the top two rows of the workspace. We can, in fact, provide examples for any row in the grid at any time.

5. Now that we have the suggested values in the new column coming out the way we want, click OK to create this new column.
6. Right-click the heading of this new column (Replaced Name) and select Rename from the context menu.
7.  Type “Park Short Name” and press ENTER.

Extracting a Value

Let’s try another one. Notice the Area[1] column contains values for both acres and square km. This isn’t very useful if we want to create measures to tell us total park area or average park area. We can’t sum or average values in this form. Again, we could come up with a method to extract the values or we can let Column From Examples figure it out for us.

If the first example, we simply clicked the Column From Examples button in the ribbon to get the default behavior. The default is to have the Column From Examples feature work with all columns to determine how to create the values in our examples. The alternative is to select one or more columns and then have the Column From Examples feature only work with those columns as it determines how to create values. We’ll try this approach for this example.

1. Click the heading of the Area[1] column to select this column.
2. Click the dropdown area in the lower section of the Column From Examples button and select From selection.
   

   Figure 6

   In the Column From Examples workspace, only the Area[1] is checked.

   

   Figure 7

3. Select the top cell under Column1. This is again the row for Acadia National Park.
4. Type “48,876.58” and press ENTER. This is the number of acres for Acadia National Park in the Acre[1] column. This time Power BI figures things out from a single example. It creates a transform, shown at the top of Figure 8, to extract the area for each park.
   

   Figure 8

5. Click OK to create this new column.
6. Right-click the heading of this new column (Replaced Name) and select Rename from the context menu.
7. Type “Area in Acres” and press ENTER.
8. On the Home tab of the ribbon, change the Data Type dropdown to Decimal Number.

Concatenation

We can also Column From Example to create more complex expressions that combine values from multiple columns.

1. On the Add Column tab of the ribbon, click Column From Examples. We want to have all columns selected in the Add Column From Examples workspace for this one.
2. Select the top cell under Column1.
3. Type “Acadia N.P. – Maine (est. 1919)” and press ENTER.Power BI changed the name of the new column to Combined. In the Combined column, Power BI correctly determined to use the content of the Park Short Name column, the Location column, and the Year established column along with some fixed text to create our new combined content.
   

   Figure 9

4. Click OK to create this new column.
5. Right-click the heading of this new column (Replaced Name) and select Rename from the context menu.
6. Type “Park Basic Info” and press ENTER.

Completing Our Model and Putting It to Use

As with any session using the Query Editor, we need to apply our changes to use these new columns in visualizations.  Let’s do this and create a quick visualization to show what we’ve done:

1. In the Query Editor, click File and select Close & Apply. The model is updated with our new columns.
2. In the Fields area, click Area in Acres.
3. In the Fields area, click Park Short Name.
4. Drag Park Basic Info from the Fields area and drop it on “Tooltips | Drag data fields here”.
   

   Figure 10

5. Save your Power BI file if you wish to return to it later.

Limitations

There are limitations to what you can achieve with the Column From Examples feature. Several of the national park names contain the phrase “and Preserve” on the end. To simplify things, I tried to get Column From Examples to perform this task for me. However, no matter how many examples I provided, Power BI couldn’t figure that one out as you can see in Figure 11.

Figure 11

Still, this is a very intriguing feature. Right now, it makes operations like extracting values from a string and concatenating values from multiple columns very easy to implement. I expect it will become smarter and more useful as time goes on.