PowerQuery choose values based on a key column

Question

I have very large files which PowerQuery seems to handle nicely. I need to do some mathematical operations using column d and the value from columns a, b or c based on the value of the key column. My first thought is to isolate the salient value making a column called Salient which selects the value I need and then go from there. In Excel, this might be: =INDEX($A:$E, ROW(F2), MATCH(A2,$A$1:$D$1)).

In reality, I have between 50 and 100 columns as well as millions of rows, so extra points for computational efficiency.

Is this table just keys or are there other columns not shown in your image? — Alexis Olson
This is a toy example. There are about 50 columns whose header might appear in the keys column and 10 or 20 more columns whose header does not. — user121330
OK. My first thought involved unpivoting but might not be so great if you have data columns in addition to key columns. — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2021-01-15T20:08:48

You can define a custom column Salient with just this as the definition:

Record.Field(_, [Key])

The M code for the whole step looks like this:

= Table.AddColumn(#"Prev Step Name", "Salient", each Record.Field(_, [Key]), Int64.Type)

The _ represents the current row, which is a record data type that can be expressed as e.g.

[Key = "a", a = 17, b = 99, c = 21, d = 12]

and you use Record.Field to pick the field corresponding to the Key.

PowerQuery choose values based on a key column

1 Answers