Add a column from an example (Power Query)

Use Excel's Get & Transform (Power Query) experience to add new columns of data in the 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 from providing input based on all (or selected) columns in a given table.

Use Query Editor to add a new column from examples

For this example, we're using data from the following Wikipedia article: List of states and territories of the United States.

  1. Select any cell within your data range.

  2. Go to Query Tools > Query > Edit Query.

  3. In the Query Editor pane select Add Column > Column From Examples > choose From All Columns, or From Selection. For this example, we'll use the From all columns option.

    Power Query combine columns with example option on teh Add Column tab

    The Query Editor will open the Add Columns From Examples pane with a new, blank column on the right.

    Power Query combine column from example pane
  4. Enter a sample value for the new column data you want, then press Ctrl+Enter. In this case, we typed Montgomery, AL to join each capital with its respective state. Power Query will complete the rest of the column for you.

    Power Query combine column from example merged data result

    If you need to make any edits to the new column, you can double-click any text field and edit it, then press Ctrl+Enter when you're done. Power Query will display the transformation steps above the data.

    Power Query Combine column from example custom column formula
  5. When you're done, press OK to confirm your actions, and Power Query will complete the transformation, returning to the Power Query Editor. You'll see the step added to the Applied Steps section in the Editor.

    Power Query Combine column from example applied steps window

Considerations and limitations

The following list provides all the transformations that are supported when using Add column from Examples.

  • Reference

    • Reference to a specific column (including trim and case transformations)

  • Number transformations

    • Average

    • Count distinct values

    • Count values

    • Minimum

    • Maximum

    • Median

    • Percent of

    • Power

  • Text transformations

    • Text.Combine (supports combination of literal strings and entire column values)

    • Text.Replace

    • Text.Start

    • Text.Middle

    • Text.End

    • Text.BeforeDelimiter

    • Text.AfterDelimiter

    • Text.BetweenDelimiters

      Note: All Text transformations take into account the potential need to trim or apply a case transformation to the column value.

  • Date transformations

    • Date.Day

    • Date.DayOfWeek

    • Date.DayOfWeekName

    • Date.DayOfYear

    • Date.Month

    • Date.MonthName

    • Date.QuarterOfYear

    • Date.WeekOfMonth

    • Date.WeekOfYear

    • Date.Year

    • Date > Age

    • Date > Year > Start of Year

    • Date > Year > End of Year

    • Date > Month > Start of Month

    • Date > Month > End of Month

    • Date > Quarter > Start of Quarter

    • Date > Month > Days in Month

    • Date > Quarter > End of Quarter

    • Date > Week > Start of Week

    • Date > Week > End of Week

    • Date > Day > Day of Month

    • Date > Day > Start of Day

    • Date > Day > End of Day

  • Time transformations

    • Time.Hour

    • Time.Minute

    • Time.Second

      Note: All Date and Time transformations take into account the potential need to convert the column value to Date or Time or DateTime. - Other transformations include Format > Clean > Extract > Length.

  • Date/Time transformations

    • Subtract days

    • Combine Date and Time

    • Earliest

    • Latest

    • Subtract time

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Power Query is known as Get & Transform in Excel 2016

Import data from external data sources

Learn about Power Query formulas

Shape data with Power Query

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×