Converting Table Column with numbers to List using Table.ToList

[thejayo]

Hi everyone,

I'm working on a query which applies a calculation to each column in a table. I have included a simplified snipped of the code with which I'm struggling below.

let
    n = 1, // this will change based on another variable
    
    dataTable = #table({"X","Column A","Column B"},{{100,1,2},{200,3,4}}),

    columnName = Table.ColumnNames(dataTable){n},

        myFunction = (list1 as list, list2 as list) =>
            let
                product = List.Sum(list1) * List.Sum(list2)
            in
                product,

    result = myFunction(dataTable[X], Table.ToList(Table.SelectColumns(dataTable, columnName)))
in
    result

Basically, what I'm trying to do is multiply column A and column B with column X and then build a new table with the results. In reality there will be hundreds of columns so I want to reference these columns dynamically. The code that changes "n" and builds the new table with the results is working already.

The only issue I'm having is with calling myFunction dynamially.

This works as intended but is obviously not dynamic

result = myFunction(dataTable[X], dataTable[A])

This does not work and I get the error "We cannot convert the value 1 to type Text"

result = myFunction(dataTable[X], Table.ToList(Table.SelectColumns(dataTable, columnName)))

I would appreciate any suggestions how to solve this. Thank you!

[Anonymous]

Try changing

columnName = Table.ColumnNames(dataTable){n}

to

Table.ColumnNames(dataTable{n})

--Nate

[thejayo · Accepted solution]

Managed to sort this.

For future reference this did the trick:

result = myFunction(dataTable[x], Table.Column(dataTable, columnName))