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.
The following steps are based on the video.
-
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.
-
Select Add Column > Column From Examples > From All Columns. The Add Columns From Examples pane appears with a new, blank column on the right.
-
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.
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.
-
Select OK.
-
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.
-
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.
-
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.
-
Select Add Column > Column From Examples > From Selection. A blank column is added.
-
Enter "1 to 7" as a range in the first blank cell.
-
To see suggestions for all cells, press Ctrl + Enter.
-
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 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
Add a column from examples (docs.com)
