cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
PBI_SG
Regular Visitor

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 🙂

1 ACCEPTED SOLUTION
smozgur
Helper I
Helper I

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

View solution in original post

1 REPLY 1
smozgur
Helper I
Helper I

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.