Custom Function to multiply Columns

Hi Mic1979 ,

I think any solution with replacevalues or transform columns is not going to work:

• Replacevalues needs a value to change, separate for each row. But because you don't have the names of the columns at the time of writing the query, this is very hard or not even possible.
• TransFormColumns does not give you access to the current record.

So I wrote a custom function. And I tested it:

• Table1 as start
  

• The custom function transform_table applied to columns C1 and C12

let
    Source = Table1,
    transform_table =    (
              T as table              //  table to transform
            , F as function           /*  function called for each record in the table, for each column to be transformed) 
                                            taking 3 arguments: 
                                                the currect record
                                                the name of the column it is being called for
                                                the value of the column it is called for
                                        */
            , optional Cols as list   //  list of column names to transform. Defaults to all columns
        ) =>
            let
                Cols = // Replace by Defaults
                    let 
                        all_cols = Table.ColumnNames(T),    // use if no valid columns are given
                        Cols = List.Intersect({Cols ?? {}, all_cols})   // get the valid columns given
                    in 
                        if List.Count(Cols) > 0 then Cols else all_cols,    // no vlid columns given? Use all columns

                // function transforming the current row
                transform_current_row_function = 
                    (CurrentRow) =>             
                        List.Accumulate(            // loop to all columnnames given, calling Record.TransformFields with F as 
                              Cols
                            , CurrentRow
                            , (current_record,current_column_name) => Record.TransformFields(current_record,{ current_column_name, (current_column_value) => F(current_record, current_column_name, current_column_value)})
                            ), 
                // call the row transform function for each row, prodcing a list of records
                result_as_list = Table.TransformRows(T,transform_current_row_function),
                // convert the list of records to a Table
                result_as_table = Table.FromRecords(result_as_list)
            in
                result_as_table
        ,
    transform_function = (_, ColumnName, Column_Value) => Record.Field(_, ColumnName) * [Introduction_Rate] * [Step_Split]
in
    transform_table(Source, transform_function, {"C1", "C12"})

• The result:
  
• In your query:
  • add the custom function to your query
  • and replace
    
    

calcTable = Table.ReplaceValue (
Input_Table,
null,
null,
(val, old, new) => val * Introduction_Rate * Step_Split,
oldColumnNames
),​

by

transform_function = (_, ColumnName, Column_Value) => Record.Field(_, ColumnName) * [Introduction_Rate] * [Step_Split]

calcTable = transform_table(Input_Table, transform_function, oldColumnNames)​,

In your original query and in some of the solutions posted earlier by others, I saw [Introduction_Rate] * [Step_Split] being referred to without the []. This will never work because you refer to a field in a record or column in a table.

Also be aware!

The first parameter to the transform_function is an underscore. This allows you to refer to the field in the record passed into _, without specifying it explicitly: [Introduction_Rate] is equivalent to _[Introduction_Rate].

It was a nice challenge by the way and addressing a problem I had myself earlier but never took the time to solve properly. I now have one more function to add to my own personal libraray of reusable functions 😊

Change 

(val, old, new) => val * Introduction_Rate * Step_Split

to

(val, old, new) => val * [Introduction_Rate] * [Step_Split]

If you still get errors, click on the cell but NOT on the word error and see what shows up at the bottom of your screen...

Mic1979 

(Input_Table as table, Introduction_Rate as text, Step_Split as text) =>

let

// a list of all column names that contain the "TOTAL"

oldColumnNames = List.Select(Table.ColumnNames(Input_Table), each Text.Contains(_, "TOTAL")),

// replace "TOTAL" with "SMOOTH"

newColumnNames = List.Transform(oldColumnNames, each Text.Replace(_, "TOTAL", "SMOOTH")),

calcTable = List.Accumulate( oldColumnNames , Input_Table, (s,c)=> Table.TransformColumns(s, {{c, each _ * Introduction_Rate * Step_Split }} ) ),

// rename column name

renameColumns = Table.RenameColumns(calcTable, List.Zip({oldColumnNames, newColumnNames}))

in

renameColumns 

try this code

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

To make it easier to assist you, please provide a representative data sample (with confidential information obfuscated) as text that can be copy/pasted, and also a screenshot (or text table) of your desired results from that data.