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.SelectColum...
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
smozgur's avatar
smozgur
Icon for Helper I rankHelper 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