Math Operations on Dynamic Table.SelectColumns

[PBI_SG]

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 🙂

[smozgur · Accepted solution]

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