Forum Discussion

PBI_SG's avatar
PBI_SG
Regular Visitor
3 years ago
Solved

Math Operations on Dynamic Table.SelectColumns

I am getting some dynamic column names from a table "Model_Workings" where the column names follow certain business specific criteria - (begin with "abc" and end with "def"). 

 

= Table.SelectColumns(
                                       Model_Workings,
                                       List.Select(
                                                           Table.ColumnNames(Model_Workings),
                                                            each Text.StartsWith(_,"abc") and Text.EndsWith(_,"def")
                                                       )
                                     ) + 1 

 

My need is to perform some math operations (as simple as +1 for purposes of an example) - and I run into an error that I am currently unable to work my way around - error details below

 

Expression.Error: We cannot apply operator + to types Table and Number.
Details:
Operator=+
Left=[Table]
Right=1



Please could I get some help on how to get around this issue?

 

Thanks in advance 🙂

General Comment
Need Help
Tutorial Requests
  • smozgur's avatar
    smozgur
    3 years ago

    Try the following M Code.

     

    let
    // Source data
    Model_Workings = Excel.CurrentWorkbook(){[Name="Model_Workings"]}[Content],

    // Filter columns
    Columns = List.Select(
                        Table.ColumnNames(Model_Workings),
                        each Text.StartsWith(_, "abc") or Text.EndsWith(_, "def")
                    ),
    // Perform math operation
    Result = Table.FromRecords(Table.TransformRows(Model_Workings,
            (row) => Record.TransformFields(row, List.Transform(
                    Columns, 
                    (fieldname) => {fieldname, each Record.Field(row, fieldname) + 1}
                ))))

in
    Result

1 Reply

  • smozgur's avatar
    smozgur
    Helper I
    3 years ago

    Try the following M Code.

     

    let
    // Source data
    Model_Workings = Excel.CurrentWorkbook(){[Name="Model_Workings"]}[Content],

    // Filter columns
    Columns = List.Select(
                        Table.ColumnNames(Model_Workings),
                        each Text.StartsWith(_, "abc") or Text.EndsWith(_, "def")
                    ),
    // Perform math operation
    Result = Table.FromRecords(Table.TransformRows(Model_Workings,
            (row) => Record.TransformFields(row, List.Transform(
                    Columns, 
                    (fieldname) => {fieldname, each Record.Field(row, fieldname) + 1}
                ))))

in
    Result