dynamic column name in formula

[geraldmeunier]

Hi everyone,

Let's say I have several columns A, B and C

I create a new column with a formula like :

= [A] + [B]

Let's say I want column B to be driven from a function that we could call fnGetParamater("Parameter_a")

Is there a way to have a syntax that would be like :

= [A] + ["& fnGetParamater("Parameter_a")& "]

I know this is not working but you get the idea...

Any suggestion welcome !

Gerald

[ImkeF · Accepted solution]

Not completely sure what you're after, but could it be this?:

[A] + Record.Field(_, fnGetParamater("Parameter_a") )

It will return the value from the  colum whose name the function returns.

[geraldmeunier]

Thanks a lot !
for my own culture (yes I'm new to power query), what does the underscore really stand for in the

"Record.Field(_, ColumnName)" formula ?

[ImkeF]

Hi @geraldmeunier 

it stands for the record of the current row (if you use your fomula in a Table.AddColums-formula for exapmle).

There, the third argument is a function into which the current record will be automatically pumped into. 

There's some syntax sugar involved: https://docs.microsoft.com/en-us/powerquery-m/understanding-power-query-m-functions 

which means that you can use reference the columns with square brackets only, but if you cannot use that shortcut syntax, you have to explictly reference the record. When the "each"-keyword is used, this can be done by using the underscore "_",

but if you want to nest functions, you can replace the each by ordinary functions like so:

(x) => MyFunction(x)