Add a column from an example (Power Query) (2024)

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.

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

   

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

   

   To makeedits 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.

   

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.

   

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 uponwhich you want to base the column section by selecting Transform > Move > To End. In the example, move the Number of Repscolumn.

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.

   See Also

   Create a Data Model in Excel

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:

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 thatsample data, and then delete the sample data when you no longer need it. The newly created column won't be affected.