Get data from a single cell to fill a column

Sometimes the data that you want to use is held in a single cell.
This is a method to extract the data from the cell and use it to populate a custom column.

In this example, the aim is to extract the value Sales (cell A1) and use it to populate a column called Type.

The key function that we are going to use is Record.Field.

In Excel, select the Data tab, click on Get Data, From File and then on From Excel Workbook.

Even through the core data is in a Table, select the Sheet.
The data that we want to add is a heading outside the table.
Click on the Transform Data button.

Once the data is imported, remove any extra steps that have automatically be added.

In the formula bar, click on the Fx symbol.
Add the following, referencing the previous step and the column name that you want to get the field from.

The number after the previous steps name is the row number.

Power Query uses a zero base. This makes the first position value zero, the second position value is one and so on.
This applies to columns and rows

= Record.Field(#"PREVIOUS STEP NAME"{0},"COLUMN NAME")

Add any steps that you want to complete before adding the cell data.

In the example, the top rows are removed and the headers promoted.
The trick when performing the first step after the custom step, is to reference the step before the custom step.

= Table.Skip(#"BEFORE PREVIOUS STEP NAME",2)

When you are ready to add the value you extracted earlier into a column, select the Add Column menu and click on Custom Column.
Enter the text “replace me” and click on the OK button.

This will create the following step.

= Table.AddColumn(#"Promoted Headers", "Type", each "replace me")

In the formula bar, edit the step, remove the “replace me” text and add the name of the custom step #“Custom Step” that was manually added.

On the Home tab, click on the Close & Load icon.

The cell value has been added to the returned data as a column.