Search code examples
powerquerym

PowerQuery choose values based on a key column

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)).

Highlighted PowerQuery Window

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

Solution

  • 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.