Using a Variable as a Column Name in Power Query

Question

I have a table that contains many columns of data. Based on certain criteria, I would like to get a specific columns worth of data. This means that I need to use a variable for the selected column name. I have other parts of the logic working so I will simplify my issue down to this example.

let
Source = Table1,
TestColumnName = "HC-2 Score",
#"Added Conditional Column" = Table.AddColumn(Source, "Test Score", each if [Job Title] = "DIRECTOR" then [TestColumnName] else null)
in
#"Added Conditional Column"

The line below works but is not dynamic. I need to choose the column to retrieve the scores.

"Added Conditional Column" = Table.AddColumn(Source, "Test Score", each if [Job Title] = "DIRECTOR" then [#"HC-2 Score"] else null)

What is the solution to this problem?

Answer 1

@Todd Bocik

If problem understood you're looking for something like:

let  
    Source = Table1,  
    VarColumnName = "HC-2 Score",  
    #"Added Conditional Column" = Table.AddColumn(Source, "Test Score", each  
        if [Job Title] = "DIRECTOR" then Record.Field(_, VarColumnName) else null  
    )  
in  
    #"Added Conditional Column"  

Answer 2

Thank you so much. It worked perfectly!