Add a column from an example in Power BI Desktop
Starting with the April 2017 release of Power BI Desktop, you can add new columns of data to your model using Query Editor by simply providing one or more sample values for your new column. You can create a new column example from a current selection or provide input based on all (or selected) columns in a given table.
This approach helps you quickly and easily create new columns and is great for the following situations:
- You know the data result you want in your new column, but you're not sure which transformation (or collection of transformations) will get you there.
- You already know which transformations you need, but you're not sure where to click or select in the UI to make them happen.
- You know all about the transformations you need using a Custom Column expression in M, but one (or more) of those expressions aren't available to click or add in the UI.
Using the add column from example feature is easy and straightforward. In the next few sections, we see just how easy it is.
Use Query Editor to add a new column from examples
To create a new column from an example, launch Query Editor. You can do this by selecting Edit Queries from the Home ribbon in Power BI Desktop.
To get data from a web page, go to the Home tab, click Get Data > Web, then paste the URL into the dialog that appears. This article uses data from a Wikipedia article. You can click the following link to get the data for yourself and follow along:
Once Query Editor is launched and you have some data loaded, you can get started adding a column from examples. To add a new column, in Query Editor select the Add Column tab on the ribbon and select Column from Examples. If you choose the drop-down, you can select either From All Columns (the default, if you just select the button instead of the drop-down) or select From Selection. In this article, we'll walk through selecting From All Columns.
The Add Column From Examples pane
Once you make a selection to add a new column from examples, a new pane appears that shows the columns in the current table (you may need to scroll to see them all). The new Column1 is also shown to the right, which is the column that Power BI Desktop creates based on your examples. Below the new Column1 header, there are blank cells where you can type your examples that Power BI uses to create rules and transformations to match your example.
Notice that this is an Applied Step in the Query Settings pane. As always, Query Editor will record your transformation steps and apply them to the query, in order.
This is called the Add Columns From Examples pane, and it consists of four primary areas:
- The Command bar that includes a brief description of the feature or the transformation.
- The Send Feedback option to help Power BI improve this feature.
- The OK and Cancel buttons, which let you commit your transformations and add the column or cancel.
- The new column area, where you can type your sample values in any of the rows (to provide Power BI with your example), relating to other columns in that row.
As you type your example in the new column, Power BI gives you a preview of how the column it's creating will look, based on the transformations it detects. For example, if you type Alabama in the first row, it corresponds to the Alabama value in the first column of the table. As soon as you press Enter, Power BI fills in the column based on that value.
But, then say you go to the row that includes Massachusetts[E] and delete that last [E] portion (because you don't want it anymore). Power BI detects the change and uses the example to create a transformation. Notice the explanation of the transform in the upper-middle pane.
As you continue to provide examples, Query Editor adds to the transformations. When you're satisfied, you can select OK to commit your changes.
See Add Column from Examples in action
Want to see this working? The following video shows this feature being put to use, using the data source provided earlier in this example. Take a look and follow along for yourself!
Considerations and limitations
There are many transformations that are available when using Add column from Examples, but not every transformation is included. The following list provides all of the supported transformations.
- Reference to a specific column (including trim, clean, and case transformations)
- Combine (supports combination of literal strings and entire column values)
- First Characters
- Last Characters
- Text before Delimiter
- Text after Delimiter
- Text between Delimiters
The following supported text transformations are available beginning with the November 2017 release of Power BI Desktop:
- Remove Characters
- Keep Characters
All Text transformations take into account the potential need to trim, clean, or apply a case transformation to the column value.
- Day of Week
- Day of Week Name
- Day of Year
- Month Name
- Quarter of Year
- Week of Month
- Week of Year
- Start of Year
- End of Year
- Start of Month
- End of Month
- Start of Quarter
- Days in Month
- End of Quarter
- Start of Week
- End of Week
- Day of Month
- Start of Day
- End of Day
- To Local Time
All Date and Time transformations take into account the potential need to convert the column value to Date or Time or DateTime.
- Absolute Value
- Convert to Number
- Integer Divide
- Is Even
- Is Odd
- Base-10 Logarithm
- Round Down
- Round Up
- Square Root
The following supported number transformation is available beginning with the November 2017 release of Power BI Desktop:
- Conditional Column