Dynamically Reference Named Column

[cpadilla0024]

Hi,

I am trying to automate a working template as much as I can to avoid editing PQ steps. I have named dataset_actual in the workbook to be edited by the user. In this case "ACTUAL2025" is the column I am trying to reference. However, this will change as the user toggles the workbook and we need power query to change smoothly in the steps. I have tried a few different ways.

 

[p45cal]

One way:
Get your value in the named single cell dataset_actual as follows:
I chose to name the step ColmHdr:

ColmHdr = Table.FirstValue(Excel.CurrentWorkbook(){[Name="dataset_actual"]}[Content])

Then in the add column step use the likes of this:

= Table.AddColumn(Source, "Added Colm", each Record.Field(_ ,ColmHdr)  & " Hi!", type text)

Here I've used the leftmost column headed ColmHdr1:


Here I've opted for ColmHdr5:


I hope I've properly understood your question.

 

[cpadilla0024]

It worked! Thank you!