Reference Column by Position

[Kirk P.]

I've got this code

Table.AddColumn(ReplacedNullwithZero, "$ Variance", each [2017]-[2016])

to calculate a dollar variance between the 2017 and 2016 columns.

For obvious reasons, I'd rather make this calculation refer to column numbers or positions rather than the actual column header names. Something like Column2 - Column1, not 2017 - 2016. Can this be done?

 

[Bob Phillips]

You could extract the column names in to a list

ColumnNames = Table.ColumnNames(ReplacedNullwithZero)

and then rename the 2nd and 3rd columns

Columns.Renamed = Table.RenameColumns(ReplacedNullWithZero,{{ColumnNames{1}, "Prev"},{ColumnNames{2},"Curr"}})

then use the fixed names

Table.AddColumn(ReplacedNullwithZero, "$ Variance", each [Curr]-[Prev])

You could always set them back again afterwards

 

[MarcelBeug]

You can use Record.FieldValues: this code subtracts the values in column 2 from the values in column 3:

= Table.AddColumn(ReplacedNullwithZero, "$ Variance", each Record.FieldValues(_){2}-Record.FieldValues(_){1})

 

[Kirk P.]

Thank you gentlemen. Both suggestions are highly useful.