Reference a column by a variable

user_131874 0
Viewed 119

I want to reference a table column by a variable while creating another column but I can't get the syntax:

    t0 = Table.FromRecords({[a = 1, b = 2]}),
    c0 = "a", c1 = "b",
    t1 = Table.AddColumn(t0, "c", each([c0] + [c1]))

I get the error the record's field 'c0' was not found. It is understanding c0 as a literal but I want the text value contained in c0. How to do it?

Edit

I used this inspired by the accepted answer:

    t0 = Table.FromRecords({[a = 1, b = 2]}),
    c0 = "a", c1 = "b",
    t1 = Table.AddColumn(t0, "c", each(Record.Field(_, c0) + Record.Field(_, c1)))
m powerbi powerquery
3 Answers

Another way:

let
    t0 = Table.FromRecords({[a = 1, b = 2]}),
    f = {"a","b"},
    t1 = Table.AddColumn(t0, "sum", each List.Sum(Record.ToList(Record.SelectFields(_, f))))
in
    t1
user_107870500

try using an index as below

let t0 = Table.FromRecords({[a = 1, b = 2]}),
#"Added Index" = Table.AddIndexColumn(t0, "Index", 0, 1),
c0 = "a",
c1 = "b",
t1 = Table.AddColumn(#"Added Index", "c", each Table.Column(#"Added Index",c0){[Index]} + Table.Column(#"Added Index",c1){[Index]} )
in t1
user_92642300

Expression.Evaluate is another possibility:

= Table.AddColumn(t0, "c", each Expression.Evaluate("["&c0&"] + ["&c1&"]", [_=_]) )

Please refer to this article to understand the [_=_] context argument:

Expression.Evaluate() In Power Query/M

This article explains that argument specifically:

Inside a table, the underscore _ represents the current row, when working with line-by-line operations. The error can be fixed, by adding [_=_] to the environment of the Expression.Evaluate() function. This adds the current row of the table, in which this formula is evaluated, to the environment of the statement, which is evaluated inside the Expression.Evaluate() function.

user_7652260
Related