Return value based on a dynamic column name (dax or power query fine)

[vancromy]

Hello,

I have two tables:

1. Contains a unique identifier (concatenation of date and agent)

2. Contains the same unique identifier as well as a column for each unique buy/sell pair (positive & negative) for each unique ID (probably best to contextualize this in example below)

Example of Table 2:

Unique ID                Agent            Pair Number        Buy:1            Sell:1       Buy:-1        Sell:-1        Buy:2       Sell:2       Buy:-2  etc...

04042018-Bob        Bob                1                            £20               £20        £-15          £-30           null          null         null

05042018-Bob        Bob                1                            £30               £10        £-30          £-10           null          null         null

06042018-Bob        Bob                2                            null               null          null          null             £5           £10         £-10

Worth knowing about Table 2:

- I have a Unpivot and Re-pivot in Table 2 which cannot be altered

- I have filtered the rows to reflect the highest pair number for that specific date. I.e. there are no duplicates of Unique ID

- There can be up to a maximum of 5 buy/sell pairs (+ and/or -) in the dataset (but this will vary as the dataset changes) meaning that there can be up to a maximum of 20 columns after the unpivot/repivot mentioned above.

I'm now trying to bring through the according buy and sell price for each unique ID in Table 1. I.e. go from having for example 20 columns to just 4 since 16 out of these will have nulls...To do this though I need to lookup the unique ID, find the corresponding pair number and then retrieve the values from the columns with that same pair number such that my final result would look like this.

Unique ID               Buy (+)  Price      Sell (+) Price       Buy (-) Price       Sell (-) Price

04042018-Bob       £20                          £20                       £-15                 £-30

05042018-Bob       £30                          £10                       £-30                 £-10

06042018-Bob        £5                           £10                       £-10               and so on...

I have no preference as to whether I do this in PQ or Dax as long as it's the most efficient way from a memory allocation point of view because the datasets can be very big. 

I've tried playing with defining my column name as a variable in Dax as follows;

Buy (+) Price =
VAR Pair Number = calculate(sum(Table_2[Exp.Pair Number]),filter(Table_2,Table_2[unique_id]=Table_1[unique_id]))
RETURN
If(Pair Number = 1,RELATED(Table_2[Exp.Buy:-1]),if(Pair Number = 2,RELATED(Table_2[Exp.Buy:-2]),if(Pair Number = 3,RELATED(Table_2[Exp.Buy:-3]),if(Pair Number = 4,RELATED(Table_2[Exp.Buy:-4]),if(Pair Number = 5,RELATED(Table_2[Exp.Buy:-5]),Blank())))))

But it errored because for the current dataset any column with a pair number above 2 does not exist (remember because of the unpivot and repivot the number of columns in my table changes).

Hope this was clear. Happy to expand if necessary.

Thanks,

Y

EDIT: I should probably also add that I attempted to define a variable that would actually dynamically be the column name I'm trying to pull from as follows:

Buy (+) Price =
VAR ColumnName = "Exp.Buy:"&calculate(sum(Table_2[Exp.Pair Number]),filter(Table_2,Table_2[unique_id]=Table_1[unique_id]))
RETURN

RELATED(Table_2[ColumnName])

But again this failed with error saying ColumnName doesn't exist in Table_2 so it seems variables can't be passed in as column names?

[vancromy · Accepted solution]

Solved this by adding a custom column to determine whether it was a Buy (+) or Buy (-) price (same for sell) and added these in before the re-pivot I mentioned in my original post. This solved my problem and made it such that I will only ever always have 4 columns in my table. Completed in PQ. 

Didn't find a solution to the broader question at hand which is how you can get either PQ or DAX to calculate something based on a dynamically changing column name. In excel this would be fairly easy because you'd do an index match/vlookup where you combine a bit of text with the variable (as I tried in my edit) but this doesn't seem to be possible in PQ or DAX?