Multiply columns based on position Power query

Question

Hello I need to multiply two columns in a new column in Power Query where the columns can change name but not position. So for example I want to multiply the second column in a table with the fourth column in the same table independently and want to be able to change name on the table headers and the code should still work

horseyride horseyride · Accepted Answer · 2021-04-01T15:48:55

Table.ColumnNames(Source){1} would be the name of the 2nd column

Table.ColumnNames(Source){3} would be the name of the 4th column

You can then wrap it with Record.Field (_,x) to get the value

so the value in the second column would be

Record.Field (_,Table.ColumnNames(Source){1})

So the code below would create a new column named Mult that multiplies columns 2 and 4 without knowing their column names

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Mult", each 
     Record.Field (_,Table.ColumnNames(Source){1}) *
     Record.Field (_,Table.ColumnNames(Source){3}) )
in  #"Added Custom"

Multiply columns based on position Power query

1 Answers