CODEWITHSUNDEEP
powerquery
doodle
doodle

Reputation: 5

Power Query: How do I add a column with values combined from two other columns whose column headers are dynamic?

I'm able to do the basic operation of creating the new column, but since the source columns have dynamic headers, I run into an error as soon as the header changes. The headers for the source columns are derived from defined names Variable1 and Variable2 as below.

Column 1 Header = Variable1&" Column Name"

Column 2 Header = Variable2&" Column Name"

I need to add a new column that calculates the difference between Column 1 and Column 2. I tried some approaches but didn't really work out. I was hoping that the below code would work, but it spits an error message.

= Table.AddColumn(#"Prior Step", "New Column Name", each [Variable1&" Column Name"]-[Variable2&" Column Name"])

How do I get it to work without an error?

Upvotes: 0

Views: 100

Answers (1)

horseyride
horseyride

Reputation: 21318

Table.ColumnNames(Source){0} is the name of the 1st column of table Source

Table.ColumnNames(Source){1} is the name of the 2nd column of table Source

Record.Field(_,"xxx") is the contents of the current row in column named xxx

So to get difference between the first and second column in the current row, no matter their column names, use

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

If instead you know the names of the columns you want to use, and have the names in variables MyKnownName1 and MyKnownName2, then use

Record.Field(_,MyKnownName1)- Record.Field(_,MyKnownName2)

Upvotes: 1

Related Questions