How To Add Column In Power Query From Another Table

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 More...Less

In Power Query, y'all tin add together new columns by providing 1 or more sample values to assist create information technology. You tin practice this from a current option or past providing input based on selected columns. This is useful when yous know the data you want in your new column, but you're non sure which transformations to use.

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

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

The following steps are based on the video.

To open a query, locate 1 previously loaded from the Ability Query Editor, select a jail 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, bare cavalcade on the right.
Enter a sample value for the new column data you want, and then press Ctrl + Enter. In this case, nosotros entered "Montgomery, AL" to combine each capital letter with its respective state.

To make edits to the new cavalcade, double-click any text field, edit it, and then printing 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 past right-clicking the Step and selecting Edit Settings and the corresponding formula in the formula bar.

When calculation 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 cavalcade to organize u.s. by how many representatives each has.

To open a query, locate one previously loaded from the Power Query Editor, select a cell in the information, and so select Query > Edit. For more information meet Create, load, or edit a query in Excel.
To make information technology more than convenient, motion 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 Repscolumn.
Select Add Column > Column From Examples > From Choice. A blank column is added.
Enter "one to seven" as a range in the first bare prison cell.
To encounter 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 y'all entered. A quick fashion to see the subsets is to select AutoFilter on the new column. For instance:

Using the AutoFilter to see the ranges created

Afterwards calculation columns from examples, consider removing the original columns if y'all no longer need to brandish them. The new column data is non affected.

Adding a column from examples is based on the height 100 rows of Data Preview. Alternatively, you can add together your ain sample data, add a cavalcade example based on that sample information, and so delete the sample data when y'all no longer need it. The newly created cavalcade won't exist affected.