Using a Variable as a Column Name in Power Query

Question

question

ToddBocik-1305 asked Feb 1, '21 Lz-3068 commented Feb 1, '21

Using a Variable as a Column Name in Power Query

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?

power-query-not-supported

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-02-01T13:47:01.853Z

Lz-3068 answered Feb 1, '21 MayaOrita-1651 commented Jun 17, '22

@ToddBocik-1305

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"

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2021-02-01T13:51:34.1Z

ToddBocik-1305 answered Feb 1, '21 Lz-3068 commented Feb 1, '21

Thank you so much. It worked perfectly!

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 · Feb 01, 2021 at 03:44 PM

Welcome @ToddBocik-1305. Glad I could help & thanks for posting back

0 ·

question