LOOKUP Select Column/Field?

[SpillerBD]

In Excel I can perform a lookup that uses the desired column for the return value by varying the column in the formula.
How can this be done on either the Query side or the PowerPivot side of PowerBI?

 

[MarcelBeug]

Query Result:

let
    Source = Table.NestedJoin(Data,{"Category"},LookupTable,{"Category"},"LookupTable",JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "ReturnValue", each Table.Column([LookupTable],[RequiredField]){0}, type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"LookupTable"})
in
    #"Removed Columns"

 

[SpillerBD]

Awesome! Though it'll be a bit for me to digest and implement (just priorities to get back to the project at the office.)