Add a column from an example (Power Query)

In Power Query, you can add new columns by providing one or more sample values to help create it. You can do this from a current selection or by providing input based on selected columns. This is useful when you know the data you want in your new column, but you're not sure which transformations to use.

The following examples use web data at this location: List of states and territories of the United States.

Your browser does not support video.

The following steps are based on the video.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select Add Column > Column From Examples > From All Columns

    The Add Columns From Examples pane appears with a new, blank column on the right.

    Power Query combine column from example pane

  3. Enter a sample value for the new column data you want, and then press Ctrl + Enter. In this case, we entered "Montgomery, AL" to combine each capital with its respective state.

    Power Query combine column from example merged data result

    To make edits to the new column, double-click any text field, edit it, and then press Ctrl + Enter when done.

    The transformation steps appear above Data Preview.

    Power Query Combine column from example custom column formula
  4. Select OK.

  5. Examine the step added to the Applied Steps section in the Query Settings pane by right-clicking the Step and selecting Edit Settings and the corresponding formula in the formula bar.

    Power Query Combine column from example applied steps window

When adding a column from examples by selection, Power Query offers a helpful list of available fields, values, and suggested transformations for the selected columns. In this example based on the data in the video, create a new column to organize the states by how many representatives each has.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. To make it more convenient, move the column to the end upon which you want to base the column section by selecting Transform > Move > To End. In the example, move the Number of Reps column.

  3. Select Add Column > Column From Examples > From Selection. A blank column is added.

  4. Enter "1 to 7" as a range in the first blank cell. 

  5. To see suggestions for all cells, press Ctrl + Enter.

  6. If you are satisfied, select OK.

    Sometimes, you may need to iterate a few times to get the results you want. 

Results

Power Query arranges the data by subsets according to the range you entered. A quick way to see the subsets is to select AutoFilter on the new column. For example:

Using the AutoFilter to see the ranges created

After adding columns from examples, consider removing the original columns if you no longer need to display them. The new column data is not affected.

Adding a column from examples is based on the top 100 rows of Data Preview. Alternatively, you can add your own sample data, add a column example based on that sample data, and then delete the sample data when you no longer need it. The newly created column won't be affected. 

See Also

Power Query for Excel Help

Add a column from examples (docs.com)

Add a custom column

Add a column based on a data type

Power Query M formula language reference (docs.com)

Need more help?

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

×