CODEWITHSUNDEEP
powerbipowerquerym
Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

Reference a column by a variable

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)))

Upvotes: 2

Views: 374

Answers (4)

user26967000
user26967000

Reputation: 11

I've had this same problem but I solved it differently

As you've already discovered M Query doesn't allow variables within square brackets

x = "EmpName" // x is a string
ColumnName = MyTable[x] // [x] is a column called "x" in MyTable and not a column named "EmpName"

I solved it by abstracting the task into a function so I could reuse it as needed in a single line

let 
            ColSumFunc = (MyTable as table, Col1Name as text, Col2Name as text, NewColName) =>
            /*
            MyTable: the table to which you want to add the new col [c1]+[c2]
            Col1Name: the current name of [c1] in the original table
            Col2Name: the current name of [c2] in the original table
            NewColName: the name of the new column to be added
        
            Returns the original table with a new column containing the sum of the two columns specified
            */
            let
                RenamedCols = Table.RenameColumns(MyTable, {{Col1Name, "c1"}, {Col2Name, "c2"}}), // renames the columns to match the column names used in the summing step
                NewSumCol = Table.AddColumn(RenamedCols, NewColName, each [c1] + [c2]), // adds a new column containing the sum of the renamed columns
                RestoredCols = Table.RenameColumns(NewSumCol, {{"c1", Col1Name}, {"c2", Col2Name}}), // restores the original column names
        
                result = RestoredCols // return the table with the added column
        
            in
                result
        
in
    ColSumFunc

Now in your example:

let
    t0 = Table.FromRecords({[a = 1, b = 2]}),
    c0 = "a", c1 = "b",
    t1 = ColSumFunc(t0, c0, c1, "c")
in
    t1

returns:

Table.FromRecords({[a = 1, b = 2, c = 3]})

Obviously you don't need to use a function and can just use the rename method inline with your other steps

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40204

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.

Upvotes: 1

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

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

Upvotes: 2

horseyride
horseyride

Reputation: 21318

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

Upvotes: 1

Related Questions