Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
APPLIES TO: Power BI Desktop Power BI service
With Add Column From Examples in Power Query Editor, you can add new columns to your data model by providing one or more example values for the new columns. You can create the new column examples from a selection or provide input based on all existing columns in the table.
Use Add Column From Examples to create new columns quickly and easily in the following situations:
Adding a column from an example is easy and straightforward. The next sections show how easy it is.
To get sample data from Wikipedia, select Get Data > Web from the Home tab of the Power BI Desktop ribbon.
https://wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States
In the Navigator dialog box, select the States of the United States of America table, and then select Transform Data. The table opens in Power Query Editor.
Select Transform data from the Home tab of the ribbon. The data opens in Power Query Editor.
When you select Add Column > From Examples, the Add Column From Examples pane opens at the top of the table. The new Column 1 appears to the right of the existing columns. You might need to scroll to see all of the columns. When you enter your example values in the blank cells of Column 1, Power BI creates rules and transformations to match your examples and uses them to fill the rest of the column.
Notice that Column From Examples also appears as an Applied Step in the Query Settings pane. As always, Power Query Editor records your transformation steps and applies them to the query in order.
As you type your example in the new column, Power BI shows a preview of the rest of the column, based on the transformations it creates. For example, if you type Alabama in the first row, it corresponds to the Alabama value in the first column of the table. When you press Enter, Power BI fills in the rest of the new column based on the first column value, and names the column Name & postal abbreviation[12] - Copy.
Now go to the Massachusetts[E] row of the new column and delete the [E] portion of the string. Power BI detects the change and uses the example to create a transformation. Power BI describes the transformations in the Add Column From Examples pane, and renames the column to Text Before Delimiter.
As you continue to provide examples, Power Query Editor adds to the transformations. When you're satisfied, select OK to commit your changes.
You can rename the new column by double-clicking the column heading or by right-clicking it and selecting Rename.
Watch this video to see Add Column From Examples in action, using the sample data source:
Power BI Desktop: Add Column From Examples.
Many transformations are available when using Add Column from Examples. The following list shows the supported transformations:
General
Reference
Text transformations
Note
All Text transformations take into account the potential need to trim, clean, or apply a case transformation to the column value.
Date transformations
Time transformations
Note
All Date and Time transformations take into account the potential need to convert the column value to Date or Time or DateTime.
Number transformations
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayTraining
Module
Add calculated tables and columns to Power BI Desktop models - Training
By the end of this module, you'll be able to add calculated tables and calculated columns to your semantic model. You'll also be able to describe row context, which is used to evaluated calculated column formulas. Because it's possible to add columns to a table by using Power Query, you'll also learn when it's best to create calculated columns instead of Power Query custom columns.
Certification
Microsoft Certified: Power BI Data Analyst Associate - Certifications
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.