Add a column from examples
When you add columns from examples, you can quickly and easily create new columns that meet your needs. This process is useful for the following situations:
- You know the data you want in your new column, but you're not sure which transformation, or collection of transformations, can get you there.
- You already know which transformations you need, but you're not sure what to select in the UI to make them happen.
- You know all about the transformations you need by using a custom column expression in the M language, but one or more of those transformations aren't available in the UI.
The Column from examples command is located on the Add column tab, in the General group.
In this example, you start with the table shown in the following image.
Your goal in this example is to create two new columns:
- Range: Create bins for the Monthly Income column in discrete increments of 5,000.
- Full Name: Concatenate the Last Name and First Name columns to a single column.
One of the options you have when creating your new column is to select which column is used in your calculations. For this example, you create the Range column from the values in the Monthly Income column.
To do this task, select the Monthly Income column, select the Column from examples command, and then select From selection.
The preview pane displays a new, editable column where you can enter your examples. For the first example, the value from the selected column is 19500. So in your new column, enter the text 15000 to 20000, which is the bin where that value falls.
When Power Query finds a matching transformation, it fills the transformation results into the remaining rows. You can also see the M formula text for the transformation above the table preview.
After you select OK, your new column becomes part of your query. A new step is also added to your query.
The next goal is to create a Full Name column by using the values from the First Name and Last Name columns.
To do this task, select the Column from examples command, and then select From all columns.
Next, enter your first Full Name example as Enders, Maria.
After you select OK, your new column is now part of your query. A new step is also added to your query. Select and hold (or right-click) the Merge column name, then select Rename and change the name of the column to Full Name.
Your last step is to remove the First Name, Last Name, and Monthly Income columns. Your final table now contains the Range and Full Name columns with all the data you produced in the previous steps.
When providing examples, Power Query offers a helpful list of available fields, values, and suggested transformations for the selected columns. You can view this list by selecting any cell of the new column and either begin entering a value or selecting F2 on your keyboard. You could also use your mouse and double-click on one of the cells in the new column.
It's important to note that the Column from examples experience works only on the top 100 rows of your data preview. You can apply steps before the Column from examples step to create your own data sample. After the Column from examples column is created, you can delete those prior steps; the newly created column won't be affected.
Many, but not all, transformations are available when you use Column from examples. The following list shows the supported transformations.
General
- Conditional Column
Reference
- Reference to a specific column, including trim, clean, and case transformations
Text transformations
- Combine (supports combination of literal strings and entire column values)
- Replace
- Length
- Extract
- First Characters
- Last Characters
- Range
- Text before Delimiter
- Text after Delimiter
- Text between Delimiters
- Length
- Remove Characters
- Keep Characters
Note
All Text transformations take into account the potential need to trim, clean, or apply a case transformation to the column value.
Date transformations
- Day
- Day of Week
- Day of Week Name
- Day of Year
- Month
- Month Name
- Quarter of Year
- Week of Month
- Week of Year
- Year
- Age
- 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
Time transformations
- Hour
- Minute
- Second
- To Local Time
Note
All Date and Time transformations take into account the potential need to convert the column value to Date, Time, or DateTime.
Number transformations
- Absolute Value
- Arccosine
- Arcsine
- Arctangent
- Convert to Number
- Cosine
- Cube
- Divide
- Exponent
- Factorial
- Integer Divide
- Is Even
- Is Odd
- Ln
- Base-10 Logarithm
- Modulo
- Multiply
- Round Down
- Round Up
- Sign
- Sine
- Square Root
- Square
- Subtract
- Sum
- Tangent
- Bucketing/Ranges