When you’re writing an M expression for a custom column in Power BI or Power Query it’s easy to reference the values in other columns. A slightly more difficult question, however, is how can you reference column values dynamically? For example, given the following input table:
How can you use the values in the “Column To Select” column to dynamically select values from either Column 1, Column 2 or Column 3? For example, on the first line of the table the “Column To Select” column contains the value 2, so the calculated column should contain the value from “Column 2”; on the second line of the table the “Column To Select” column contains the value 1, so the calculated column should contain the value from “Column 1” and so on:
There are a number of different ways to achieve this. You could, for instance, write a nested if or do some other kind of fancy conditional logic in M, but this could result in a lot of code. You could also unpivot all the columns except “Column To Select”, do some filtering, then pivot the data again but that would also be quite complicated. Probably the easiest way of doing this is with the Record.Field() function, which allows you to get the value of any given field in a record by passing the name of that field as a text value.
Here’s an example query that generates the table shown in the first screenshot above in its first step, sets some data types on the columns in the second step, then creates the custom column shown in the second screenshot in the final step:
[sourcecode language=”text” padlinenumbers=”true” highlight=”18,19,20″]
let
Source = #table(
{"Column 1", "Column 2",
"Column 3", "Column To Select"},
{{10,20,30,2},
{30,40,50,1},
{50,60,70,3}}),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Column 1", Int64.Type},
{"Column 2", Int64.Type},
{"Column 3", Int64.Type},
{"Column To Select", Int64.Type}}),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Selected Column Value",
each
Record.Field(
_,
"Column " & Text.From([Column To Select])))
in
#"Added Custom"
[/sourcecode]
Here’s the expression for the custom column isolated, in the form that you would use in the Add Custom Column dialog:
[sourcecode language=”text”]
Record.Field(_, "Column " & Text.From([Column To Select]))
[/sourcecode]
Understanding how to use the Record.Field() function here leads us to an interesting side discussion about custom columns, functions, records and each expressions. The full M expression for the third step in the query is:
[sourcecode language=”text”]
Table.AddColumn(
//the table returned by the previous step
#"Changed Type",
//the name of the new custom column
"Selected Column Value",
//a function to call for each row of the table
//returning the value to display in the custom column
each Record.Field(_, "Column " & Text.From([Column To Select])))
[/sourcecode]
The first two parameters of the Table.AddColumn() function are straightforward but if you read the documentation you’ll see that the third parameter requires a value of type function. This function is called for every row in the table and returns the values in the custom column. It doesn’t look like it but there is a new function being defined here. In M an each expression can be used to define a function that has no name and that takes one parameter whose name is _ (ie the underscore character). The Table.AddColumn() function passes this new function a value of type record representing all of the values in the current row of the table, and that means Record.Field() can access this record by using _. What’s more, when referring to fields in this record you don’t even have to say _[Column To Select], you can drop the underscore and just say [Column To Select], as in the code example above. All this means that the expression
[sourcecode language=”text”]
each Record.Field(_, "Column " & Text.From([Column To Select]))
[/sourcecode]
is basically the same as
[sourcecode language=”text”]
(_)=> Record.Field(_, "Column " & Text.From(_[Column To Select]))
[/sourcecode]
…which, if you know a bit of M, makes a lot more sense but for the average user is probably more intimidating.
Hi Chris,
A little note about your solution. When we do not have a column headers like “Column x” then I have a slightly different proposal.
= Table.AddColumn(#”Changed Type”, “Selected column value”, each Record.ToList(_){[Column To Select]-1})
Cheers 🙂
Thanks Bill!
Hey Chris, An important extension of this, sometimes you want to do a table function in a new table column and end up referencing fields from both the tables in a single step. So you add a column to table 1 ( Table.AddColumn(MyTable1, “NewColumnName”, …) and a field or column reference from a previous step or external reference alongside a column from Table1 (e.g. Table.SelectRows(MyTable2, each [TableTwoField] = [TableOneField]).
In summary this:
Table.AddColumn(MyTable1, “NewColumnName”, Table.SelectRows(MyTable2, each [TableTwoField] = [TableOneField] ) )
This causes a big referencing problem – PQ tries to behave as if both fields are from Table 2. This can be worked around creating a separate function in a separate step, but I have found a way to do what you want in the single line as follows. (Note the seemingly duplicative double parentheses)
Table.AddColumn(MyTable1, “NewColumnName”, ( ( TableOneField) => Table.SelectRows ( MyTable2, each [TableTwoField] = TableOneField ) ) ( [TableOneField] ) )
For some reason the quotes around the function that has been created are key to getting the evaluation of [TableOneField] to happen, without them you don’t get what you expect.
This inline function creation and evaluation has saved me a ton of extra steps and evaluation.
Let me know what you think!
Interesting, thanks! I need to think about why all those parentheses are necessary…
This saved my day today 🙂 – thank you
This was very helpfull! I’ve been trying to solve this exact problem for quite some time but wasn’t able to put my finger on it!
Really grateful for your guidance! Context is I am working to parameterise an example Power BI report and was struggling as the parameters I created to indicate column names were being interpreted as text values rather than as reference to another column. Once I used the Record.Field function my problem was solved. Much appreciated!
This was exactly what I was trying to do. Thank you!