cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
geraldmeunier
New Member

dynamic column name in formula

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

 

1 ACCEPTED SOLUTION
ImkeF
Super User II
Super User II

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.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
ImkeF
Super User II
Super User II

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.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

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 ?

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)

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors